Fonctions de fenêtre SQL et fonctions d'agrégation SQL : Similitudes et différences

Si vous n'êtes pas familier avec les fonctions de fenêtre SQL, vous vous demandez peut-être en quoi elles diffèrent des fonctions d'agrégation. Quand devez-vous utiliser les fonctions de fenêtre ? Dans cet article, nous allons passer en revue les fonctions de fenêtre et les fonctions d'agrégation, examiner leurs similitudes et leurs différences, et voir laquelle choisir en fonction de ce que vous devez faire.

Après avoir abordé le langage SQL de base, vous aurez probablement envie d'utiliser certaines de ses fonctions les plus avancées. C'est une bonne chose, car ces fonctions facilitent la création de rapports et l'analyse.

Mais très vite, vous rencontrerez deux groupes de fonctions mystérieuses : les fonctions de fenêtre et les fonctions d'agrégation. Que font-elles ? En quoi sont-elles différentes ?

C'est ce que vous allez découvrir.

Que sont les fonctions agrégées SQL ?

Lesfonctions agrégées opèrent sur un ensemble de valeurs pour renvoyer une seule valeur scalaire. Voici les fonctions agrégées SQL :

• AVG() renvoie la moyenne des valeurs spécifiées.
• SUM() calcule la somme de toutes les valeurs de l'ensemble.
• MAX() et MIN() renvoient respectivement la valeur maximale et minimale.
• COUNT() renvoie le nombre total de valeurs dans le jeu.

En utilisant la clause GROUP BY, vous pouvez calculer une valeur agrégée pour plusieurs groupes en une seule requête.

Par exemple, supposons que nous ayons des données de transaction provenant de deux villes, San Francisco et New York :

Vous pouvez utiliser les fonctions d'agrégation SQL pour calculer le montant moyen des transactions quotidiennes pour chaque ville. Vous devrez regrouper les données à la fois par date et par ville :

SELECT date, city, AVG(amount) AS avg_transaction_amount_for_city
FROM transactions
GROUP BY date, city;

Voici le résultat de cette requête :

En utilisant la fonction d'agrégation AVG() et GROUP BY, nous obtenons des résultats qui sont regroupés par date et par ville. Nous avons eu deux transactions à New York le 2 novembre et deux transactions à San Francisco le 3 novembre, mais le jeu de résultats n'inclut pas ces transactions individuelles ; les fonctions d'agrégation réduisent les lignes individuelles et présentent la valeur agrégée (ici, la moyenne) pour toutes les lignes du groupe.

Qu'est-ce que SQL Fonctions de fenêtrage?

En SQL, les fonctions de fenêtre opèrent sur un ensemble de lignes appelé cadre de fenêtre. Elles renvoient une valeur unique pour chaque ligne de la requête sous-jacente.

Le cadre de la fenêtre (ou simplement la fenêtre) est défini à l'aide de la clause OVER(). Cette clause permet également de définir une fenêtre basée sur une colonne spécifique (similaire à GROUP BY).

Pour calculer les valeurs retournées, les fonctions de fenêtre peuvent utiliser des fonctions d'agrégation, mais elles les utiliseront avec la clause OVER().

Revenons à nos données pour San Francisco et New York. Voici à nouveau le tableau :

Nous voulons ajouter une autre colonne à ce tableau avec la valeur moyenne quotidienne des transactions pour chaque ville. La requête SQL suivante utilise une fonction de fenêtre pour obtenir le résultat dont nous avons besoin :

SELECT id, date, city, amount,
       AVG(amount) OVER (PARTITION BY date, city) AS  avg_daily_transaction_amount_for_city
FROM transactions
ORDER BY id;

Voici le résultat :

Notez que les lignes ne sont pas réduites; nous avons toujours une ligne pour chacune de nos transactions. Toutes les moyennes calculées sont présentées dans la colonne avg_daily_transaction_amount_for_city.

Vous pouvez en savoir plus sur les fonctions de fenêtre dans ce guide approfondi. Il fournit plusieurs exemples, y compris des applications simples et plus avancées. Par ailleurs, l'équipe LearnSQL.fr a préparé un excellent aide-mémoire SQL Fonctions de fenêtrage . Imprimez-la et collez-la sur votre bureau, surtout si vous êtes novice en matière de fonctions de fenêtre.

