COMPTER SUR LA PARTITION PAR : Une explication avec 3 exemples pratiques

En SQL, la combinaison de la fonction COUNT() avec la clause OVER() et PARTITION BY ouvre la porte à une manière totalement différente de compter les lignes. Dans cet article, vous apprendrez à effectuer plusieurs COUNT dans une seule requête et à calculer des expressions basées sur le résultat de la fonction COUNT.

Dans cet article, nous verrons comment vous pouvez utiliser la fonction COUNT() combinée avec les clauses OVER() et PARTITION BY. Pour en savoir plus sur ce sujet, je vous recommande notre cours interactif Fonctions de fenêtrage. Il contient plus de 200 exercices interactifs sur l'utilisation de la clause OVER() avec les fonctions window. Après avoir terminé ce cours, vous aborderez ce sujet avec facilité et vous vous sentirez à l'aise dans l'utilisation des fonctions window dans les bases de données SQL.

Exemple #1 : Introduction à l'utilisation de COUNT OVER PARTITION BY

Supposons que nous ayons une table appelée order contenant un enregistrement pour chaque commande reçue dans une animalerie. La table comporte des colonnes telles que order_id, order_date, customer_id, salesperson_id, ship_address, ship_state et amount_paid.

La requête suivante indique les commandes reçues par l'entreprise au cours du premier semestre 2023. Notez que nous avons ajouté une colonne supplémentaire appelée orders_this_customer qui indique le nombre total de commandes envoyées par chaque client au cours de cette période.

SELECT 
  order_id,
  order_date,
  customer_id,
  amount_paid,
 COUNT(*) OVER (PARTITION BY customer_id) AS orders_this_customer
FROM order
WHERE order_date >= '2023-01-01' AND order_date <= '2023-06-30';

En SQL, nous utilisons la fonction COUNT() seule ou combinée avec la clause GROUP BY pour compter les lignes d'un ensemble de résultats ou d'un groupe de lignes. OVER() et PARTITION BY appliquent la fonction COUNT() à un groupe de lignes défini par PARTITION BY. Dans notre exemple, le groupe est défini par les valeurs de la colonne customer_id. La fonction COUNT() compte le nombre de commandes ayant la même valeur customer_id.

La combinaison de COUNT() et OVER(PARTITION BY) est plus puissante que l'utilisation de la fonction COUNT() seule, car elle nous permet d'obtenir le nombre de lignes pour chaque valeur spécifique d'une colonne.

En utilisant OVER() et PARTITION BY, il n'est pas nécessaire d'utiliser la clause GROUP BY pour grouper les enregistrements ; cela nous permet d'avoir des ensembles de résultats au niveau des lignes. Chaque ligne de l'ensemble de résultats contiendra des informations au niveau de la commande, mais la valeur de la colonne orders_this_customer indiquera le nombre total de commandes passées par chaque client.

En d'autres termes, nous combinons des données au niveau de l'état avec des données au niveau du client dans la même ligne. Nous pouvons ajouter d'autres niveaux de données en utilisant différentes colonnes dans la clause PARTITION BY. Vous trouverez ci-dessous un résultat partiel de cette requête :

Avant de terminer cette section, j'aimerais vous suggérer l'article La fonction SQL COUNT() expliquée avec 7 exemples, où vous trouverez de nombreux exemples de requêtes utilisant la fonction COUNT(). Pour les lecteurs qui souhaitent approfondir le sujet, je recommande l'article Comment utiliser la fonction SQL PARTITION BY avec OVER où vous trouverez une explication claire avec des exemples des clauses OVER() et PARTITION BY.

Exemple #2 : Calculer les totaux en fonction de différents critères

Avant de continuer, montrons l'ordre complet du tableau. Chaque ligne du tableau représente une commande reçue par l'animalerie. Une ligne de commande possède notamment les colonnes customer_id, salesperson_id, order_date, ship_state et ship_city; les autres colonnes sont explicites. Voici une vue partielle du tableau :

Supposons que l'animalerie souhaite obtenir, à la fin du mois, un rapport reprenant toutes les commandes. Pour chaque ligne, elle souhaite également afficher deux champs calculés : le nombre total de commandes vendues ce jour-là et le nombre total de commandes vendues par ce vendeur. La requête permettant d'obtenir ce rapport est la suivante :

SELECT 
  order_id,
  order_date,
  customer_id,
  salesperson_id,
  COUNT(1) OVER (PARTITION BY order_date) as orders_per_day,
  COUNT(1) OVER (PARTITION BY salesperson_id) as orders_per_salesperson
FROM order
WHERE order_date between '2023-06-01' AND '2023-06-30';

Le résultat de la requête est présenté ci-dessous :

Dans la requête ci-dessus, nous avons utilisé l'expression COUNT(1), qui fonctionne de la même manière que COUNT(*). La clause PARTITION BY indique les critères de regroupement des lignes à compter.

Pour obtenir le champ orders_per_day, nous utilisons la clause OVER (PARTITION BY order_date); pour le champ orders_per_salesperson, nous utilisons la clause OVER (PARTITION BY salesperson_id).

