Critère ROWS & RANGE

Pour l’instant, nous ne pouvons pas être flexibles quant au choix du nombre de lignes avant ou après à prendre en compte. C’est désormais possible avec les critères ROWS et RANGE. Pour les utiliser, nous écrivons :

OVER (ROWS BETWEEN --START-- AND --END--)

OVER (RANGE BETWEEN --START-- AND --END--)

Et nous pouvons spécifier les options suivantes :

• CURRENT ROW - la ligne actuelle
• n PRECEDING - les lignes précédant la ligne actuelle
• n FOLLOWING - les lignes suivant la ligne actuelle 

La différence entre ROWS et RANGE est que le critère ROWS ne tient pas compte des valeurs, mais uniquement des positions, tandis que RANGE définit la fenêtre en termes de plages de valeurs plutôt que de positions de lignes.

Pour RANGE, nous devons spécifier ORDER BY --column_name--, car sinon il ne saurait pas comment choisir la fenêtre.

Par exemple :

ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING

Ici, il crée une fenêtre qui inclut la ligne actuelle, la ligne précédente et la ligne suivante.

RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING ORDER BY levels

Il crée ici une fenêtre qui inclut, pour chaque niveau (trié par ordre croissant), le niveau actuel, un niveau avant celui-ci et un niveau après celui-ci. Si le niveau actuel est 5, elle inclura alors les niveaux 4, 5 et 6.

Remarque : L’utilisation de RANGE BETWEEN peut entraîner l’inclusion de davantage de lignes dans votre fenêtre, car elle inclut toutes les lignes qui partagent les mêmes valeurs que celles de la plage, tandis que ROWS BETWEEN inclura toujours le même nombre de lignes (dans la mesure où elles sont disponibles dans l’ensemble de données). 

De plus, RANGE ne prend pas en charge les colonnes de date.

Voici un exemple simple pour illustrer ROWS par rapport à RANGE :

Utilisation de ROWS :

SELECT employee_name, salary,
    AVG(salary) OVER (
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    ) as avg_salary_rows

Utilisation de RANGE :

SELECT employee_name, salary,
    AVG(salary) OVER (
        ORDER BY salary
        RANGE BETWEEN 1000 PRECEDING AND 1000 FOLLOWING
    ) as avg_salary_range