Syntaxe de OVER() et ses points clés

OVER() — c’est une instruction qui dit à SQL sur quel ensemble de lignes il faut appliquer une window function. On peut dire que c’est une façon de définir la “fenêtre” de données pour appliquer la window function. Imagine que t’as une pièce pleine de gens, et tu veux compter combien de personnes se tiennent sur chaque mètre carré du sol. OVER() va indiquer sur quelle partie de la pièce tu vas te concentrer. En d’autres termes, il définit sur quel ensemble de lignes la fonction va bosser.

L’opérateur OVER() s’utilise uniquement avec les window functions pour faire des opérations sur les lignes d’une ou plusieurs tables, sans faire de group by.

Syntaxe :

window_function() OVER (
    [PARTITION BY ...]
    [ORDER BY ...]
    [ROWS/RANGE ...]
)

Où :

• PARTITION BY — divise l’ensemble de données en groupes logiques
• ORDER BY — définit l’ordre des lignes à l’intérieur de chaque groupe
• ROWS/RANGE — précise la taille de la “fenêtre” (par exemple, la ligne courante + 1 suivante)

Exemple : OVER() sans paramètres

Quand OVER() est utilisé sans paramètres supplémentaires, ça veut dire que la fonction juste avant va bosser sur tout l’ensemble de données.

SELECT
    employee_id,
    salary,
    ROW_NUMBER() OVER () AS row_num -- ROW_NUMBER() sera appliqué à toutes les lignes du résultat
FROM employees;

Qu’est-ce qui se passe ?

1. ROW_NUMBER() attribue un numéro unique à chaque ligne.
2. Comme il n’y a pas de paramètres dans OVER(), toutes les lignes de la table employees sont traitées comme un seul bloc.

Résultat :

Utiliser PARTITION BY pour créer des groupes

Ok, maintenant imagine que tu veux numéroter les employés non pas sur toute la boîte, mais à l’intérieur de chaque département. C’est là que PARTITION BY entre en jeu.

PARTITION BY dans OVER() divise les données en groupes (ou “partitions”). Pour chaque groupe, la fonction calcule la valeur séparément. Genre, si ROW_NUMBER() était un serveur, il recommencerait la numérotation à chaque “table” (partition).

Exemple : on utilise PARTITION BY

SELECT
    department_id,
    employee_id,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department_id) AS row_num
FROM employees;

Qu’est-ce qui se passe ?

1. Les données de la table employees sont divisées en groupes selon la valeur de department_id.
2. Dans chaque groupe, les lignes reçoivent un numéro d’ordre via ROW_NUMBER().

Résultat :

Utiliser ORDER BY pour définir l’ordre

Maintenant, ajoutons un peu de structure. Imagine que tu veux pas juste numéroter les lignes, mais le faire dans un ordre précis, genre en commençant par le plus gros salaire. Cette tâche se règle avec ORDER BY.

ORDER BY définit l’ordre dans lequel les lignes vont être traitées par la window function.

Exemple : on utilise ORDER BY dans OVER()

SELECT
    department_id,
    employee_id,
    salary,
    RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
FROM employees;

Qu’est-ce qui se passe ?

1. Les données sont divisées en groupes (PARTITION BY department_id).
2. Dans chaque groupe, les lignes sont triées par salaire décroissant (ORDER BY salary DESC).
3. Chaque ligne reçoit un rang selon le tri.

Résultat :

Combiner plusieurs window functions

SQL te permet d’utiliser plusieurs window functions dans une même requête, et chacune peut bosser avec ses propres règles. C’est comme si dans une pièce, y’avait de la musique et quelqu’un qui compte les gens en même temps — chaque process est indépendant !

Exemple : plusieurs window functions

SELECT
    department_id,
    employee_id,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS row_num,
    AVG(salary) OVER (PARTITION BY department_id) AS avg_salary
FROM employees;

Qu’est-ce qui se passe ?

1. ROW_NUMBER() numérote les lignes dans chaque groupe par salaire décroissant.
2. AVG() calcule le salaire moyen dans chaque groupe.

Résultat :

Exemples dans la vraie vie

Les window functions avec OVER() sont utilisées dans plein de scénarios réels. Voilà quelques exemples :

• Analyse des ventes : classement des produits par nombre de ventes dans chaque catégorie.
• Classements : déterminer la position des étudiants dans chaque groupe selon la moyenne.
• Séries temporelles : somme cumulative des ventes dans le temps.

Exemple d’analyse des ventes :

SELECT
    category_id,
    product_id,
    product_name,
    SUM(sales) OVER (PARTITION BY category_id ORDER BY sales DESC) AS cumulative_sales
FROM products;

Erreurs fréquentes avec les window functions

1. Oubli de PARTITION BY

Si tu n’utilises pas PARTITION BY, la window function s’applique à toute la table. Ça peut donner des résultats inattendus, surtout si tu voulais un découpage par groupes.

---

2. Types de données incorrects dans ORDER BY

ORDER BY dans une window function est sensible aux types de données. Si tu tries sur un champ date stocké en texte (VARCHAR), l’ordre sera alphabétique, pas chronologique.

3. Mauvaise utilisation de ROWS BETWEEN

Par défaut, les window functions bossent avec des frames définies par ROWS BETWEEN. Si tu précises pas la frame, le comportement RANGE peut s’appliquer, ce qui peut renvoyer plus de lignes que prévu.

4. Mauvaise gestion des NULL

Les window functions peuvent gérer les NULL différemment. Par exemple, RANK() et DENSE_RANK() vont considérer NULL comme une valeur et lui donner un rang à part.

5. Utiliser des window aggregates au lieu des agrégats classiques

Parfois on utilise des window aggregates (SUM() OVER(...)) là où un simple agrégat avec GROUP BY suffit, ce qui complique la requête et la ralentit.