Coding Interview Prep · Leçon

OVER, PARTITION BY et ORDER BY

Anatomie d’une spécification de fenêtre et rôle des partitions dans la réinitialisation du calcul

OVER, PARTITION BY et ORDER BY est une leçon Coding Interview Prep gratuite sur CoddyKit. Ceci est la leçon 1 sur 4. Tu peux lire la leçon complète ci-dessous gratuitement — puis la pratiquer en direct dans le navigateur avec un éditeur de code intégré et un tuteur IA 24/7. Elle fait partie du parcours d'apprentissage Coding Interview Prep, et ta progression se synchronise sur le web et l'application CoddyKit. Le cours Coding Interview Prep comprend 4 leçons au total.

Pourquoi les intervieweurs choisissent les fonctions de fenêtrage

Une fonction de fenêtrage effectue un calcul sur un ensemble de lignes associées à la ligne actuelle, sans les regrouper comme le fait GROUP BY. Cette seule propriété explique pourquoi les intervieweurs les apprécient : vous conservez chaque ligne détaillée tout en obtenant à ses côtés un agrégat, un rang ou un total cumulatif.

GROUP BY renvoie une ligne par groupe.
Fonction de fenêtrage renvoie chaque ligne d'entrée, avec une colonne calculée supplémentaire.

Lorsqu'une personne qui mène l'entretien dit « affichez chaque employé et le salaire moyen de son service sur la même ligne », elle vérifie que vous choisissez une fonction de fenêtrage plutôt qu'une jointure de la table avec elle-même.

Anatomie de la clause OVER

Toute fonction de fenêtrage est suivie d'une clause OVER (...). Cette clause comporte trois parties facultatives, et les nommer précisément impressionne les intervieweurs :

PARTITION BY — divise les lignes en groupes ; la fonction recommence dans chacun d'eux.
ORDER BY — ordonne les lignes à l'intérieur de chaque partition, ce qui est nécessaire pour les classements et les totaux cumulatifs.
Cadre — limite les lignes utilisées pour le calcul (ROWS/RANGE).

Un OVER () vide considère l'ensemble des résultats comme une seule partition.

SELECT
  name,
  department,
  salary,
  AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;

Fenêtrage et agrégation : même fonction, résultat différent

La même fonction d'agrégation se comporte différemment lorsqu'elle est utilisée comme fonction de fenêtrage. Comparez conceptuellement les deux requêtes ci-dessous.

AVG(salary) avec GROUP BY department renvoie une ligne par service.
AVG(salary) OVER (PARTITION BY department) renvoie chaque employé, en associant à chacun la moyenne de son service.

Conseil pour l'entretien : insistez sur le fait que la version fenêtrée n'exige pas de GROUP BY et ne supprime pas les lignes détaillées en double.

-- Aggregate: collapses
SELECT department, AVG(salary)
FROM employees
GROUP BY department;

-- Window: preserves every row
SELECT department, name, AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;

PARTITION BY : Réinitialiser le calcul

PARTITION BY joue pour les fonctions de fenêtrage le même rôle que GROUP BY pour les agrégats, sauf qu'il ne regroupe pas les lignes en une seule. Chaque valeur distincte de partition bénéficie de son propre calcul indépendant.

Dans l'exemple, la numérotation des lignes recommence à 1 pour chaque département. Sans PARTITION BY, la numérotation continuerait sur l'ensemble des employés.

Vous pouvez partitionner selon une seule colonne ou plusieurs.
L'absence de PARTITION BY crée une seule partition immense (l'ensemble complet).

SELECT
  department,
  name,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
FROM employees;

ORDER BY dans OVER

Le ORDER BY à l'intérieur de OVER n'est pas identique au ORDER BY final de la requête. Il définit uniquement l'ordre des lignes au sein de chaque partition sur lequel la fonction va opérer.

