Comment combiner deux fonctions agrégées en SQL

Vous avez des difficultés à utiliser deux fonctions d'agrégation dans une requête ? Cet article vous montrera comment le faire de la bonne façon - en fait, des deux bonnes façons.

Dans l'analyse des données et la création de rapports, nous avons souvent besoin de compter le nombre d'enregistrements ou de les additionner, puis de calculer la moyenne de ce compte ou de cette somme.

Traduit en logique SQL, il s'agit de l'agrégation de données agrégées, ou agrégation multi-niveaux. Pour l'agrégation, il existe les fonctions d'agrégation SQL. Et pour l'agrégation multi-niveaux, vous utilisez (au moins) deux fonctions d'agrégation en même temps.

Si vous souhaitez créer des rapports de qualité, vous aurez besoin de bien plus que les fonctions d'agrégation de SQL. Cependant, elles constituent certainement la base d'un bon reporting. La meilleure façon d'acquérir cette base est de suivre notre cours interactif. Création de rapports basiques en SQL interactif. Il comporte une section entière consacrée uniquement au calcul d'agrégations multi-niveaux en SQL et à la synthèse de données à l'aide de fonctions d'agrégation. Il couvre également la classification des données à l'aide de CASE WHEN et GROUP BY, le calcul de plusieurs métriques dans un rapport et la comparaison de groupes au sein d'un même rapport. Avec 97 exercices interactifs, ce cours vous aidera à maîtriser la création de rapports en SQL !

Pour vous faciliter encore plus la tâche, je vais vous montrer ici comment combiner deux fonctions d'agrégation en SQL. Si vos connaissances des fonctions d'agrégation sont rouillées, ce guide des fonctions d'agrégation SQL peut vous aider à suivre cet article. Vous pouvez également vous entraîner sur ces exemples de fonctions agrégées.

Exemple de données

Nous avons une table appelée new_users. Elle recueille des données sur les nouveaux utilisateurs d'une application sur le marché sud-américain. Les colonnes sont les suivantes :

• id - L'ID de chaque enregistrement individuel.
• date - La date à laquelle les utilisateurs se sont inscrits.
• number_of_new_users - Le nombre de nouveaux utilisateurs par date.
• city - La ville des utilisateurs.
• country - Le pays de localisation des utilisateurs.

Vous voyez ci-dessous qu'il y a deux dates au total, chaque date ayant un nombre différent d'utilisateurs de deux pays et de deux villes dans chaque pays.

Comment utiliseriez-vous les fonctions AVG() et SUM() pour calculer le nombre quotidien moyen de nouveaux utilisateurs par pays ?

La solution naïve

La logique qui sous-tend l'approche naïve est, en fait, assez solide. Il est dommage que cette solution naïve ne soit pas une solution du tout. Vous allez bientôt comprendre pourquoi.

Si vous ne pensez qu'à l'approche mathématique, c'est simple : vous devez d'abord additionner le nombre de nouveaux utilisateurs par pays chaque jour, puis calculer la moyenne de cette somme.

Transféré dans les fonctions SQL, il semble logique d'écrire quelque chose comme ça :

SELECT country,
 	 AVG(SUM(number_of_new_users)) AS average_new_daily_users
FROM new_users
GROUP BY country;

Pourquoi pas, n'est-ce pas ? Il y a une raison très simple à cela : SQL ne permet pas l'imbrication des fonctions d'agrégation. En d'autres termes, vous ne pouvez pas utiliser une fonction d'agrégation dans une autre fonction d'agrégation. Eh bien, vous pouvez, mais la requête renverra une erreur disant exactement ce que je viens de dire :

Comment corriger cette requête pour combiner deux fonctions agrégées en SQL ?

Vous avez deux possibilités : utiliser une sous-requête ou utiliser des expressions de table communes (CTE).

Solution réelle 1 : Sous-requête

La première option consiste à combiner deux fonctions d'agrégation à l'aide d'une sous-requête. La sous-requête est une requête à l'intérieur de la requête principale. Lors de la création de rapports, on les trouve généralement dans les clauses SELECT, FROM ou WHERE.

