Fonctions de fenêtrage ① — OVER et PARTITION BY

Données utilisées dans cet article — sales et employee

Une fonction de fenêtrage calcule des choses comme les totaux par groupe, les rangs et les totaux cumulés ligne par ligne, et ajoute une seule colonne à ton résultat.

Alors que GROUP BY réduit plusieurs lignes en une seule ligne de synthèse, une fonction de fenêtrage conserve chaque ligne de détail en place et ajoute juste la valeur calculée à côté.

Avant de te lancer dans les exercices, jette un œil aux définitions de colonnes et aux données d'exemple des deux tables que nous allons utiliser — sales et employee.

① Exécute PRAGMA table_info(sales); et PRAGMA table_info(employee); pour vérifier les définitions de colonnes des deux tables.

② Exécute SELECT * FROM sales LIMIT 5; et SELECT * FROM employee LIMIT 5; pour prévisualiser les 5 premières lignes de chacune. Note que la colonne dept_id dans employee vaut NULL pour certains employés.

OVER () — calculer sur toutes les lignes et ajouter une colonne

Commençons par la forme la plus simple : aggregate(column) OVER ().

Avec des parenthèses vides, la fenêtre couvre l'ensemble du résultat (toutes les lignes sélectionnées).

Ajouter SUM(amount) OVER () comme 4ᵉ colonne te donne une nouvelle colonne grand_total où chaque ligne a la même valeur (le total global).

Le nombre de lignes ne change pas — tu obtiens juste une colonne de plus.

-- OVER () avec des parenthèses vides couvre l'ensemble du résultat
-- Les 5 lignes de détail restent, et grand_total porte la même valeur sur chaque ligne
SELECT sale_id, emp_id, amount,
  SUM(amount) OVER ()   AS grand_total,
  COUNT(*)    OVER ()   AS row_count,
  AVG(amount) OVER ()   AS grand_avg
FROM sales;

