La clause ORDER BY dans les fonctions SQL de fenêtre

Dans cet article, vous aurez une vue d'ensemble de ORDER BY dans les fonctions de fenêtre. Vous apprendrez comment il se compare à PARTITION BY et à ORDER BY, le tout accompagné d'exemples et d'explications.

Si vous souhaitez créer des rapports qui vont au-delà des simples agrégations, vous aurez besoin des fonctions de fenêtre SQL. Ces fonctions vous aident à créer des classements, à calculer des totaux courants et des moyennes mobiles, et à trouver la différence entre les lignes. Pour utiliser efficacement les fonctions window, vous devez comprendre le rôle de la clause ORDER BY. Non seulement elle modifie le comportement des fonctions window, mais certaines d'entre elles ne s'exécutent même pas sans ORDER BY.

Cet article s'adresse aux personnes qui ont déjà une connaissance générale des fonctions de fenêtre. Si vous ne les connaissez pas, je vous recommande vivement de suivre notre cours de Fonctions de fenêtrage cours. Il vous permettra d'acquérir une connaissance complète des fonctions de fenêtre, y compris les fonctions de classement, les fonctions d'analyse et les clauses ORDER BY et PARTITION BY. Le cours comprend 218 défis de codage à résoudre ; vous pouvez en trouver d'autres dans l'ensemble de pratiquesFonctions de fenêtrage .

Même si vous connaissez les fonctions de fenêtre SQL, vous voudrez peut-être garder notre feuille d'aideFonctions de fenêtrage à portée de main pour vous y référer rapidement.

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

Les fonctions de fenêtre en SQL effectuent des opérations sur un cadre de fenêtre, qui se compose de la ligne actuelle et des lignes qui lui sont liées. Contrairement aux fonctions d'agrégation, les fonctions de fenêtre ne réduisent pas les lignes individuelles ; au lieu de cela, elles ajoutent une colonne à chaque ligne avec le résultat de la fonction. Cela signifie que les fonctions de fenêtre vous permettent d'afficher simultanément des données individuelles et agrégées.

Syntaxe

La syntaxe des fonctions SQL à fenêtre est présentée ci-dessous :

window_function OVER ([PARTITION BY column_name] [ORDER BY column_name ASC|DESC])

Voici une brève description de chaque partie de la syntaxe :

• window_function: La fonction de fenêtre que vous voulez utiliser.
• OVER(): Une clause obligatoire pour créer une fonction fenêtre.
• PARTITION BY: Une clause optionnelle qui partitionne (divise) les données.
• ORDER BY: Une clause facultative qui trie les données dans le cadre de la fenêtre.

Qu'est-ce que la clause ORDER BY dans Fonctions de fenêtrage?

ORDER BY La clause ORDER BY (ainsi que PARTITION BY) est un élément fondamental de nombreuses fonctions de fenêtre. ORDER BY dans la fonction de fenêtre trie les lignes dans le cadre de la fenêtre. Elle définit l'ordre dans lequel le calcul de la fonction de fenêtre sera effectué.

ORDER BY permet de trier les données d'une fenêtre de manière ascendante (A à Z, 1 à 10) ou descendante (Z à A, 10 à 1). Vous pouvez trier les données textuelles par ordre alphabétique ou alphabétique inversé, les données numériques de la plus basse à la plus haute (ou vice versa) et les données de date/heure de la plus ancienne à la plus récente (ou de la plus récente à la plus ancienne).

Exemple : ORDER BY avec une fonction de fenêtre de classement

Voyons un exemple de l'influence de ORDER BY dans les fonctions de fenêtre sur l'exécution de la requête.

La requête ci-dessous classe les données de la table product_sales par ordre décroissant des ventes, c'est-à-dire de la plus élevée à la plus faible.

SELECT 
  id,
  date,
  sales,
  product_name,
  DENSE_RANK() OVER(ORDER BY sales DESC) AS ranking
FROM product_sales; 

DENSE_RANK() ORDER BY est l'une des fonctions de fenêtre de classement. Elle classe l'ensemble des données du chiffre d'affaires le plus élevé au chiffre d'affaires le plus bas, spécifié dans ORDER BY.

Le tableau product_sales original ressemble à ceci :

Par conséquent, le code ci-dessus classera les lignes de l'ensemble de données de manière décroissante : de la plus élevée à la plus faible.

Voici le résultat de la requête, avec les rangs affichés dans une colonne distincte :

Le classement serait sensiblement différent si vous remplaciez DESC par ASC dans ORDER BY, comme indiqué ci-dessous :

SELECT 
  id,
  date,
  sales,
  product_name,
  DENSE_RANK() OVER(ORDER BY sales ASC) AS ranking
FROM product_sales; 

Le classement s'effectue désormais de la vente la plus faible à la vente la plus élevée :

ORDER BY et PARTITION BY dans Fonctions de fenêtrage

Nous savons ce que fait ORDER BY dans les fonctions de fenêtre. Qu'en est-il de PARTITION BY? Il s'agit d'une clause facultative qui divise les données en sous-ensembles basés sur une ou plusieurs catégories. Pour ce faire, nous spécifions des colonnes dans la clause PARTITION BY, comme ceci : PARTITION BY product_name. Je montrerai comment cela fonctionne dans la section suivante.

Sans PARTITION BY, ORDER BY trie les données dans l'ensemble des résultats. Mais si vous l'utilisez avec PARTITION BY, il triera les données de chaque partition séparément.

Exemple : Utilisation de ORDER BY avec PARTITION BY dans une fonction de fenêtre

Lorsqu'il est utilisé avec PARTITION BY, ORDER BY trie les données dans chaque partition.