Dans cet exemple, je vais placer la sous-requête dans la clause FROM.

SELECT country,
	 AVG(ds.sum_new_users) AS average_daily_new_users
FROM (SELECT date,
	  	 country,
	 	 SUM(number_of_new_users) AS sum_new_users
	  FROM new_users
	  GROUP BY date, country) AS ds
GROUP BY country;

Le principe de la combinaison de deux fonctions d'agrégation est d'utiliser la sous-requête pour calculer la statistique "interne". Ensuite, le résultat est utilisé dans les fonctions d'agrégation de la requête externe.

Le code ci-dessus sélectionne la date et le pays et calcule la somme de la colonne number_of_new_users. Cela renvoie le nombre total quotidien de nouveaux utilisateurs. J'ai donné à la sous-requête l'alias ds, qui est l'abréviation de "daily sum".

Une fois que j'ai obtenu la somme quotidienne, je l'ai référencée dans la requête externe en calculant la moyenne de la colonne ds.sum_new_users - c'est-à-dire la moyenne des nouveaux utilisateurs quotidiens. Je veux que cette moyenne soit par pays ; c'est pourquoi j'ai regroupé les données par la colonne pays.

Solution réelle 2 : CTE

L'autre option pour combiner les fonctions d'agrégation en SQL consiste à utiliser un CTE au lieu d'une sous-requête. Un CTE est une version plus ordonnée et "plus proche de la logique mathématique" d'une sous-requête. Il s'agit d'une expression qui vous permet de créer un résultat temporaire, que vous pouvez référencer dans une autre instruction SELECT. Vous pouvez utiliser le résultat d'un CTE comme vous le feriez pour n'importe quelle autre table. La différence est que le résultat d'un CTE n'existe que lorsqu'un CTE est exécuté en même temps que la requête utilisant le CTE. Vous trouverez une explication plus détaillée des CTE dans l'article Qu'est-ce qu'un CTE ?

La requête ci-dessous vous donnera le même résultat que la solution de sous-requête :

WITH ds AS (
  SELECT date, 
   country,
         SUM(number_of_new_users) AS sum_new_users
  FROM new_users
  GROUP BY date, country)

SELECT country,
       AVG(ds.sum_new_users) AS average_daily_new_users
FROM ds
GROUP BY country;

Chaque CTE est introduit à l'aide du mot-clé WITH. Il en va de même pour mon CTE nommé ds. Après le mot-clé AS vient la définition du CTE. Dans ce cas, il s'agit de la même déclaration SELECT que dans la sous-requête de l'exemple précédent. Voici le sum(): il renverra, là encore, la somme des nouveaux utilisateurs par pays et par date.

La deuxième instruction SELECT fait référence au CTE et calcule la moyenne de la somme retournée par le CTE.

Contrairement à l'imbrication de la sous-requête, l'utilisation du CTE vous permet d'utiliser les fonctions d'agrégation dans l'ordre logique : d'abord SUM(), puis AVG(). La fonction "interne" est utilisée dans l'ETC, tandis que la deuxième instruction SELECT est destinée à la fonction d'agrégation "externe".

Le résultat sera le même qu'avec la sous-requête ; je n'ai pas besoin de le montrer à nouveau. Cependant, il est toujours bon d'en savoir plus sur les différences entre une sous-requête et un CTE, puis de s'entraîner à utiliser les CTE sur des exemples concrets.

Devenez un maître de l'agrégation de données et du reporting

Le problème de l'utilisation de deux fonctions d'agrégation en SQL est omniprésent. Plus le reporting devient complexe, plus vous utiliserez les agrégations multi-niveaux. Vous savez maintenant que les sous-requêtes et les CTE sont les deux solutions pour cela.

Toutes les autres nuances du reporting peuvent être trouvées et pratiquées dans notre cours. Création de rapports basiques en SQL cours. Les CTE peuvent rendre vos rapports plus faciles et plus sophistiqués. Requêtes récursives cours est donc une bonne idée.