Comment utiliser SUM() avec OVER(PARTITION BY) en SQL

Découvrez des cas d'utilisation réels de la fonction SUM() avec la clause OVER(PARTITION BY). Apprenez la syntaxe et consultez 5 exemples différents.

Nous utilisons les fonctions SQL window pour effectuer des opérations sur des groupes de données. Ces opérations comprennent les fonctions mathématiques SUM(), COUNT(), AVG(), et bien d'autres encore. Dans cet article, nous expliquerons ce que fait SUM() avec OVER(PARTITION BY) en SQL. Nous vous montrerons les cas d'utilisation les plus courants dans des applications réelles pour déterminer le rapport entre la valeur d'une ligne individuelle et la valeur totale, calculer les totaux courants et trouver un total de commande personnalisé qui inclut les remises pour certains produits.

Pour obtenir un guide approfondi de l'utilisation de SUM() avec OVER() et d'autres fonctions de fenêtre, suivez notre cours interactif de Fonctions de fenêtrage cours interactif. Il explique en détail tous les concepts des fonctions de fenêtre SQL et comporte plus de 200 exercices.

Commençons.

La clause OVER() en SQL

En SQL, la clause OVER() est utilisée pour introduire les fonctions de fenêtre. La syntaxe générale est la suivante

SELECT …
  <window function> OVER(...)
…

OVER() indique à la base de données que nous voulons utiliser des fonctions de fenêtre. La fonction fenêtre peut être une fonction agrégée, comme SUM(), ou une autre fonction fenêtre.

Une fonction de fenêtre travaille sur un "cadre de fenêtre", ou un ensemble de lignes liées à la ligne actuelle. OVER() définit le cadre de fenêtre pour chaque ligne. Une clause OVER() vide indique à la base de données que l'ensemble des résultats est le cadre de la fenêtre.

Examinons un exemple de la syntaxe SUM() OVER(). Nous utiliserons la table employees de l'échantillon de données fourni par Oracle Live SQL. Cette table se compose des colonnes suivantes :

• emp_id est l'identifiant de l'employé.
• name est le nom de l'employé.
• job est l'intitulé du poste.
• dept_id est l'identifiant du service.
• salary est le salaire de l'employé.

Nous pouvons utiliser la fonction SUM() avec la clause OVER() pour obtenir le salaire total de tous les employés et afficher le salaire total à côté du salaire de chaque employé.

SELECT 
  emp_id, 
  name, 
  job, 
  dept_id, 
  salary, 
  SUM(salary) OVER() AS total_salary
FROM employees;

La fonction SUM() est exécutée pour chaque ligne du tableau. Chaque fois qu'elle est appelée, elle récupère les informations salariales de toutes les lignes du tableau pour calculer le montant total du salaire. Le salaire total est affiché à côté des détails de chaque ligne. Les détails de chaque ligne sont conservés et affichés à côté du salaire total.

