juillet 18, 2020

SQLShack (Français)

Dans cet article, nous allons discuter la fonction SQL ROW_NUMBER. Ceci est une continuation de la série SQL essential. Dans ce guide, je vais vous expliquer ce quest une fonction de fenêtre et vous verrez des exemples dexemples pour comprendre les concepts derrière la fonction SQL ROW_NUMBER.

Introduction

Le plus La fonction couramment utilisée dans SQL Server est la fonction SQL ROW_NUMBER. La fonction SQL ROW_NUMBER est disponible à partir de SQL Server 2005 et des versions ultérieures.

ROW_NUMBER ajoute un numéro incrémentiel unique à la grille de résultats. Lordre dans lequel les numéros de ligne sont appliqués est déterminé par lexpression ORDER BY. La plupart du temps, une ou plusieurs colonnes sont spécifiées dans lexpression ORDER BY, mais il est possible dutiliser des expressions plus complexes ou même une sous-requête. Ainsi, cela crée une valeur intégrale toujours croissante et commence toujours à 1 et les lignes suivantes obtiennent la valeur immédiatement supérieure.

Vous pouvez également lutiliser avec une clause PARTITION BY. Mais quand il franchit une limite ou une limite de partition, il réinitialise le compteur et commence à 1. Ainsi, la partition peut avoir les valeurs 1, 2, 3, et ainsi de suite et les secondes partitions recommencent le compteur à partir de 1, 2, 3… et ainsi de suite.

Bases:

  1. La fonction SQL ROW_NUMBER est une génération non persistante dune séquence de valeurs temporaires et elle est calculée dynamiquement lorsque la requête est ensuite exécutée.
  2. Il ny a aucune garantie que les lignes renvoyées par une requête SQL utilisant la fonction SQL ROW_NUMBER seront ordonnées exactement de la même manière à chaque exécution.
  3. Les fonctions ROW_NUMBER et RANK sont similaires. La sortie de ROW_NUMBER est une séquence de valeurs qui commence à 1 avec un incrément de 1, mais alors que la fonction RANK, les valeurs sont également incrémentées de 1 mais les valeurs se répéteront pour les égalités.
  4. Si vous avez une expérience avec Oracle puis le ROWNUM vous est plus familier. Cest une pseudo-colonne. Il commence par 1 et descend tout en augmentant de un, jusquà la fin du tableau.
  5. La fonction SQL ROW_NUMBER est de nature dynamique et nous sommes autorisés à réinitialiser les valeurs à laide de la clause PARTITION BY
  6. La clause ORDER BY de la requête et la clause ORDER BY de lOVER nont rien à voir les uns avec les autres.

Syntaxe

1
2

ROW_NUMBER ()
OVER (] order_by_clause col1, col2 ..)

ROW_NUMBER

ROW_NUMBER suivi de la fonction OVER puis, entre parenthèses, utilisez une clause ORDER BY. Il est nécessaire dutiliser la clause ORDER BY afin dimposer une sorte dordre pour lensemble de résultats.

OVER

La clause OVER définit la fenêtre ou lensemble de lignes que la fenêtre fonction fonctionne, il est donc très important pour vous de comprendre. Les composants possibles de la clause OVER sont ORDER BY et PARTITION BY.

Lexpression ORDER BY de la clause OVER est prise en charge lorsque les lignes doivent être alignées dune certaine manière pour que la fonction fonctionne.

1
PARTITION BY value_expression1

PARTITION BY

La clause Partition By est facultative. Lors de la spécification de la valeur, il divise lensemble de résultats produit par la clause FROM en partitions auxquelles la fonction SQL ROW_NUMBER est appliquée. Les valeurs spécifiées dans la clause PARTITION définissent les limites du jeu de résultats. Si la clause PARTITION BY nest pas spécifiée, la clause OVER opère sur toutes les lignes du jeu de résultats comme un seul jeu de données. Cette clause peut être constituée dune ou plusieurs colonnes, dune expression plus complexe ou même dune sous-requête.

order_by_clause

La clause Order by est une clause obligatoire. Il détermine la séquence et lassociation de la valeur temporaire aux lignes dune partition spécifiée. La clause ORDER BY est une expression de la clause OVER et elle détermine comment les lignes doivent être alignées dune certaine manière pour la fonction.

Démo

Dans cette section, nous Jetons un œil à la fonction SQL ROW_NUMBER. Pour toute la démo, jai utilisé la base de données AdventureWorks2016.

Comment utiliser ROW_NUMBER dans une requête SQL

Les exemples suivants, nous verrons lutilisation de la clause OVER.

Obtenons la liste de tous les clients en projetant les colonnes telles que SalesOrderID, OrderDate, SalesOrderNumber, SubTotal, TotalDue et RowNum. La fonction Row_Number est appliquée avec lordre de la colonne CustomerID.La valeur temporaire commence à 1 attribuée en fonction de lordre du CustomerID, et les valeurs se poursuivent jusquaux dernières lignes de la table. Lordre de CustomerID nest pas garanti car nous ne spécifions pas la clause ORDER BY dans la requête.

1
2
3
4
5
6
7
8
9
10
11

UTILISER AdventureWorks2016;
GO
SELECT ROW_NUMBER () OVER (
ORDER BY CustomerID) AS RowNum,
CustomerID,
SalesOrderID,
OrderDate,
SalesOrderNumber,
SubTotal,
TotalDue
FROM Sales.SalesOrderHeader;