Imagine que tu veux une liste de ventes individuelles, avec le total global et la part de chaque vente dans ce total affichés à côté. (Si ta requête s'exécute correctement, une explication apparaîtra.)

① Sélectionne sale_id, emp_id et amount depuis sales.

② Ajoute SUM(amount) OVER () comme 4ᵉ colonne avec l'alias grand_total. Chaque ligne portera le même total global.

③ Ajoute *amount 100.0 / SUM(amount) OVER ()** comme 5ᵉ colonne avec l'alias pct_of_total — la part de chaque ligne dans le total global.

④ Le correcteur vérifie l'ordre des lignes, donc termine par ORDER BY sale_id.

Comparaison avec GROUP BY — réduire ou conserver les lignes

Mettre GROUP BY et une fonction de fenêtrage côte à côte rend la différence évidente.

Avec GROUP BY emp_id + SUM(amount), tu réduis les lignes de chaque employé en une seule ligne qui renvoie juste le total.

Avec SUM(amount) OVER (), chaque ligne de détail reste en place, et tu obtiens juste une colonne de plus avec la valeur agrégée.

Choisis l'un en fonction de ce que tu veux : juste les totaux, ou à la fois les totaux et le détail dans une même table.

-- GROUP BY : réduit à 1 ligne par employé — le détail disparaît
SELECT emp_id, COUNT(*) AS cnt, SUM(amount) AS total
FROM sales
WHERE emp_id = 2
GROUP BY emp_id;
-- résultat : 1 ligne (emp_id=2, cnt=5, total=2150000)

-- Fonction de fenêtrage : conserve les 5 lignes de détail, avec le total global à côté
SELECT sale_id, emp_id, amount,
  SUM(amount) OVER () AS grand_total
FROM sales
WHERE emp_id = 2;
-- résultat : 5 lignes, grand_total = 2150000 sur chaque ligne

Écris le même calcul « donne-moi le total » de deux façons — une fois avec GROUP BY et une fois avec OVER () — et compare les résultats côte à côte.

① Commence par la version GROUP BY : filtre sales sur emp_id = 2, puis sélectionne emp_id, COUNT(*) AS cnt et SUM(amount) AS total, et agrège avec GROUP BY emp_id. Regarde comment ça se réduit à 1 ligne.

② Ensuite écris la version fonction de fenêtrage : conserve les lignes de détail d'emp_id = 2 et ajoute SUM(amount) OVER () AS grand_total. Sélectionne sale_id, emp_id, amount, grand_total. Tu devrais obtenir 5 lignes, chacune avec le même total à côté.

③ Exécute les deux requêtes dans la même console et compare « 1 ligne » vs « 5 lignes » de tes propres yeux. Le correcteur regarde la sortie de la fonction de fenêtrage, donc termine ② par ORDER BY sale_id pour fixer l'ordre.

PARTITION BY — diviser la fenêtre en groupes

Là où OVER () traite l'ensemble du résultat comme une seule fenêtre (la plage de lignes à agréger), OVER (PARTITION BY column) divise la fenêtre selon les valeurs de cette colonne.

Avec PARTITION BY emp_id, la fenêtre est divisée par employé, et chaque ligne reçoit le total de son propre employé à côté.

Dans le schéma ci-dessous, les 4 lignes pour emp_id=1 reçoivent toutes le même « total emp_id=1 = 970 000 » ajouté, et les 5 lignes pour emp_id=2 reçoivent « total emp_id=2 = 2 150 000 ».

-- Ajouter le total, la moyenne et le compte par employé à chaque ligne de détail
SELECT sale_id, emp_id, amount,
  SUM(amount)   OVER (PARTITION BY emp_id) AS emp_total,
  AVG(amount)   OVER (PARTITION BY emp_id) AS emp_avg,
  COUNT(*)      OVER (PARTITION BY emp_id) AS emp_count
FROM sales
ORDER BY emp_id, sale_id;

-- Pourcentage du total de l'employé : quelle est la part de cette vente dans le total de cet employé ?
SELECT sale_id, emp_id, amount,
  amount * 100.0 / SUM(amount) OVER (PARTITION BY emp_id) AS pct_of_emp
FROM sales
ORDER BY emp_id, sale_id;

Imagine que tu veux afficher chaque vente à côté du total, de la moyenne et du compte des ventes de cet employé.

① Sélectionne sale_id, emp_id et amount depuis sales.

② À l'aide d'une fenêtre partitionnée par employé, ajoute trois colonnes avec les alias emp_total (somme), emp_avg (moyenne) et emp_count (compte).

③ Le correcteur vérifie l'ordre des lignes, donc termine par ORDER BY emp_id, sale_id (ascendant par emp_id, puis par sale_id au sein de chaque employé).

Combiner avec JOIN pour obtenir des agrégats par département

Les fonctions de fenêtrage fonctionnent aussi sur les résultats de JOIN.

Fais un JOIN entre sales et employee pour récupérer dept_id (identifiant du département), puis PARTITION BY e.dept_id te permet d'attacher le total des ventes du département de cet employé à chaque ligne de vente.

La colonne de PARTITION BY peut être une colonne récupérée d'une autre table via JOIN.

Les fonctions de fenêtrage sont évaluées après FROM / JOIN / WHERE, une fois que l'ensemble de lignes est fixé — le flux est donc « JOIN pour assembler les lignes dont tu as besoin → distribuer les agrégats de fenêtrage sur cet ensemble de lignes ».

-- JOIN entre sales et employee, ajout du total et du compte par département à chaque détail
SELECT s.sale_id, e.name, e.dept_id, s.amount,
  SUM(s.amount) OVER (PARTITION BY e.dept_id) AS dept_total,
  COUNT(*)      OVER (PARTITION BY e.dept_id) AS dept_sales
FROM sales AS s
JOIN employee AS e ON s.emp_id = e.emp_id
ORDER BY e.dept_id, s.sale_id;

Imagine que tu veux un rapport listant chaque vente avec le nom et le département de l'employé, plus le total des ventes pour ce département à côté.

① INNER JOIN sales (avec l'alias s) avec employee (avec l'alias e) sur s.emp_id = e.emp_id.

② Sélectionne s.sale_id, e.name, e.dept_id et s.amount.

③ Avec une fenêtre partitionnée par département (PARTITION BY e.dept_id), ajoute dept_total comme 5ᵉ colonne montrant le total des ventes du département.

④ Le correcteur vérifie l'ordre des lignes, donc termine par ORDER BY e.dept_id, s.sale_id (ascendant par dept_id, puis par sale_id au sein de chaque département).