Comment utiliser la clause PARTITION BY en SQL

Aujourd'hui, nous allons nous intéresser aux fonctions de fenêtre. Plus précisément, nous allons nous concentrer sur la fonction PARTITION BY et expliquerons ce qu'elle fait.

PARTITION BY est l'une des clauses utilisées dans les fonctions de fenêtre. En SQL, les fonctions de fenêtre sont utilisées pour organiser les données en groupes et calculer des statistiques pour ceux-ci. Cela semble terriblement familier, n'est-ce pas ? Même si elles se ressemblent, les fonctions de fenêtre et GROUP BY ne sont pas les mêmes. Les fonctions de fenêtre ressemblent plutôt à GROUP BY sous stéroïdes. Pourquoi ? Parce que les fonctions de fenêtre conservent les détails des lignes individuelles tout en calculant des statistiques pour les groupes de lignes. GROUP BY ne peut pas faire cela !

La clause PARTITION BY est cruciale pour cette distinction ; il s'agit de la clause qui divise le résultat d'une fonction fenêtre en sous-ensembles de données ou partitions. D'une certaine manière, c'est GROUP BY pour les fonctions de fenêtre. Vous apprendrez bientôt comment cela fonctionne.

Il existe une version beaucoup plus complète (et interactive) de cet article - notre Fonctions de fenêtrage cours. Il couvre tout ce dont nous allons parler et bien plus encore. Grâce à ses exercices interactifs, vous apprendrez tout ce que vous devez savoir sur les fonctions de fenêtre. Vous parcourrez les clauses OVER(), PARTITION BY et ORDER BY et apprendrez à utiliser les fonctions de fenêtre de classement et d'analyse. Le cours vous propose également 47 exercices pour vous entraîner et un quiz final. Si vous êtes indécis, voici pourquoi vous devriez apprendre les fonctions de fenêtre.

Maintenant, parlons de PARTITION BY!

Syntaxe de la clause PARTITION BY

La syntaxe de la clause PARTITION BY est la suivante :

SELECT column_name,
  	 window_function (expression) OVER (PARTITION BY column name)
FROM table;

Dans la partie window_function, vous mettez la fonction fenêtre spécifique.

La clause OVER() est une clause obligatoire qui fait fonctionner la fonction de fenêtre. Elle définit virtuellement la fonction de fenêtre.

La sous-clause PARTITION BY est suivie du nom de la ou des colonnes. La ou les colonnes que vous spécifiez dans cette clause seront les partitions/groupes dans lesquels les résultats de la fonction fenêtre seront regroupés.

Les exemples suivants vous permettront d'y voir plus clair. Nous savons que vous ne pouvez pas tout mémoriser immédiatement, alors n'hésitez pas à garder notre aide-mémoire SQL Fonctions de fenêtrage à portée de main pendant que nous parcourons les exemples. Il s'agit d'un rappel pratique des différentes fonctions de fenêtre et de leur syntaxe.

Exemples de PARTITION BY

L'ensemble de données de l'exemple se compose d'une table, employees. Voici ses colonnes :

• id - L'ID de l'employé.
• first_name - Le prénom de l'employé.
• last_name - Le nom de famille de l'employé.
• job_title - Le titre du poste de l'employé.
• department - Le département de l'employé.
• date_of_employment - La date à laquelle l'employé a commencé à travailler.
• salary - Le salaire de l'employé.

Jetez un coup d'œil aux données de la table avant de commencer à écrire le code :

Si vous souhaitez suivre en écrivant vos propres requêtes SQL, voici le code pour créer cet ensemble de données.

Utilisation de OVER (PARTITION BY)

Il est maintenant temps de vous montrer comment PARTITION BY fonctionne sur un ou deux exemples.

Exemple 1

Dans le premier exemple, l'objectif est de montrer les salaires des employés et le salaire moyen pour chaque département. Si vous avez été attentif, vous savez déjà comment PARTITION BY peut nous aider ici :

SELECT first_name,
	 last_name,
	 job_title,
	 department,
	 salary,
	 AVG(salary) OVER (PARTITION BY department) AS average_salary_by_department
FROM employees;

