Mécanisme de fenêtrage : ROWS vs RANGE

• L'application de la fonction analytique peut être configurée pour cibler un morceau de la partition (une fenêtre sur la partition)
• les clauses ROWS et RANGE sont associées à une expression définissant cette fenêtre mobile (une ligne de début et une ligne de fin à prendre en compte pour le calcul)
• La fenêtre mobile s'écrit :

  ROWS BETWEEN début AND fin
  -- ou 
  RANGE BETWEEN début AND fin

• début peut valoir :
  • UNBOUNDED PRECEDING : la première ligne de la partition
  • CURRENT ROW : la ligne de la partition en cours de traitement
  • nPRECEDING (avec n entier) : n lignes avant la ligne courante
• fin peut valoir :
  • UNBOUNDED FOLLOWING : la dernière ligne de la partition
  • CURRENT ROW
  • nFOLLOWING (avec n entier) : n lignes après la ligne courante
• Par défaut toute la partition jusqu'à la ligne courante : ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
• Différence entre ROWS et RANGE
  • ROWS traite chaque ligne de la partition séparément
  • RANGE regroupe les lignes dont les valeurs sont identiques sur le critère de tri.
• Exemple : Table naissance

  +--+---------+----------+------+
  |id|maternite|jour      |nombre|
  +--+---------+----------+------+
  |1 |Rose     |2025-01-01|3     |
  |2 |Rose     |2025-01-02|2     |
  |3 |Rose     |2025-01-02|4     |
  |4 |Rose     |2025-01-03|3     |
  |5 |Rose     |2025-01-04|2     |
  |6 |Rose     |2025-01-05|5     |
  |7 |Rose     |2025-01-06|0     |
  |8 |Rose     |2025-01-07|1     |
  |9 |Rose     |2025-01-08|2     |
  |10|Rose     |2025-01-09|3     |
  |11|Chou     |2025-01-01|6     |
  |12|Chou     |2025-01-02|7     |
  |13|Chou     |2025-01-02|0     |
  |14|Chou     |2025-01-04|2     |
  |15|Chou     |2025-01-04|8     |
  |16|Chou     |2025-01-05|6     |
  +--+---------+----------+------+

  • Cumul des naissances ligne après ligne

    SELECT id, jour, nombre, 
    sum(nombre) OVER (ORDER BY jour ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumul
    FROM naissance;
    +--+----------+------+-----+
    |id|jour      |nombre|cumul|
    +--+----------+------+-----+
    |1 |2025-01-01|3     |3    |
    |11|2025-01-01|6     |9    |
    |3 |2025-01-02|4     |13   |
    |2 |2025-01-02|2     |15   |
    |13|2025-01-02|0     |15   |
    |12|2025-01-02|7     |22   |
    |4 |2025-01-03|3     |25   |
    |5 |2025-01-04|2     |27   |
    |15|2025-01-04|8     |35   |
    |14|2025-01-04|2     |37   |
    |16|2025-01-05|6     |43   |
    |6 |2025-01-05|5     |48   |
    |7 |2025-01-06|0     |48   |
    |8 |2025-01-07|1     |49   |
    |9 |2025-01-08|2     |51   |
    |10|2025-01-09|3     |54   |
    +--+----------+------+-----+

  • Cumul des naissances jour après jour

    
    SELECT id, jour, nombre,
    sum(nombre) OVER (ORDER BY jour RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumul
    FROM naissance;
    +--+----------+------+-----+
    |id|jour      |nombre|cumul|
    +--+----------+------+-----+
    |1 |2025-01-01|3     |9    |
    |11|2025-01-01|6     |9    |
    |3 |2025-01-02|4     |22   |
    |2 |2025-01-02|2     |22   |
    |13|2025-01-02|0     |22   |
    |12|2025-01-02|7     |22   |
    |4 |2025-01-03|3     |25   |
    |5 |2025-01-04|2     |37   |
    |15|2025-01-04|8     |37   |
    |14|2025-01-04|2     |37   |
    |16|2025-01-05|6     |48   |
    |6 |2025-01-05|5     |48   |
    |7 |2025-01-06|0     |48   |
    |8 |2025-01-07|1     |49   |
    |9 |2025-01-08|2     |51   |
    |10|2025-01-09|3     |54   |
    +--+----------+------+-----+