Dans cet exemple, le cadre de la fenêtre (l'ensemble des lignes sur lesquelles SUM() opère) est l'ensemble des données. Vous pouvez ajouter des clauses supplémentaires dans OVER() pour modifier le cadre de la fenêtre.

Exemple 1A : Calcul de la somme pour chaque groupe - SUM() avec OVER(PARTITION BY ...)

La clause OVER() peut contenir des détails sur la façon dont nous voulons partitionner les données. Nous utilisons la clause PARTITION BY dans OVER() pour diviser les données en partitions, ou groupes. L'utilisation de PARTITION BY est similaire à celle de GROUP BY dans la mesure où les lignes sont divisées en groupes sur la base de la valeur de certaines colonnes. Lorsque nous utilisons SUM() OVER(PARTITION BY …), nous pouvons calculer la somme des valeurs pour chaque groupe, ou partition, de données. Par exemple, nous pouvons calculer le salaire total pour chaque département :

SELECT 
  emp_id, 
  name, 
  job, 
  dept_id, 
  salary,
  SUM(salary) OVER(PARTITION BY dept_id) AS dept_total_salary
FROM employees;

Voici le résultat :

Nous utilisons SUM(salary) OVER(PARTITION BY dept_id) pour obtenir le salaire total par département. La clause PARTITION BY divise les lignes en groupes sur la base de la colonne dept_id. Les lignes dont la valeur dept_id est égale à 10 sont placées dans un groupe (marqué en jaune dans le tableau ci-dessus), les lignes dont la valeur dept_id est égale à 20 sont placées dans un autre groupe (marqué en vert), et enfin les lignes dont la valeur dept_id est égale à 30 sont placées dans un autre groupe (marqué en rouge). La fonction SUM() calcule la somme des lignes dans chaque groupe.

Notez que lorsque vous utilisez SUM() OVER(PARTITION BY), vous conservez les détails des lignes individuelles. Vous pouvez, par exemple, voir les détails de l'employé nommé Ford : son poste, son salaire et sa comparaison avec le total des salaires de son département.

C'est l'utilisation la plus typique de SUM() OVER(PARTITION BY): vous calculez la valeur de la somme pour chaque groupe de données et vous conservez les détails des lignes individuelles. Voyons un exemple similaire.

Exemple 1B : Calcul du salaire pour chaque titre de poste

Nous pouvons utiliser SUM(salary) OVER(PARTITION BY job) pour obtenir le salaire total par poste. Voyons ce que cela donne :

SELECT 
  emp_id, 
  name, 
  job, 
  dept_id, 
  salary,
  SUM(salary) OVER(PARTITION BY job) AS job_total_salary
FROM employees;

Voici le résultat :

Cette fois, les lignes sont regroupées en fonction de la valeur du poste plutôt que de l'identifiant du service. Les employés ayant la même fonction sont regroupés et nous calculons le salaire total des personnes occupant cette fonction. La fonction SUM() est appliquée à tous les salaires de chaque groupe : le salaire total du groupe "Manager" est la somme de 2450, 2850 et 2975, qui sont les salaires des trois managers figurant dans notre tableau.

Exemple 2 : calculer le rapport entre la valeur d'une seule ligne et le total

Généralement, nous voulons voir la comparaison entre chaque ligne individuelle et la somme totale. Calculons le pourcentage du salaire de chaque personne par rapport au total des salaires de son service.

SELECT 
  emp_id, 
  name, 
  job, 
  dept_id, 
  salary,
  ROUND(100.0 * salary / SUM(salary) OVER(PARTITION BY dept_id), 2) 
           AS percentage_of_dept_total_salary
FROM employees;

Cette fois, les lignes sont regroupées en fonction de la valeur job plutôt que de l'ID du département. Les employés occupant le même poste sont regroupés et nous calculons le salaire total des personnes occupant ce poste. La fonction SUM() est appliquée à tous les salaires de chaque groupe : le salaire total du groupe "Manager" est la somme de 2450, 2850 et 2975, qui sont les salaires des trois managers figurant dans notre tableau.

Exemple 2 : calculer le rapport entre la valeur d'une seule ligne et le total

Généralement, nous voulons voir la comparaison entre chaque ligne individuelle et la somme totale. Calculons le pourcentage du salaire de chaque personne par rapport au total des salaires de son service.

SELECT 
  emp_id, 
  name, 
  job, 
  dept_id, 
  salary,
  ROUND(100.0 * salary / SUM(salary) OVER(PARTITION BY dept_id), 2) 
           AS percentage_of_dept_total_salary
FROM employees;

Nous constatons, par exemple, que l'analyste Scott a un salaire individuel de 3 000, ce qui représente 27,59 % du total des salaires de son service.

Voici une ventilation des fonctions utilisées pour accomplir cette tâche :

• Nous prenons chaque salaire individuel et le divisons par le salaire total du département :

salary / SUM(salary) OVER(PARTITION BY dept_id)

• Pour obtenir un pourcentage, nous le multiplions par 100 % :
  
  • 0 * salary / SUM(salary) OVER(PARTITION BY dept_id)
• Ensuite, nous utilisons la fonction ROUND() pour obtenir deux chiffres décimaux :

ROUND(100.0 * salary / SUM(salary) OVER(PARTITION BY dept_id), 2)

Le calcul du rapport entre la ligne individuelle et la somme pour un groupe est un autre cas d'utilisation très courant de SUM() avec OVER(PARTITION BY). De la même manière, vous pouvez calculer la différence entre le total du groupe et la ligne individuelle.

Quelle est la différence entre SUM() avec OVER(PARTITION BY …) et SUM() avec GROUP BY?

La clause PARTITION BY accomplit une tâche similaire à la clause GROUP BY. OVER(PARTITION BY) et GROUP BY divisent l'ensemble de données en partitions, ou groupes. Lors de l'utilisation de la fonction SUM(), les deux expressions calculent la somme pour chaque groupe. Alors, laquelle choisir ?

Lorsque vous utilisez la fonction GROUP BY, les détails des lignes individuelles sont réduits. Jetez un coup d'œil :

SELECT 
  job, 
  SUM(salary) AS total_salary
FROM employees
GROUP BY job;

Ici, nous obtenons la valeur salariale totale pour chaque titre de poste sans tenir compte des salaires individuels des employés.

Toutefois, si vous souhaitez comparer la valeur totale avec les valeurs des lignes individuelles (par exemple, pour calculer le rapport entre la valeur individuelle et la valeur totale), vous pouvez utiliser OVER(PARTITION BY …). Il renvoie toutes les lignes individuelles ainsi que la valeur totale pour chaque ligne. Cette valeur totale peut varier en fonction de la partition à laquelle la ligne appartient.

SELECT 
  name, 
  job, 
  salary, 
  SUM(salary) OVER(PARTITION BY job) AS total_salary
FROM employees;

La valeur de total_salary correspond aux résultats de la requête précédente avec GROUP BY. Mais ici, vous pouvez également voir les salaires individuels. Par exemple, il y a deux analystes qui gagnent 6000 au total ; chacun d'entre eux gagne 3000.

La règle générale est la suivante : si vous souhaitez connaître uniquement la valeur totale de chaque groupe et que vous n'êtes pas intéressé par les détails de chaque ligne, vous devez utiliser la clause GROUP BY. Si vous êtes intéressé à la fois par la somme de chaque groupe et par le détail des lignes individuelles, vous devez utiliser SUM() OVER(PARTITION BY).

Exemple 3 : Calcul d'un total courant à l'aide de SUM() avec OVER(PARTITION BY)

Une autre utilisation courante de la syntaxe SUM() OVER(...) consiste à calculer le total courant.

Un total courant est la somme cumulative des nombres précédents dans une colonne. Les totaux courants sont utilisés pour calculer des valeurs qui s'accumulent au fil du temps. Par exemple, avec un total courant, vous pouvez calculer l'utilisation mensuelle des données mobiles en ajoutant la valeur de chaque jour suivant à la somme des valeurs des jours précédents. De même, vous pouvez calculer comment le nombre d'utilisateurs enregistrés augmente chaque jour ou comment le revenu total augmente à chaque transaction.

Nous utilisons la syntaxe SUM() avec OVER(PARTITION BY … ORDER BY …) pour calculer le total courant.

Prenons un exemple. Nous utiliserons la table orders de l'échantillon de données fourni par Oracle Live SQL. Cette table stocke les données historiques des commandes. Chaque commande a sa date (order_date), son représentant (sales_rep_id) et sa valeur totale (order_total).

Nous calculons ici la valeur totale courante pour chaque représentant commercial :

SELECT 
  order_date, 
  sales_rep_id, 
  order_total,
  SUM(order_total) OVER(PARTITION BY sales_rep_id ORDER BY order_date) 
          AS running_total
FROM orders;

Pour ce faire, nous partitionnons notre table par représentant commercial, puis nous ordonnons chaque partition par date :

	SUM(order_total) OVER(PARTITION BY sales_rep_id ORDER BY order_date)

Le nouvel élément de syntaxe est ORDER BY. Il définit la manière dont les lignes sont triées dans chaque fenêtre. La fonction SUM() est appliquée à chaque ligne ; elle additionne la valeur actuelle de order_total à la valeur précédente de running_total; les totaux courants sont calculés séparément pour chaque partition (ici, pour chaque ID de représentant commercial).

Examinons de plus près le représentant commercial dont l'ID est 153. Sa première commande date du²⁹ mars et sa valeur totale est de 10794,6. À ce stade, le total courant (la somme) est égal à la valeur de la commande. Leur deuxième commande a été passée le¹⁶ août pour un montant de 78279,6 ; le total courant est maintenant égal à la somme des valeurs de la première et de la deuxième commande (10794,6 + 78279,6 = 89074,2). Après leur troisième commande, le total courant est égal au total courant précédent plus la valeur de la troisième commande (89074,2 + 129 = 89203,2). Ce processus est similaire pour le représentant commercial dont l'ID est 154.

Le calcul d'un total courant est un schéma courant lors de l'utilisation de SQL pour l'analyse de données. Vous pouvez lire comment calculer un total courant en SQL ailleurs dans notre blog.

Exemple 4 : Comptage d'objets dans des catégories personnalisées

La fonction SUM() est souvent associée à l'instruction CASE WHEN pour compter les objets dans des catégories personnalisées. Par exemple, vous pouvez vouloir calculer le salaire total des employés occupant des postes de direction dans un département et l'afficher à côté des détails de chaque employé. Vous pouvez y parvenir en utilisant SUM() OVER(PARTITION BY) en combinaison avec CASE WHEN.

Examinons tout d'abord l'instruction CASE WHEN. Elle est similaire à l'instruction if utilisée dans de nombreux langages de programmation. Nous l'utilisons pour définir la valeur d'une expression dans différentes situations, ou cas.

Dans l'exemple suivant, nous utilisons l'instruction CASE WHEN pour identifier chaque employé en tant que cadre (directeurs et présidents) ou en tant qu'employé ordinaire (tous les autres postes). Jetez un coup d'œil :

SELECT 
  emp_id, 
  name, 
  job, 
  dept_id, 
  CASE WHEN job IN (‘PRESIDENT’, ‘MANAGER’) 
    THEN ‘MANAGEMENT’ 
    ELSE ‘REGULAR’ END AS emp_type
  salary
FROM employees;

L'instruction CASE évalue toutes les conditions WHEN. Si elle trouve la condition correspondante, elle renvoie la valeur dans la branche THEN. Si elle ne trouve pas la condition correspondante, elle renvoie la valeur indiquée après ELSE. Dans notre exemple, les employés occupant le poste de président ou de directeur sont étiquetés comme 'Management'. Tous les autres postes se voient attribuer l'étiquette "Regular". Voici le résultat de la requête :

Pour en savoir plus sur CASE, lisez l'article Comment utiliser CASE en SQL.

Vous pouvez également utiliser l'instruction CASE avec SUM() pour additionner des valeurs dans des catégories personnalisées. Voici la requête :

SELECT
  dept_id,
  SUM(
    CASE WHEN job IN (‘PRESIDENT’, ‘MANAGER’) 
    THEN salary
    ELSE 0 END) 
    AS dept_management_salary,
  SUM(
    CASE WHEN job IN (‘PRESIDENT’, ‘MANAGER’) 
    THEN 0
    ELSE salary END) 
    AS dept_regular_salary
FROM employees
GROUP BY dept_id;

Pour les employés occupant des postes de direction, la première instruction CASE renvoie la valeur de la colonne salary. La fonction SUM() combinée à GROUP BY additionne ensuite tous les salaires des cadres avec la même valeur dept_id. De cette manière, nous calculons le salaire total des cadres par département. La deuxième instruction CASE calcule le salaire total par département pour tous les employés réguliers.

Vous pouvez calculer le salaire total des employés occupant un poste de direction dans un département et l'afficher à côté des détails de l'employé en utilisant SUM() avec PARTITION BY:

SELECT 
  emp_id, 
  name, 
  job, 
  dept_id, 
  CASE WHEN job IN (‘PRESIDENT’, ‘MANAGER’) 
    THEN ‘MANAGEMENT’ 
    ELSE ‘REGULAR’ END AS emp_type
  salary,
  SUM(
    CASE WHEN job IN (‘PRESIDENT’, ‘MANAGER’) 
    THEN salary 
    ELSE 0 END) OVER(PARTITION BY dept_id) AS dept_management_salary
FROM employees;

Les lignes sont divisées en groupes en fonction de la colonne dept_id. Il y a trois groupes, un pour chaque département. La fonction SUM() est appliquée à l'expression CASE WHEN. Au lieu de calculer le salaire total dans chaque département, nous calculons le salaire total des employés occupant des postes de direction dans le département. La fonction CASE renvoie 0 pour les employés ordinaires (la somme n'est pas augmentée) et la valeur du salaire pour les cadres. Vous pouvez utiliser ce modèle dans de nombreuses situations différentes. En fait, dans la section suivante, nous verrons un autre exemple de combinaison de CASE WHEN avec SUM() et OVER(PARTITION BY).