Pour calculer la moyenne, vous devez utiliser la fonction d'agrégation AVG(). Inscrivez la colonne salary entre parenthèses. Il s'agit, pour l'instant, d'une fonction d'agrégation ordinaire. Pour en faire une fonction d'agrégation de fenêtre, écrivez la clause OVER().

Maintenant, rappelez-vous que nous n'avons pas besoin de la moyenne totale (c'est-à-dire pour l'ensemble de l'entreprise) mais de la moyenne par département. Pour avoir cette métrique, mettez la colonne département dans la clause PARTITION BY.

Est-ce que cela renvoie le résultat souhaité ? Voyons voir !

Vous pouvez voir que le résultat liste tous les employés et leurs salaires. Pour le département informatique, le salaire moyen est de 7 636,59. Cette valeur est répétée pour tous les employés du département informatique.

Lorsque nous arrivons aux employés d'un autre département, la moyenne change. Dans ce cas, elle est de 6 418,12 pour le département Marketing. En suivant cette logique, le salaire moyen en Gestion des risques est de 6 760,01.

Comme vous pouvez le constater, PARTITION BY a utilisé la fonction fenêtre pour calculer la moyenne du département.

En quoi cela diffère-t-il de GROUP BY? Voyons ce qui se passe si nous calculons le salaire moyen par département en utilisant GROUP BY.

Comme vous pouvez le constater, vous obtenez les mêmes salaires moyens par département. Cependant, la différence majeure est que vous n'obtenez pas le salaire de chaque employé. Vous pouvez approfondir cette différence en lisant un article sur la différence entre PARTITION BY et GROUP BY.

Exemple n° 2

Nous voulons maintenant afficher les salaires de tous les employés, ainsi que le salaire le plus élevé par fonction.

La requête est très similaire à la précédente. Les deux seuls changements sont la fonction d'agrégation et la colonne dans PARTITION BY.

SELECT first_name,
	 last_name,
	 job_title,
	 department,
	 salary,
	 MAX(salary) OVER (PARTITION BY job_title) AS max_salary_by_job_title
FROM employees;

Cette fois, nous utilisons la fonction d'agrégation MAX() et nous séparons le résultat par titre de poste.

Voici le résultat :

Regardez les deux premières lignes. Bob Mendelsohn et Frances Jackson sont des analystes de données travaillant respectivement dans la gestion des risques et le marketing. Le tableau indique leurs salaires et le salaire le plus élevé pour ce poste. Il s'agit de 5 412,47, le salaire de Bob Mendelsohn.

La même logique s'applique au reste des résultats. Bien sûr, lorsqu'il n'y a qu'un seul titre de poste, le salaire de l'employé et le salaire maximum pour ce titre de poste seront les mêmes. C'est le cas pour l'ingénieur de données et l'analyste de système.

Cet exemple peut également montrer les limites de GROUP BY.

Le code ci-dessous affichera le salaire le plus élevé selon l'intitulé de poste :

SELECT job_title,
	 MAX(salary) AS max_salary_by_job_title
FROM employees
GROUP BY job_title;

Et voici le résultat :

Oui, les salaires sont les mêmes qu'avec PARTITION BY. Mais avec ce résultat, vous n'avez aucune idée du salaire de chaque employé et de celui qui a le salaire le plus élevé.

Utilisation de OVER (ORDER BY)

La clause ORDER BY est une autre sous-clause de la fonction fenêtre. Elle ordonne les données dans une partition ou, si la partition n'est pas définie, dans l'ensemble du jeu de données.

Lorsque nous parlons d'ordre, nous ne parlons pas de la sortie. Lorsqu'elle est utilisée avec les fonctions de fenêtre, la clause ORDER BY définit l'ordre dans lequel une fonction de fenêtre effectuera son calcul.

ORDER BY peut être utilisée avec ou sans PARTITION BY.

Voyons d'abord comment elle fonctionne sans PARTITION BY. Nous allons l'utiliser pour afficher les données des employés et les classer en fonction de leur date d'embauche. Le classement se fera de la date la plus ancienne à la date la plus récente.

SELECT first_name,
	 last_name,
	 job_title,
	 department,
	 date_of_employment,
	 RANK() OVER (ORDER BY date_of_employment ASC) AS employment_date_rank