Ici, notez que la seule différence est le champ que l'on place après la clause PARTITION BY. Ce champ définit les critères de comptage des lignes, c'est-à-dire que toutes les lignes ayant la même valeur dans ce champ seront comptées ensemble.

L'effet de ces deux champs dans le résultat de la requête est que nous ajoutons deux champs de niveaux de granularité différents à la ligne ; le champ orders_per_salesperson est un total groupé par vendeur, tandis que le champ orders_per_day est un total groupé par date de commande.

Remarque : COUNT(DISTINCT) ne fonctionne pas avec OVER(PARTITION BY)

Il existe plusieurs variantes de la fonction COUNT(), telles que COUNT(*), COUNT(1) ou COUNT(DISTINCT). Pour en savoir plus, consultez notre article Quelle est la différence entre COUNT(*), COUNT(1), COUNT(nom de colonne) et COUNT(DISTINCT nom de colonne) ? Vous y trouverez une explication claire de ces différentes façons d'utiliser la fonction COUNT().

Dans certains cas - par exemple, si nous devons compter combien de personnes différentes ont passé des commandes au cours d'une journée donnée - nous pourrions penser à utiliser l'expression COUNT(DISTINCT customer_id) OVER (PARTITION BY order_date). Cependant, il est important de préciser que COUNT(DISTINCT) OVER(PARTITION BY) n'est pas pris en charge par la plupart des bases de données courantes (comme PostgreSQL, SQL Server et Snowflake, entre autres).

Exemple n° 3 : utilisation de COUNT() avec OVER dans les expressions

Dans l'exemple de requête suivant, nous allons calculer des pourcentages en utilisant des expressions arithmétiques avec deux COUNTs différents.

Supposons que le service marketing souhaite cibler la prochaine campagne de marketing sur un groupe précis de clients. Pour ce faire, il a besoin de quelques indicateurs sur les résultats commerciaux : le pourcentage de commandes reçues de chaque État, le pourcentage de commandes reçues pour chaque famille de produits et la combinaison des deux indicateurs (par exemple, le pourcentage de commandes reçues du Texas pour la famille de produits DOG). La requête permettant d'obtenir un tel rapport est la suivante :

SELECT DISTINCT
  ship_state,
  product_family,
  COUNT(1) OVER () AS total_orders,
  COUNT(1) OVER (PARTITION BY ship_state) state_orders,
  COUNT(1) OVER (PARTITION BY ship_state) / COUNT(1) OVER () AS state_percentage,
  COUNT(1) OVER (PARTITION BY product_family) AS family_orders,
  COUNT(1) OVER (PARTITION BY product_family) / COUNT(1) OVER () AS family_percentage
FROM   order
ORDER BY ship_state, product_family

Dans la requête ci-dessus, le champ total_orders utilise l'expression COUNT(1) OVER () pour calculer la quantité totale de commandes reçues par l'entreprise. Ensuite, dans le champ state_orders, l'expression COUNT() est utilisée pour obtenir la quantité de commandes reçues à partir de l'état de la ligne actuelle (par exemple, si la valeur ship_state de la ligne actuelle est TX (Texas), cela montrera toutes les commandes provenant du Texas) ; ce champ est très similaire à ce que nous avons calculé dans les exemples précédents.

La partie intéressante de cet exemple de requête réside dans l'expression du champ state_percentage, qui utilise deux COUNTs pour calculer un pourcentage. En d'autres termes, nous divisons le nombre de commandes de l'état actuel (l'expression rouge) par le nombre total de commandes (l'expression bleue). Dans les deux derniers champs, nous répétons la même approche pour le champ product_family.

Le résultat de la requête est présenté ci-dessous :

Utilisation de OVER PARTITION BY avec COUNT() et d'autres fonctions

Dans cet article, nous avons abordé la fonction COUNT() combinée aux clauses OVER et PARTITION BY. Cependant, en SQL, il existe de nombreuses autres fonctions qui peuvent être combinées avec PARTITION BY. Ces fonctions sont appelées fonctions de fenêtre, et vous trouverez un excellent matériel d'apprentissage à leur sujet dans notre coursFonctions de fenêtrage . Il comporte plus de 200 exercices interactifs et offre un tutoriel complet sur les fonctions de fenêtre.

Si vous connaissez déjà les fonctions de fenêtre et que vous souhaitez vous entraîner davantage, je vous recommande notre Fonctions de fenêtrage Practice Set. Ce cours a été conçu pour offrir une pratique complète des fonctions de fenêtre sur des exemples du monde réel. Ses 100 exercices sur trois ensembles de données différents ressemblent aux problèmes que vous rencontrerez dans le monde réel.

Si vous cherchez d'autres ressources sur les fonctions de fenêtre, jetez un coup d'œil à notre feuille de contrôle SQL Fonctions de fenêtrage et aux 10 meilleures questions d'entretien SQL Fonctions de fenêtrage , où vous trouverez du matériel pour vous préparer à un entretien d'embauche en SQL. Foncez, apprenez le SQL et investissez en vous !