Exemple 5 : Calcul du total d'une commande avec des remises en utilisant SUM() avec CASE WHEN

Dans cet exemple, nous voulons calculer la valeur totale de chaque commande, y compris les remises pour certains articles de la commande. Nous utiliserons la syntaxe SUM() OVER(PARTITION BY …) pour additionner toutes les valeurs des articles par commande. Et pour intégrer les remises sur les produits, nous utiliserons l'instruction CASE WHEN.

Nous utilisons la table order_items de l'échantillon de données fourni par Oracle Live SQL. Cette table stocke tous les articles de la commande (product_id) appartenant à chaque commande (order_id). Elle contient des informations sur le prix unitaire du produit (unit_price) et la quantité commandée (quantity).

Nous voulons maintenant calculer les valeurs de la commande : une pour chaque produit de la commande et un total pour la commande ; le total de la commande doit inclure toutes les réductions appliquées aux produits.

Le total de la commande est égal au prix unitaire multiplié par la quantité commandée. Toutefois, lorsqu'une remise est appliquée à un produit, nous utiliserons un relevé CASE WHEN pour en rendre compte.

SELECT 
  order_id, 
  product_id, 
  unit_price, 
  quantity,   

  SUM(CASE 
     -- 20% discount for this product
     WHEN product_id = 3143 THEN unit_price*quantity*0.8
     -- 40% discount for this product
     WHEN product_id = 3106 THEN unit_price*quantity*0.6
     ELSE unit_price*quantity END)
  OVER(PARTITION BY order_id, product_id) 
        AS order_product_total_with_discount,

  SUM(CASE 
     -- 20% discount for this product
     WHEN product_id = 3143 THEN unit_price*quantity*0.8
     -- 40% discount for this product
     WHEN product_id = 3106 THEN unit_price*quantity*0.6
     ELSE unit_price*quantity END)
  OVER(PARTITION BY order_id) 
        AS order_total_with_discount    