L'utilisation de ORDER BY avec la fonction de fenêtre SUM() produit une somme cumulative (c'est-à-dire la somme de la valeur de la ligne actuelle et de toutes les lignes qui la précèdent dans la partition). Par exemple, le code ci-dessous calcule la somme cumulée des ventes par nom de produit (comme spécifié dans PARTITION BY) de la date la plus ancienne à la date la plus récente (comme spécifié dans ORDER BY).

SELECT 
  id,
  date,
  sales,
  product_name,
  SUM(sales) OVER(PARTITION BY product_name ORDER BY date ASC) AS cumulative_sum
FROM product_sales; 

La requête divise d'abord l'ensemble de données en partitions par nom de produit. Ensuite, les valeurs de chaque partition seront triées de la date la plus ancienne à la date la plus récente, spécifiée à l'adresse ORDER BY. La fonction de fenêtre SUM() calcule ensuite le total cumulé en additionnant les ventes de la date actuelle et les ventes de toutes les ventes précédentes dans la partition.

Voici le résultat, qui montre chaque ligne et les ventes cumulées pour chaque produit :

Si nous supprimons ORDER BY du code, comme indiqué ci-dessous ...

SELECT 
  id,
  date,
  sales,
  product_name,
  SUM(sales) OVER(PARTITION BY product_name) AS cumulative_sum
FROM product_sales; 

... le code renverra la somme totale des ventes pour chaque produit. En d'autres termes, en omettant simplement ORDER BY d'une fonction de fenêtre, vous perdez la possibilité d'effectuer des calculs cumulatifs. Sans ORDER BY, toutes les lignes de la partition constituent le cadre de la fenêtre.

ORDER BY dans Fonctions de fenêtrage vs. ORDER BY normal

Quand je dis régulier, je parle de la norme ORDER BY à la fin de la requête. Quelle est la différence avec ORDER BY dans une fonction de fenêtre ?

Un ORDER BY normal trie la sortie d'une requête, tandis que ORDER BY dans les fonctions de fenêtre trie une fenêtre de données ou une partition de données.

Exemple : ORDER BY dans Fonctions de fenêtrage et ORDER BY normal

Cette requête utilise la fonction de fenêtre DENSE_RANK() avec PARTITION BY et ORDER BY pour classer les dates de vente de chaque produit :

SELECT 
  id,
  date,
  sales,
  product_name,
  DENSE_RANK() OVER(PARTITION BY product_name ORDER BY sales DESC) AS day_rank
FROM product_sales;

Vous pouvez constater que le résultat global n'est pas trié des ventes les plus élevées aux plus faibles. Le tri décroissant des dates n'est appliqué qu'à l'intérieur de chaque partition.

Si vous souhaitez que votre résultat soit trié par ordre décroissant des ventes, vous devez le faire explicitement en ajoutant ORDER BY à la fin de la requête :

SELECT 
  id,
  date,
  sales,
  product_name,
  DENSE_RANK() OVER(PARTITION BY product_name ORDER BY sales DESC) AS sales_rank_by_product
FROM product_sales
ORDER BY sales DESC;

Vous obtenez maintenant un résultat trié par ventes et par dates. Vous pouvez voir comment le rang des ventes d'une date se compare à celui des ventes du même produit à d'autres dates.

Fonctions de fenêtrage Cette exigence ORDER BY

Comme vous l'avez vu dans l'exemple précédent, les fonctions de fenêtre peuvent fonctionner sans ORDER BY. ORDER BY est généralement considérée comme une clause facultative dans les fonctions de fenêtre.

Mais même si ORDER BY est considéré comme une clause facultative dans les fonctions de fenêtre, certaines fonctions de fenêtre nécessitent ORDER BY pour fonctionner. Dans ce cas, ORDER BY devient obligatoire; ces fonctions de fenêtre nécessitent un ordre de tri pour fonctionner correctement. Ces fonctions sont les suivantes

• RANK()
• DENSE_RANK()
• NTILE()
• LEAD()
• LAG()

Cadres de fenêtre par défaut avec et sans ORDER BY

Le comportement d'une fonction de fenêtre change selon qu'elle est écrite avec ou sans ORDER BY. Plus précisément, la présence ou l'absence de ORDER BY a un impact sur le cadre de fenêtre par défaut.

S'il n'y a pas ORDER BY, le cadre de fenêtre par défaut comprend la ligne actuelle et toutes les lignes qui la précèdent et la suivent. En d'autres termes, toutes les lignes de la partition sont incluses. Nous l'avons vu avec l'exemple de la somme cumulée : en l'absence de ORDER BY, le cadre de la fenêtre s'étend par défaut à l'ensemble de la partition et la somme devient la somme totale.

S'il y a ORDER BY, le cadre de la fenêtre inclut la valeur actuelle et toutes les valeurs précédentes. Nous avons vu cela avec l'exemple de la somme cumulative : avec ORDER BY, le cadre de la fenêtre inclut toutes les lignes précédant la ligne actuelle et la ligne actuelle.

Si vous n'aimez pas le cadre de fenêtre par défaut, vous pouvez le définir explicitement à l'aide des mots-clés ROWS et RANGE.

Utilisation de ORDER BY dans Fonctions de fenêtrage

Voilà : ORDER BY - parfois obligatoire, parfois facultative - est la clause qui fait et défait parfois les fonctions de fenêtre.

Cependant, tout savoir sur ORDER BY dans les fonctions de fenêtre ne sert à rien si vous ne pouvez pas l'utiliser dans vos requêtes. Testez donc ce que vous avez appris ici en résolvant les défis de codage de notre cours sur les fonctions de fenêtre et de notre jeu d'exercicesFonctions de fenêtrage .

Pour encore plus d'exercices, résolvez ces 11 exercices sur les fonctions de fenêtre et répondez aux 10 meilleures questions d'entretien sur les fonctions de fenêtre. Bon apprentissage !