Aller au contenu
Fonctions de fenêtre

Fonctions de fenêtre (OVER)

Une fonction de fenêtre calcule un agrégat sur un groupe de lignes tout en gardant chaque ligne, à l'inverse de GROUP BY. On compare ainsi chaque salaire à la moyenne de son service.

  • window
  • OVER
  • PARTITION BY
  • fenêtre
  • analytique

Syntaxe

Modèle
SELECT col,       fonction(col) OVER (         [PARTITION BY groupe]         [ORDER BY tri]       ) AS aliasFROM table;

Exemple

Exemple concret
SELECT name,       department_id,       salary,       AVG(salary) OVER (PARTITION BY department_id) AS moyenne_service,       salary - AVG(salary) OVER (PARTITION BY department_id) AS ecartFROM employeesWHERE department_id IN (2, 4)ORDER BY department_id, salary DESC;-- Jeu de données : entreprise-- Résultat attendu :-- Olivier Caron | 2 | 78000 | 58000 | 20000-- Quentin Faure | 2 | 54000 | 58000 | -4000-- Rachid Amrani | 2 | 51000 | 58000 | -7000-- Wendy Roche | 2 | 49000 | 58000 | -9000-- Sandrine Lopez | 4 | 58000 | 50000 | 8000-- Théo Brun | 4 | 42000 | 50000 | -8000

À retenir

  1. PARTITION BY découpe les lignes en groupes indépendants. Sans PARTITION BY, la fenêtre couvre tout le résultat : SUM(salary) OVER () donne la masse salariale totale sur chaque ligne.
  2. Quand la même fenêtre revient, nommez-la : … AVG(salary) OVER w … FROM employees WINDOW w AS (PARTITION BY department_id).
  3. Piège : ajouter ORDER BY dans OVER change le calcul. Le cadre par défaut devient « du début jusqu'à la ligne courante » : AVG(salary) OVER (ORDER BY hired_at) donne une moyenne cumulée, pas la moyenne du groupe.
  4. Piège : une fonction de fenêtre ne peut pas figurer dans WHERE, car elle est calculée après. Filtrez son résultat dans une CTE ou une sous-requête.
  5. Disponibles dans SQLite 3.25+, PostgreSQL, SQL Server et MySQL 8+.

Voir aussi