Les fonctions de classement (ROW_NUMBER, RANK) l'exigent : elles ont besoin d'un ordre pour établir le classement.
Les agrégats simples appliqués à une partition n'en ont pas besoin, sauf si vous voulez effectuer un calcul cumulatif.

Une confusion fréquente en entretien consiste à confondre l'ORDER BY de la fenêtre avec l'ordre de présentation des résultats.

SELECT
  name,
  hire_date,
  ROW_NUMBER() OVER (ORDER BY hire_date) AS seniority_rank
FROM employees
ORDER BY name;  -- output order is independent of the window order

Combiner PARTITION BY et ORDER BY

La fenêtre de classement classique combine les deux : PARTITION BY regroupe, puis ORDER BY ordonne les lignes au sein de chaque groupe.

Lisez la spécification ci-dessous ainsi : « Dans chaque département, trier les employés par salaire décroissant, puis les numéroter. » La personne la mieux rémunérée de chaque département obtient le numéro de ligne 1.

Cette spécification unique constitue le fondement des problèmes d'entretien les plus courants sur les fonctions de fenêtrage, notamment la sélection des N premiers éléments par groupe.

SELECT
  department,
  name,
  salary,
  ROW_NUMBER() OVER (
    PARTITION BY department
    ORDER BY salary DESC
  ) AS dept_salary_rank
FROM employees;

ORDER BY modifie le comportement des agrégats

Voici un point subtil que les recruteurs vérifient en entretien : ajouter ORDER BY à une fenêtre d'agrégation la transforme en calcul cumulatif, car un cadre implicite (« du début de la partition à la ligne actuelle ») est appliqué.

SUM(x) OVER (PARTITION BY g) → le même total du groupe sur chaque ligne.
SUM(x) OVER (PARTITION BY g ORDER BY d) → un total cumulatif jusqu'à la ligne actuelle.

Savoir que ORDER BY ajoute implicitement un cadre permet de distinguer les candidats de niveau intermédiaire des candidats débutants.

SELECT
  account_id,
  txn_date,
  amount,
  SUM(amount) OVER (
    PARTITION BY account_id
    ORDER BY txn_date
  ) AS running_balance
FROM transactions;

Où les fonctions de fenêtrage sont autorisées

Les fonctions de fenêtrage peuvent apparaître uniquement dans la liste SELECT et dans la clause ORDER BY. Elles ne sont pas autorisées dans WHERE, GROUP BY ou HAVING.

La raison tient à l'ordre d'exécution logique : les fonctions de fenêtrage sont évaluées après l'exécution de WHERE, GROUP BY et HAVING. Les lignes sont déjà sélectionnées avant même que la fenêtre ne les examine.

C'est pourquoi filtrer selon un classement nécessite une sous-requête ou une CTE — un point traité en détail dans une leçon ultérieure.

-- This FAILS: window function in WHERE
-- SELECT name FROM employees
-- WHERE ROW_NUMBER() OVER (ORDER BY salary) = 1;

-- This works: window in SELECT, filter outside
SELECT * FROM (
  SELECT name, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
  FROM employees
) t
WHERE rn = 1;

Plusieurs fonctions de fenêtrage dans une même requête

Vous pouvez utiliser plusieurs fonctions de fenêtrage dans le même SELECT, chacune avec sa propre spécification ou avec une spécification partagée. La base de données les calcule en un seul parcours des données partitionnées.

C'est pratique en entretien lorsque vous avez besoin à la fois d'un classement et de la moyenne d'un département. Si deux fonctions partagent une spécification, certains dialectes vous permettent de lui donner un nom avec une clause WINDOW afin d'éviter les répétitions.

SELECT
  name,
  department,
  salary,
  ROW_NUMBER() OVER w  AS rn,
  AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees
WINDOW w AS (PARTITION BY department ORDER BY salary DESC);

Exemple détaillé : salaire et moyenne du département

