Utiliser les fonctions de classement et d’ensemble de lignes
Les fonctions de classement et d’ensemble de lignes ne sont pas des fonctions scalaires, car elles ne retournent pas une valeur unique. Ces fonctions acceptent un ensemble de lignes comme entrée et retournent un ensemble de lignes comme sortie.
Fonctions de classement
Les fonctions de classement vous permettent d’effectuer des calculs sur un ensemble de lignes défini par l’utilisateur. Ces fonctions incluent des fonctions de classement, de décalage, d’agrégation et de distribution.
Cet exemple utilise la fonction RANK pour calculer un classement en fonction de ListPrice, avec le prix le plus élevé classé à 1 :
SELECT TOP 100 ProductID, Name, ListPrice,
RANK() OVER(ORDER BY ListPrice DESC) AS RankByPrice
FROM Production.Product AS p
ORDER BY RankByPrice;
Les résultats de requête peuvent ressembler à ceci :
ProductID
Nom
ListPrice
RankByPrice
749
Road-150 Red, 62
3578.27
1
750
Road-150 Red, 44
3578.27
1
751
Road-150 Red, 48
3578.27
1
771
Mountain-100 Silver, 38
3399.99
4
772
Mountain-100 Silver, 42
3399.99
4
775
Mountain-100 Black, 38
3374.99
6
...
...
...
...
OVER
Vous pouvez utiliser la clause OVER pour définir des partitions ou des regroupements dans les données. Par exemple, la requête suivante étend l’exemple précédent pour calculer des classements basés sur les prix pour des produits dans chaque catégorie.
SELECT c.Name AS Category, p.Name AS Product, ListPrice,
RANK() OVER(PARTITION BY c.Name ORDER BY ListPrice DESC) AS RankByPrice
FROM Production.Product AS p
JOIN Production.ProductCategory AS c
ON p.ProductCategoryID = c.ProductcategoryID
ORDER BY Category, RankByPrice;
Les résultats de cette requête peuvent se présenter comme suit :
Category
Produit
ListPrice
RankByPrice
Cuissards
Men’s Bib-Shorts, S
89.99
1
Cuissards
Men’s Bib-Shorts, M
89.99
1
Porte-vélos
Hitch Rack - 4-Bike
120
1
Supports à vélos
All-Purpose Bike Stand
159
1
Bidons et porte-bidons
Mountain Bottle Cage
9.99
1
Bidons et porte-bidons
Road Bottle Cage
8.99
2
Bidons et porte-bidons
Water Bottle - 30 oz.
4.99
3
Jeux de pédalier
HL Bottom Bracket
121.49
1
Jeux de pédalier
ML Bottom Bracket
101.24
2
Jeux de pédalier
LL Bottom Bracket
53.99
3
...
...
...
...
Notes
Notez que plusieurs lignes ont la même valeur de classement, et que certaines valeurs sont ignorées. Cela est dû au fait que nous utilisons RANK uniquement. En fonction des exigences, vous souhaiterez peut-être éviter les liens à la même valeur de classement. Vous pouvez contrôler la valeur de classement avec d’autres fonctions, DENSE_RANK, NTILE et ROW_NUMBER, selon les besoins. Pour plus d’informations sur ces fonctions, consultez la documentation de référence de Transact-SQL.
Fonctions d’ensemble de lignes
Les fonctions d’ensemble de lignes retournent une table virtuelle qui peut être utilisée dans la clause FROM comme source de données. Ces fonctions acceptent des paramètres spécifiques à la fonction d’ensemble de lignes elle-même. Elles incluent OPENDATASOURCE, OPENQUERY, OPENROWSET, OPENXML et OPENJSON.
Les fonctions OPENDATASOURCE, OPENQUERY et OPENROWSET vous permettent de transmettre une requête à un serveur de base de données distant. Le serveur distant retourne alors un jeu de lignes de résultat. Par exemple, la requête ci-dessous utilise OPENROWSET pour obtenir les résultats d’une requête à partir d’une instance SQL Server nommée SalesDB.
SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=SalesDB;Trusted_Connection=yes;',
'SELECT Name, ListPrice
FROM AdventureWorks.Production.Product') AS a;
Pour utiliser des serveurs distants, vous devez activer certaines options avancées dans l’instance de SQL Server dans laquelle vous exécutez la requête.
Les fonctions OPENXML et OPENJSON vous permettent d’interroger des données structurées au format XML ou JSON et d’extraire des valeurs dans un ensemble de lignes tabulaire.
L’exploration détaillée des fonctions d’ensemble de lignes dépasse le cadre de ce module. Pour plus d’informations, consultez la documentation de référence de Transact-SQL.