Notice: This page requires JavaScript to function properly.
Please enable JavaScript in your browser settings or update your browser.Apprendre Types de Fonctions de Fenêtre | Quelques Sujets Supplémentaires
Optimisation SQL et Fonctionnalités de Requête

bookTypes de Fonctions de Fenêtre

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

Fonctions d’agrégation

Il s'agit des 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 en SQL sont un type de fonction de fenêtre 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 et des analyses ordonnées.

  • RANK() : attribue un rang unique à chaque ligne distincte dans la partition selon la clause ORDER BY. Les lignes ayant des valeurs identiques reçoivent le même rang, laissant des écarts dans le classement ;

  • DENSE_RANK() : similaire à RANK(), mais sans écarts 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

Nous allons classer les ventes selon le Amount pour chaque ProductID par 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;
copy

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

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 courante avec les valeurs d’autres lignes au sein de la même partition.
Ces fonctions sont particulièrement utiles pour analyser des tendances, effectuer des calculs basés sur des lignes adjacentes ou accéder à des valeurs 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 l’ensemble de résultats sans avoir besoin d’une auto-jointure ;
  • LEAD() : récupère la valeur d’une ligne suivante dans l’ensemble de résultats 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

Utilisons 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;
copy

Ainsi, il est possible d’extraire simplement des informations sur les différences de ventes pour chaque produit spécifique sans utiliser de sous-requêtes ni de procédures stockées.
Il est également possible de calculer les différences pour l’ensemble des ventes sans partitionnement à l’aide de 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;
copy

Vous pouvez constater 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.

question mark

Que fait la fonction NTILE() en SQL ?

Select the correct answer

Tout était clair ?

Comment pouvons-nous l'améliorer ?

Merci pour vos commentaires !

Section 3. Chapitre 3

Demandez à l'IA

expand

Demandez à l'IA

ChatGPT

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

Suggested prompts:

Can you explain the difference between RANK() and DENSE_RANK() in more detail?

What are some practical use cases for value comparison window functions like LAG() and LEAD()?

Could you provide more examples of window functions in SQL?

Awesome!

Completion rate improved to 4.55

bookTypes de Fonctions de Fenêtre

Glissez pour afficher le menu

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

Fonctions d’agrégation

Il s'agit des 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 en SQL sont un type de fonction de fenêtre 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 et des analyses ordonnées.

  • RANK() : attribue un rang unique à chaque ligne distincte dans la partition selon la clause ORDER BY. Les lignes ayant des valeurs identiques reçoivent le même rang, laissant des écarts dans le classement ;

  • DENSE_RANK() : similaire à RANK(), mais sans écarts 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

Nous allons classer les ventes selon le Amount pour chaque ProductID par 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;
copy

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

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 courante avec les valeurs d’autres lignes au sein de la même partition.
Ces fonctions sont particulièrement utiles pour analyser des tendances, effectuer des calculs basés sur des lignes adjacentes ou accéder à des valeurs 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 l’ensemble de résultats sans avoir besoin d’une auto-jointure ;
  • LEAD() : récupère la valeur d’une ligne suivante dans l’ensemble de résultats 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

Utilisons 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;
copy

Ainsi, il est possible d’extraire simplement des informations sur les différences de ventes pour chaque produit spécifique sans utiliser de sous-requêtes ni de procédures stockées.
Il est également possible de calculer les différences pour l’ensemble des ventes sans partitionnement à l’aide de 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;
copy

Vous pouvez constater 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.

question mark

Que fait la fonction NTILE() en SQL ?

Select the correct answer

Tout était clair ?

Comment pouvons-nous l'améliorer ?

Merci pour vos commentaires !

Section 3. Chapitre 3
some-alt