Comment utiliser la clause Trier par

Lexemple suivant utilise la clause ORDER BY dans la requête. Clause ORDER BY dans la requête appliquée à la colonne SalesOrderID. Nous pouvons voir que les lignes en sortie sont toujours ordonnées et renvoyées. Le Row_Number est toujours appliqué au CustomerID. La sortie indique que ORDER BY de la requête et ORDER BY de la clause OVER sont indépendants de la sortie.

1
2
3
4
5
6
7
8
9
10
11
12

USE AdventureWorks2016;
GO
SELECT ROW_NUMBER () OVER (
ORDER BY CustomerID) AS RowNum,
CustomerID,
SalesOrderID,
OrderDate,
SalesOrderNumber,
SubTotal,
TotalDue
FROM Sales.SalesOrderHeader
ORDER BY SalesOrderID;

Comment utiliser plusieurs colonnes avec la clause OVER

Lexemple suivant montre que nous avons répertorié customerID et OrderDate dans la clause ORDER BY. Cela donne au client les détails de la commande la plus récente ainsi que la séquence de numéros attribués à lensemble du jeu de résultats.

1
2
3
4
5
6
7
8
9
10

USE AdventureWorks2016;
GO
SELECT ROW_NUMBER () OVER (ORDER BY CustomerID, OrderDate DESC) AS RowNum,
CustomerID,
SalesOrderID,
OrderDate,
SalesOrderNumber,
SubTotal,
TotalDue
FROM Sales.SalesOrderHeader

Comment utiliser la fonction SQL ROW_NUMBER avec PARTITION

Lexemple suivant utilise la clause PARTITION BY sur les champs CustomerID et OrderDate. Dans la sortie, vous pouvez voir que le client 11019 a trois commandes pour le mois 2014-juin. Dans ce cas, la partition se fait sur plus dune colonne.

La partition est une combinaison de OrderDate et de CustomerID. Le numéro de ligne recommencera pour chaque combinaison unique de OrderDate et de CustomerID. De cette manière, il est facile de trouver le client qui a passé plus d’une commande le même jour.

1
2
3
4
5
6
7
8
9
10
11
12

USE AdventureWorks2016;
GO
SELECT ROW_NUMBER () OVER (PARTITION BY CustomerID,
DATEADD (MONTH, DATEDIFF (MONTH, 0, OrderDate), 0)
ORDER BY SubTotal DESC) AS MonthlyOrders,
CustomerID,
SalesOrderID,
OrderDate,
SalesOrderNumber,
SubTotal,
TotalDue
FROM Sales.SalesOrderHeader;

Comment renvoyer un sous-ensemble de lignes en utilisant CTE et ROW_NUMBER

Lexemple suivant, nous allons analyser SalesOrderHeader pour afficher les cinq plus grosses commandes passées par chaque client chaque mois. À laide de la fonction Month, les colonnes orderDate sont manipulées pour récupérer la partie mois.De cette manière, les ventes correspondant au mois spécifique (OrderDate) avec le client (CustomerID) sont partitionnées.

Pour répertorier les cinq commandes les plus importantes de chaque mois pour chaque client, un CTE est utilisé. Une fenêtre est créée sur les données de la partition et les valeurs lui sont attribuées, puis le CTE est appelé pour récupérer les commandes les plus importantes.

1
2
3
4
5
6
7
8
9
10
11
12

WITH cte
AS (SELECT ROW_NUMBER OVER (PARTITION BY customerID, MONTH (OrderDate) ORDER BY SubTotal DESC, TotalDue DESC) AS ROW_NUM,
CustomerID,
MONTH (OrderDate) Month,
SubTotal,
TotalDue,
OrderDate
FROM Sales.SalesOrderHeader
)
SELECT *
FROM cte
WHERE ROW_NUM < = 5

Résumé

Jusquà présent, nous avons examiné en détail la fonction SQL ROW_NUMBER. Nous avons discuté de plusieurs exemples, du plus simple au plus complexe. Nous avons également expliqué comment utiliser la fonction SQL ROW_NUMBER avec les CTE (Common Table Expressions). Dans la plupart des cas, vous verrez toujours une clause over avec chaque fonction de fenêtre.

La clause over définit la fenêtre que chaque ligne voit. Dans la clause over, il y a une partition by, là encore elle est prise en charge par chaque fonction de fenêtre, suivie de la clause order by. Cest tout pour le moment… Jespère que vous avez apprécié la lecture de larticle.

  • Auteur
  • Messages récents
Je suis un technologue en bases de données ayant plus de 11 ans dexpérience pratique et riche sur les technologies de bases de données. Je suis Microsoft Certified Professional et soutenu par un diplôme en Master of Computer Application.
Ma spécialité réside dans la conception de & implémentation de solutions de haute disponibilité et de migration de base de données multiplateforme. Les technologies actuellement utilisées sont SQL Server, PowerShell, Oracle et MongoDB.
Voir tous les articles de Prashanth Jayaram

Derniers articles de Prashanth Jayaram (voir tout)
  • Un aperçu rapide de laudit de base de données en SQL – 28 janvier 2021
  • Comment configurer Azure Data Sync entre les bases de données Azure SQL et SQL Server local – 20 janvier 2021
  • Comment effectuer des opérations dimportation / exportation de base de données Azure SQL à laide de PowerShell – 14 janvier , 2021

admin
0

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Archives

Articles récents