Apprendre Types de Fonctions de Fenêtre | Quelques Sujets Supplémentaires

Glissez pour afficher le menu

Exploration des principaux types de fonctions de fenêtre utilisées en SQL.

Fonctions d'agrégation

Fonctions d'agrégation standard (AVG, SUM, MAX, MIN, COUNT) utilisées dans un contexte de fenêtre. Ce type de fonction de fenêtre a déjà été utilisé dans le chapitre précédent.

Fonctions de classement

Fonctions de classement en SQL permettant d'attribuer un rang à chaque ligne au sein d'une partition d'un ensemble de résultats. Ces fonctions sont particulièrement utiles pour effectuer des calculs ordonnés et des analyses.

RANK() : attribue un rang unique à chaque ligne distincte dans la partition selon la clause ORDER BY. Les lignes ayant des valeurs égales reçoivent le même rang, avec des sauts dans la séquence de classement ;
DENSE_RANK() : similaire à RANK(), mais sans sauts dans la séquence de classement ;
NTILE(n) : divise les lignes d'une partition ordonnée en n groupes et attribue un numéro de groupe à chaque ligne.

Exemple

Classement des ventes selon le Amount pour chaque ProductID par ordre croissant à l'aide de la fonction DENSE_RANK() :


              12345678
            
SELECT 
    sales_id,
    product_id,
    sales_date,
    amount,
    DENSE_RANK() OVER (PARTITION BY product_id ORDER BY amount DESC) AS dense_rank_amount
FROM 
    Sales;

La table de résultats contient toutes les informations de la table principale ainsi qu'une colonne supplémentaire indiquant le rang de chaque vente pour le produit concerné.

Fonctions de comparaison de valeurs

Fonctions de comparaison de valeurs en SQL permettant de comparer les valeurs de la ligne courante avec celles d'autres lignes au sein de la même partition.
Ces fonctions sont particulièrement utiles pour l'analyse de tendances, les calculs basés sur les lignes adjacentes ou l'accès à des valeurs spécifiques dans une fenêtre définie. Plusieurs fonctions de comparaison de valeurs existent en SQL :

LAG() : récupère la valeur d'une ligne précédente dans le résultat sans avoir besoin d'une auto-jointure ;
LEAD() : récupère la valeur d'une ligne suivante dans le résultat sans avoir besoin d'une auto-jointure ;
FIRST_VALUE() : retourne la valeur de la première ligne dans la fenêtre ;
LAST_VALUE() : retourne la valeur de la dernière ligne dans la fenêtre.

Exemple

Utilisation de la fonction de fenêtre de comparaison de valeurs LAG() pour calculer la variation du montant des ventes par rapport à la vente précédente pour chaque produit :


              1234567891011
            
SELECT
    sales_id,
    product_id,
    sales_date,
    amount,
    LAG(amount, 1) OVER (PARTITION BY product_id ORDER BY sales_date) AS previous_amount,
    amount - LAG(amount, 1) OVER (PARTITION BY product_id ORDER BY sales_date) AS amount_change
FROM
    Sales
ORDER BY
    product_id, sales_date;

Description de la requête

Principales parties de la requête :

Clause SELECT : Spécifie les colonnes à extraire de la table.
- Noms de colonnes : Inclut des colonnes telles que SalesID, ProductID, SalesDate et Amount pour afficher les données pertinentes de chaque vente.
- Fonction de fenêtre : Utilise la fonction LAG() pour récupérer la valeur de la ligne précédente pour une colonne spécifique au sein d'une partition. Une colonne calculée supplémentaire affiche la différence entre les valeurs actuelle et précédente.

Syntaxe de la fonction de fenêtre :

LAG(column_name, offset, default_value) OVER (PARTITION BY partition_column ORDER BY order_column)
- column_name : La colonne à partir de laquelle récupérer la valeur précédente.
- offset : Le nombre de lignes en arrière à partir de la ligne courante pour récupérer la valeur (par défaut 1).
- default_value : La valeur à retourner si l'offset dépasse les limites de la partition (optionnel).
- PARTITION BY partition_column : Divise le jeu de résultats en partitions auxquelles la fonction de fenêtre est appliquée, garantissant que la fonction opère séparément dans chaque partition.
- ORDER BY order_column : Spécifie l'ordre des lignes dans chaque partition, assurant que la fonction de fenêtre traite les lignes dans une séquence significative.

Ainsi, il est possible d'extraire facilement des informations sur les différences de ventes pour chaque produit sans utiliser de sous-requêtes ou de procédures stockées.
Il est également possible de calculer les différences pour l'ensemble des ventes sans partitionnement en utilisant la requête suivante :


              123456789
            
SELECT
    sales_id,
    product_id,
    sales_date,
    amount,
    LAG(amount, 1) OVER (ORDER BY sales_date) AS previous_amount,
    amount - LAG(amount, 1) OVER (ORDER BY sales_date) AS amount_change
FROM
    Sales;

Vous pouvez voir que nous n'avons pas inclus la clause PARTITION BY dans le bloc OVER. Cela signifie que nous ne souhaitons pas obtenir les valeurs précédentes uniquement pour un produit particulier, mais pour toutes les ventes de la table.

Tout était clair ?

Merci pour vos commentaires !

Section 3. Chapitre 3

Demandez à l'IA

Posez n'importe quelle question ou essayez l'une des questions suggérées pour commencer notre discussion

Section 3. Chapitre 3