Similitudes et différences entre les fonctions de fenêtre et les fonctions d'agrégation

Maintenant que nous avons vu les deux types de fonctions, nous pouvons résumer les similitudes et les différences entre elles.

Les fonctions de fenêtre et les fonctions d'agrégation :

• Opèrent sur un ensemble de valeurs (lignes).
• Elles peuvent calculer des montants agrégés (par exemple, AVG(), SUM(), MAX(), MIN() ou COUNT()) sur l'ensemble.
• Peut regrouper ou partitionner les données sur une ou plusieurs colonnes.

Lesfonctions d'agrégat avec GROUP BY diffèrent des fonctions de fenêtre en ce qu'elles :

• Utilisent GROUP BY() pour définir un ensemble de lignes pour l'agrégation.
• Regroupent les lignes en fonction des valeurs des colonnes.
• Réduisent les lignes en fonction des groupes définis.

Lesfonctions de fenêtre diffèrent des fonctions d'agrégation utilisées avec GROUP BY en ce qu'elles :

• Utilisent OVER() au lieu de GROUP BY() pour définir un ensemble de lignes.
• Elles peuvent utiliser de nombreuses fonctions autres que des agrégats (par exemple, RANK(), LAG() ou LEAD()).
• Regroupent les lignes en fonction du rang, du centile, etc. de la ligne, ainsi que de la valeur de sa colonne.
• Ne pas réduire les lignes.
• Peut utiliser un cadre de fenêtre glissante (qui dépend de la ligne actuelle).

Démontrons cette dernière différence à l'aide d'un autre exemple. Dans cet exercice, nous voulons calculer la moyenne des ventes pour les jours précédents et actuels pour chaque date (c'est-à-dire une moyenne mobile sur 2 jours).

Je suggère de commencer par une expression de table commune (CTE) pour définir le tableau. daily_sales table, où nous avons le total des ventes pour chaque jour. Ensuite, nous utilisons une fonction de fenêtre avec un cadre de fenêtre glissante pour calculer la moyenne des ventes totales pour les jours actuels et précédents. La requête est la suivante :

WITH daily_sales AS (
    SELECT date, SUM(amount) AS sales_per_day
    FROM transactions
    GROUP BY date)
SELECT date, 
   AVG(sales_per_day) OVER (ORDER BY date ROWS 1 PRECEDING) 
AS avg_2days_sales
FROM daily_sales
ORDER BY date; 

Voici l'ensemble des résultats :

Dans la première ligne, le tableau montre les ventes totales pour le 1er novembre car il n'y a pas de ligne précédente pour cette date. Ensuite, dans la deuxième ligne, nous avons les ventes moyennes pour les 1er et 2 novembre ; dans la troisième ligne, le tableau inclut les ventes moyennes pour les 2 et 3 novembre, et ainsi de suite.

Les fonctions de fenêtre sont idéales pour calculer des moyennes mobiles, ce que vous ne pouvez pas faire en utilisant uniquement les fonctions d'agrégation et GROUP BY().

Entraînons-nous à Fonctions de fenêtrage!

Le tableau suivant résume toutes les similitudes et les différences entre les fonctions d'agrégation et les fonctions de fenêtre de SQL :

Même si les fonctions de fenêtre SQL sont un sujet avancé, vous pouvez les pratiquer par vous-même. Ce guide spécial sur la pratique des fonctions de fenêtre SQL donne quelques conseils utiles pour apprendre la syntaxe des fonctions de fenêtre et écrire les requêtes correspondantes.

LearnSQL a préparé un cours complet sur Fonctions de fenêtrage; dans ce cours, vous pouvez vous entraîner à créer des cadres de fenêtre sophistiqués grâce à 218 exercices interactifs. Vous apprendrez à exploiter les fonctions de fenêtre pour calculer les totaux courants et les moyennes mobiles, établir des classements, trouver les meilleurs et les moins bons résultats et étudier les tendances dans le temps. Vous pouvez en savoir plus sur ce cours en lisant notre entretien avec Agnieszka Kozubek-Krycuń, Chief Content Officer, sur LearnSQL.fr.

Merci de votre lecture et bon apprentissage !