Configurer le frame de fenêtre avec ROWS et RANGE

Quand tu utilises des fonctions fenêtres, tu te demandes sûrement : "Combien de lignes dans la fenêtre participent au calcul de la valeur pour la ligne courante ?" La réponse dépend du frame de fenêtre.

Le frame de fenêtre — c'est la plage de lignes utilisée pour calculer le résultat d'une fonction fenêtre. Cette plage est construite à partir de la ligne courante, plus des conditions supplémentaires définies via ROWS ou RANGE.

Un exemple simple : en calculant une somme cumulative, tu peux préciser :

• Prendre en compte seulement la ligne courante.
• Prendre la ligne courante et toutes les lignes au-dessus.
• Prendre la ligne courante et un nombre fixe de lignes au-dessus/en dessous.

C'est justement ROWS et RANGE qui contrôlent quelles lignes vont dans le frame de fenêtre.

Utilisation de ROWS

ROWS définit le frame de fenêtre au niveau de la position physique des lignes. Ça veut dire qu'il compte les lignes de haut en bas dans leur ordre, peu importe les valeurs dans ces lignes.

Syntaxe

fonction_fenêtre OVER (
    ORDER BY colonne
    ROWS BETWEEN début AND fin
)

Expressions clés :

• CURRENT ROW — la ligne courante.
• nombre PRECEDING — un certain nombre de lignes au-dessus de la courante.
• nombre FOLLOWING — un certain nombre de lignes en dessous de la courante.
• UNBOUNDED PRECEDING — depuis le début de la fenêtre.
• UNBOUNDED FOLLOWING — jusqu'à la fin de la fenêtre.

Exemple : somme cumulative pour la ligne courante et les 2 précédentes

SELECT
    employee_id,
    salary,
    SUM(salary) OVER (
        ORDER BY employee_id
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS rolling_sum
FROM employees;

Explication :

• ROWS BETWEEN 2 PRECEDING AND CURRENT ROW veut dire : prends la ligne courante et les deux lignes au-dessus.
• La somme cumulative sera calculée juste pour ces trois lignes.

Résultat :

Exemple : analyse "fenêtre glissante" avec un nombre fixe de lignes

Objectif : calculer le salaire moyen pour la ligne courante et les deux suivantes.

SELECT 
    employee_id,
    salary,
    AVG(salary) OVER (
        ORDER BY employee_id
        ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING
    ) AS rolling_avg
FROM employees;

Résultat :

Utilisation de RANGE

RANGE construit le frame de fenêtre selon les valeurs, pas la position des lignes. Ça veut dire que les lignes sont incluses dans le frame si leurs valeurs dans la colonne ORDER BY tombent dans la plage indiquée.

Syntaxe

fonction_fenêtre OVER (
    ORDER BY colonne
    RANGE BETWEEN début AND fin
)

Exemple : somme cumulative sur une plage de valeurs

Objectif : calculer la somme cumulative pour les lignes où le salaire diffère de la ligne courante de pas plus de 2000.

SELECT 
    employee_id,
    salary,
    SUM(salary) OVER (
        ORDER BY salary
        RANGE BETWEEN 2000 PRECEDING AND 2000 FOLLOWING
    ) AS range_sum
FROM employees;

Explication :

• RANGE BETWEEN 2000 PRECEDING AND 2000 FOLLOWING veut dire : prends les lignes où la valeur salary est dans la plage ±2000 de la ligne courante.

Résultat :

Comparaison ROWS et RANGE

• ROWS bosse avec les vraies lignes et leur nombre. Il ne dépend pas des valeurs.
• RANGE bosse avec une plage logique de valeurs, définie pour la colonne de ORDER BY.

Pour comparer, voilà un exemple. Imaginons qu'on a une table sales avec les données :

Comparons les requêtes :

ROWS :

SELECT
    id,
    SUM(amount) OVER (
        ORDER BY amount
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS sum_rows
FROM sales;

Résultat :

Ici chaque ligne est ajoutée à la somme au fur et à mesure de son apparition réelle.

RANGE :

SELECT 
    id,
    SUM(amount) OVER (
        ORDER BY amount
        RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS sum_range
FROM sales;

Résultat :

Ici les lignes 1 et 2 sont groupées, car leur amount = 100. RANGE prend en compte les valeurs répétées dans la colonne amount.

Exemples de vraies tâches

1. Calcul de l'augmentation du revenu

Objectif : calculer la variation du revenu par rapport à la ligne précédente.

SELECT 
    month,
    revenue,
    revenue - LAG(revenue) OVER (
        ORDER BY month
    ) AS revenue_change
FROM sales_data;

2. Comparer la ligne courante avec la moyenne du groupe

Objectif : pour chaque département, calculer la différence entre le salaire de l'employé et la moyenne du département.

SELECT 
    department_id,
    employee_id,
    salary,
    salary - AVG(salary) OVER (
        PARTITION BY department_id
    ) AS salary_diff
FROM employees;

Erreurs fréquentes avec ROWS et RANGE

Ordre de ligne (ORDER BY) mal indiqué : Si tu ne précises pas l'ordre de tri, PostgreSQL va râler, car il ne saura pas quelle est la ligne courante.

Mélanger les approches ROWS et RANGE dans une même tâche : Choisis la bonne approche selon tes données. ROWS est top pour les tâches avec un nombre fixe de lignes, RANGE — pour les plages de valeurs.

Oublier les valeurs répétées avec RANGE : Souviens-toi que RANGE prend toutes les valeurs répétées, ce qui peut bien changer le résultat.