Classificatie- en rijsetfuncties gebruiken
Classificatie- en rijsetfuncties zijn geen scalaire functies omdat ze geen enkele waarde retourneren. Deze functies accepteren een set rijen als invoer en retourneren een set rijen als uitvoer.
Classificatiefuncties
Met classificatiefuncties kunt u berekeningen uitvoeren op basis van een door de gebruiker gedefinieerde set rijen. Deze functies omvatten classificatie-, offset-, statistische en distributiefuncties.
In dit voorbeeld wordt de functie RANK gebruikt om een rangschikking te berekenen op basis van de ListPrice, waarbij de hoogste prijs is gerangschikt op 1:
SELECT TOP 100 ProductID, Name, ListPrice,
RANK() OVER(ORDER BY ListPrice DESC) AS RankByPrice
FROM Production.Product AS p
ORDER BY RankByPrice;
De queryresultaten kunnen er als volgt uitzien:
ProductID
Naam
Lijstprijs
RankByPrice
749
Road-150 Rood, 62
3578.27
1
750
Road-150 Rood, 44
3578.27
1
751
Road-150 Rood, 48
3578.27
1
771
Berg-100 zilver, 38
3399.99
4
772
Berg-100 zilver, 42
3399.99
4
775
Berg-100 zwart, 38
3374.99
6
...
...
...
...
EINDE
U kunt de OVER-component gebruiken om partities of groeperingen in de gegevens te definiëren. Met de volgende query wordt bijvoorbeeld het vorige voorbeeld uitgebreid om classificaties op basis van prijzen te berekenen voor producten binnen elke categorie.
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;
De resultaten van deze query kunnen er ongeveer als volgt uitzien:
Categorie
Product
Lijstprijs
RankByPrice
bibshorts
Heren bib-shorts, S
89.99
1
bibshorts
Heren fietsbroek, M
89.99
1
Fietsenrekken
Trekhaakrek - 4 fietsen
120
1
Fietsstandaarden
All-Purpose fietsstandaard
159
1
Flessen en kooien
Mountainbike bidonhouder
9.99
1
Flessen en kooien
Flessenhouder voor racefiets
8.99
2
Flessen en kooien
Waterfles - 30 oz.
4,99
3
Onderhaken
HL-onderhaak
121.49
1
Onderhaken
ML-onderhaak
101.24
2
Onderhaken
LL-onderhaak
53.99
3
...
...
...
...
Opmerking
U ziet dat meerdere rijen dezelfde rangschikkingswaarde hebben en sommige waarden worden overgeslagen. Dit komt omdat we alleen RANK gebruiken. Afhankelijk van de vereiste wilt u mogelijk ties voorkomen met dezelfde rangschikkingswaarde. U kunt de rangschikkingswaarde zo nodig beheren met andere functies, DENSE_RANK, NTILE en ROW_NUMBER. Zie de Transact-SQL referentiedocumentatie voor meer informatie over deze functies.
Rijensetfuncties
Rowset-functies retourneren een virtuele tabel die als gegevensbron kan worden gebruikt in de FROM-component. Deze functies hebben parameters die specifiek zijn voor de rijsetfunctie zelf. Ze omvatten OPENDATASOURCE, OPENQUERY, OPENROWSET, OPENXML en OPENJSON.
Met de functies OPENDATASOURCE, OPENQUERY en OPENROWSET kunt u een query doorgeven aan een externe databaseserver. De externe server retourneert vervolgens een set resultaatrijen. De volgende query maakt bijvoorbeeld gebruik van OPENROWSET om de resultaten van een query op te halen uit een SQL Server-exemplaar met de naam SalesDB.
SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=SalesDB;Trusted_Connection=yes;',
'SELECT Name, ListPrice
FROM AdventureWorks.Production.Product') AS a;
Als u externe servers wilt gebruiken, moet u enkele geavanceerde opties inschakelen in het SQL Server-exemplaar waarop u de query uitvoert.
Met de functies OPENXML en OPENJSON kunt u gestructureerde gegevens opvragen in XML- of JSON-indeling en waarden extraheren in een rijenset in tabelvorm.
Een gedetailleerde verkenning van rijensetfuncties valt buiten het bereik van deze module. Zie de Transact-SQL referentiedocumentatie voor meer informatie.