FROM employees;

La fonction de fenêtre que nous utilisons maintenant est RANK(). C'est l'une des fonctions utilisées pour classer les données. Là encore, la clause OVER() est obligatoire.

La clause ORDER BY indique à la fonction de classement d'attribuer des rangs en fonction de la date d'embauche par ordre décroissant.

Exécutez la requête et vous obtiendrez ce résultat :

Tous les employés sont classés en fonction de leur date d'embauche. La première personne employée est classée première et la dernière est classée dixième.

Utilisation de OVER (PARTITION BY ORDER BY)

Comme nous l'avons déjà mentionné, PARTITION BY et ORDER BY peuvent également être utilisés simultanément. Voyons quelques exemples.

Exemple 1

Imaginez que vous deviez classer les employés de chaque département en fonction de leur salaire. Comment allez-vous procéder ?

Voici la solution :

SELECT first_name,
	 last_name,
	 job_title,
	 department,
	 salary,
	 RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
FROM employees;

Nous utilisons à nouveau la fonction fenêtre RANK(). Dans la clause OVER(), les données doivent être partitionnées par département. Pour trier les employés, utilisez la colonne salaire dans ORDER BY et triez les enregistrements par ordre décroissant.

Voyons ce que donne cette requête :

Dans le département informatique, Carolina Oliveira a le salaire le plus élevé. Viennent ensuite Ines Owen et Walter Tyson, tandis que le dernier est Sean Rice. Ils sont tous classés en conséquence.

Lorsque la fonction fenêtre arrive au département suivant, elle se réinitialise et reprend le classement depuis le début. Ainsi, Franck Monteblanc est le mieux payé, tandis que Simone Hill et Frances Jackson arrivent respectivement en deuxième et troisième position.

La même chose est faite avec les employés de la gestion des risques.

Exemple n° 2

Mettons cela en pratique dans un exemple légèrement différent. Nous voulons toujours classer les employés par salaire. Cette fois, ce n'est pas par département mais par titre de poste.

Voici comment procéder.

SELECT first_name,
	 last_name,
	 job_title,
	 department,
	 salary,
	 RANK() OVER (PARTITION BY job_title ORDER BY salary DESC) AS salary_rank
FROM employees;

Les données sont maintenant partitionnées par titre de poste. La clause ORDER BY reste la même : elle trie toujours dans l'ordre décroissant par salaire.

Voici le résultat de la requête :

La logique est la même que dans l'exemple précédent. Dans cet exemple, il y a un maximum de deux employés avec le même titre de poste, donc les rangs ne vont pas plus loin.

Bob Mendelsohn est le mieux payé des deux analystes de données. Ensuite, il n'y a que le rang 1 pour l'ingénieur de données parce qu'il n'y a qu'un seul employé avec ce titre de poste. Le reste des données est trié selon la même logique.

Vous trouverez d'autres exemples dans cet article sur les fonctions de fenêtre en SQL. Et si la connaissance des fonctions de fenêtre vous donne envie d'une meilleure carrière, vous serez heureux d'apprendre que nous avons répondu pour vous aux 10 principales questions d'entretien sur les fonctions de fenêtre en SQL.

Quand utiliser PARTITION BY

Nous avons répondu à la question "comment". La deuxième question importante à laquelle il faut répondre est de savoir quand vous devez utiliser PARTITION BY.

Il existe deux utilisations principales. La première consiste à regrouper des données et à calculer certaines métriques, tout en conservant les lignes individuelles et leurs valeurs.

La deuxième utilisation de PARTITION BY est lorsque vous voulez regrouper des données en deux groupes ou plus et calculer des statistiques pour ces groupes.

PARTITION BY doit avoir chatouillé votre curiosité

PARTITION BY est une clause merveilleuse avec laquelle il faut être familier. Non seulement elle signifie que vous connaissez les fonctions de fenêtre, mais elle augmente également votre capacité à calculer des statistiques en vous amenant au-delà des clauses obligatoires utilisées dans les fonctions de fenêtre.

Vous souhaitez satisfaire votre curiosité quant aux possibilités des fonctions de fenêtre et du site PARTITION BY? Fonctions de fenêtrage n'attend plus que vous !