Une question fréquente pour les analystes : « Afficher chaque employé avec son salaire, la moyenne de son département et l'écart. » Une seule expression de fenêtrage effectue l'essentiel du travail ; l'arithmétique fait le reste.

Remarquez qu'il n'y a pas de GROUP BY et que chaque ligne d'employé est conservée. La valeur de dept_avg se répète pour toutes les personnes du même département, ce qui permet précisément la comparaison ligne par ligne.

SELECT
  name,
  department,
  salary,
  AVG(salary) OVER (PARTITION BY department) AS dept_avg,
  salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_avg
FROM employees
ORDER BY department, salary DESC;

Erreurs courantes surveillées en entretien

Évitez ces pièges lorsque les fonctions de fenêtrage sont abordées :

Placer une fonction de fenêtrage dans WHERE ou HAVING — c'est interdit ; utilisez une sous-requête.
Oublier ORDER BY avec une fonction de classement — les résultats deviennent arbitraires.
Supposer que PARTITION BY réduit le nombre de lignes — ce n'est jamais le cas.
Confondre l'ORDER BY de la fenêtre avec l'ordre final des résultats.
Ajouter ORDER BY à une fenêtre d'agrégation sans réaliser qu'elle est devenue un total cumulatif.

Vérification rapide

Vérifiez votre compréhension de la spécification de fenêtre.

Récapitulatif : la spécification de fenêtre

Vous maîtrisez maintenant la structure de OVER (...) :

Les fonctions de fenêtrage conservent chaque ligne tout en effectuant des calculs sur les lignes associées.
PARTITION BY regroupe les lignes et réinitialise le calcul ; il ne supprime jamais de lignes.
ORDER BY ordonne les lignes au sein d'une partition ; les fonctions de classement l'exigent et il transforme les agrégats en calculs cumulatifs.
Les fonctions de fenêtrage sont autorisées uniquement dans SELECT et ORDER BY — jamais dans WHERE/HAVING.

Ensuite, vous attribuerez des numéros de séquence déterministes avec ROW_NUMBER.

Questions Fréquemment Posées

La leçon « OVER, PARTITION BY et ORDER BY » est-elle gratuite ?

Oui — le texte complet de « OVER, PARTITION BY et ORDER BY » est gratuit à lire ici sur le web. Pour la pratiquer de manière interactive (un éditeur de code intégré et un tuteur IA 24/7) et déverrouiller le reste du cours Coding Interview Prep, passe à CoddyKit PRO. Le cours Coding Interview Prep comprend 4 leçons au total.

Qu'est-ce que j'apprendrai dans « OVER, PARTITION BY et ORDER BY » ?

Anatomie d’une spécification de fenêtre et rôle des partitions dans la réinitialisation du calcul Tu pratiques Coding Interview Prep avec du code pratique que tu exécutes directement dans le navigateur, et un tuteur IA 24/7 répond à tes questions au fur et à mesure que tu avances dans la leçon.

Dois-je avoir de l'expérience pour commencer Coding Interview Prep ?

Aucune expérience préalable n'est requise. Coding Interview Prep sur CoddyKit est structuré pour les débutants jusqu'aux apprenants avancés, donc tu peux commencer ici ou depuis le début et avancer à ton rythme. Ceci est la leçon 1 sur 4.

Combien de temps prend la leçon « OVER, PARTITION BY et ORDER BY » ?

La plupart des leçons CoddyKit prennent environ 5–10 minutes. Chacune est courte et interactive, tu progresses régulièrement et tu repiques exactement où tu t'es arrêté sur le web et l'app.

Peux-tu écrire et exécuter du code dans cette leçon Coding Interview Prep ?

Oui. Chaque leçon Coding Interview Prep inclut un éditeur de code intégré, tu écris et exécutes du vrai code directement dans ton navigateur et tu reçois des retours IA instantanés — aucune configuration locale requise.

Toutes les leçons de ce cours

← Retour à Coding Interview Prep