FROM order_items;

Pour calculer le total par produit dans une commande, nous utilisons la syntaxe suivante :

SUM(CASE 
       WHEN product_id=3143 THEN unit_price*quantity*0.8 -- 20% discount 
       WHEN product_id=3106 THEN unit_price*quantity*0.6 -- 40% discount 
       ELSE unit_price*quantity END)
OVER(PARTITION BY order_id, product_id) AS order_product_total_with_discount

Nous transmettons l'instruction CASE WHEN comme argument à la fonction SUM(); si l'ID de produit de la ligne actuelle est 3143, nous appliquons une remise de 20 % ; pour l'ID de produit 3106, la remise est de 40 %. Ensuite, nous partitionnons l'ensemble de données par ID de commande et ID de produit afin d'obtenir les valeurs totales pour chaque produit d'une commande.

Notez que nous utilisons deux clauses PARTITION BY différentes dans la requête. Pour calculer la valeur totale du produit, nous partitionnons l'ensemble de données par ID de commande et ID de produit. Pour calculer la valeur totale par commande, nous partitionnons l'ensemble de données en fonction de l'identifiant de la commande uniquement. La valeur totale de la commande est égale pour toutes les lignes ayant le même ID de commande. En d'autres termes, si vous additionnez toutes les valeurs totales des produits d'une certaine commande, vous obtenez la valeur totale de la commande. Par exemple, pour l'ID de commande 2355, nous obtenons ce qui suit : 9200 + 10545 + 16337.2 + 3572 + 3230 + 211.2 + 216.7 + 4975 + 46226.4 = 94513.5.

Voici quelques exemples de la fonction SUM() avec CASE WHEN.

Aller de l'avant et utiliser la fonction SUM() avec OVER() et PARTITION BY

Maintenant que vous savez comment utiliser la fonction de fenêtre SUM() et ses différentes options syntaxiques, vous pouvez l'essayer vous-même.

Nous avons présenté des exemples sur la façon de calculer un pourcentage de la valeur d'une seule ligne par rapport au total de la partition et nous avons montré comment calculer le total courant et le total de la commande personnalisée. Nous vous encourageons à vous exercer avec vos propres données.

Pour en savoir plus, essayez notre cours interactif Fonctions de fenêtrage qui explique en détail tous les concepts des fonctions de fenêtre. Vous pouvez également consulter notre aide-mémoire Fonctions de fenêtrage si vous souhaitez une référence rapide et facile pour les fonctions de fenêtre SQL.

Bonne chance !