Types de Fonctions de Fenêtre

Explorons brièvement les principaux types de fonctions de fenêtre utilisées dans SQL.

Fonctions d'agrégation

Ce sont les fonctions d'agrégation standard (AVG, SUM, MAX, MIN, COUNT) utilisées dans un contexte de fenêtre. Nous avons déjà utilisé ce type de fonction de fenêtre dans le chapitre précédent.

Fonctions de classement

Les fonctions de classement dans SQL sont un type de fonction de fenêtre qui vous permet d'attribuer un rang à chaque ligne au sein d'une partition d'un ensemble de résultats. Ces fonctions peuvent être extrêmement utiles pour effectuer des calculs et des analyses ordonnés.

RANK(): Attribue un rang unique à chaque ligne distincte au sein de la partition en fonction de la clause ORDER BY. Les lignes avec des valeurs égales reçoivent le même rang, avec des écarts laissés dans le classement;
DENSE_RANK(): Semblable à RANK(), mais sans écarts dans la séquence de classement;
NTILE(n): Divise les lignes dans une partition ordonnée en n groupes et attribue un numéro de groupe à chaque ligne.

Exemple

Nous allons classer les ventes en fonction du Amount pour chaque ProductID dans l'ordre croissant en utilisant 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;

Le tableau de résultats contient toutes les informations de la table principale et une colonne supplémentaire qui fournit le rang de chaque vente pour le produit particulier.

Fonctions de comparaison de valeurs

Les fonctions de fenêtre de comparaison de valeurs en SQL sont utilisées pour comparer les valeurs de la ligne actuelle avec les valeurs d'autres lignes au sein de la même partition.
Ces fonctions sont particulièrement utiles pour les tâches qui impliquent l'analyse des tendances, l'exécution de calculs basés sur les lignes adjacentes ou l'accès à des valeurs de lignes spécifiques dans une fenêtre définie. Il existe plusieurs fonctions de comparaison de valeurs en SQL :

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

Exemple

Utilisons la fonction de fenêtre de comparaison de valeurs LAG() pour calculer le changement de 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;

En conséquence, nous pourrions simplement extraire des informations sur les différences de ventes pour chaque produit particulier sans utiliser de sous-requêtes ou de procédures stockées. Nous pouvons également calculer les différences pour toutes les ventes sans partitionner 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 voulons pas obtenir les valeurs précédentes uniquement pour un produit particulier, mais pour toutes les ventes dans la table.

Tout était clair ?

Merci pour vos commentaires !

Section 3. Chapitre 4