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 de valeur unique. Ces fonctions acceptent un ensemble de lignes comme entrée et retournent un ensemble de lignes en tant que sortie.
Fonctions de classement
Les fonctions de classement vous permettent d’effectuer des calculs sur un ensemble défini par l’utilisateur de lignes. Ces fonctions incluent le classement, le décalage, l’agrégation et les fonctions de distribution.
Cet exemple utilise la fonction RANK pour calculer un classement basé sur 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 la requête peuvent ressembler à ceci :
Identifiant produit
Nom
ListPrice
ClassementParPrix
749
Route-150 Rouge, 62
3578.27
1
750
Route 150 Rouge, 44
3578.27
1
751
Route-150 Rouge, 48
3578.27
1
771
Mountain-100 Silver, 38
3399.99
4
772
Mountain-100 Silver, 42
3399.99
4
775
Montagne-100 Noir, 38
3374.99
6
...
...
...
...
SUR
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 les classements basés sur les prix pour les produits de 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 ressembler à ceci :
Catégorie
Produit
ListPrice
ClassementParPrix
Bib Shorts
Bib-Shorts pour hommes, S
89.99
1
Bib Shorts
Bib-Shorts pour hommes, M
89.99
1
Racks à vélo
Hitch Rack - 4-Bike
120
1
Stand de vélo
stand de vélo All-Purpose
159
1
Bottles and Cages
Mountain Bottle Cage
9.99
1
Bottles and Cages
Road Bottle Cage
8.99
2
Bottles and Cages
Bouteille d’eau - 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
...
...
...
...
Remarque
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. Selon l’exigence, 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, si nécessaire. Pour plus d’informations sur ces fonctions, consultez la documentation de référenceTransact-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 prennent des paramètres spécifiques à la fonction d’ensemble de lignes elle-même. Ils incluent OPENDATASOURCE, OPENQUERY, OPENROWSET, OPENXML et OPENJSON.
Les fonctions OPENDATASOURCE, OPENQUERY et OPENROWSET vous permettent de passer une requête à un serveur de base de données distant. Le serveur distant retourne ensuite un ensemble de lignes de résultat. Par exemple, la requête suivante 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 SQL Server où 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.
Une exploration détaillée des fonctions d’ensemble de lignes dépasse la portée de ce module. Pour plus d’informations, consultez la documentation de référenceTransact-SQL.