Rangsorolási és sorhalmazfüggvények használata
A rangsorolási és sorhalmazfüggvények nem skaláris függvények, mert egyetlen értéket sem adnak vissza. Ezek a függvények bemenetként fogadnak el egy sorhalmazt, és kimenetként visszaadnak egy sorhalmazt.
Rangsorolási függvények
A rangsorolási függvények lehetővé teszik, hogy számításokat hajtson végre egy felhasználó által meghatározott sorkészleten. Ezek a függvények közé tartoznak a rangsorolási, eltolási, összesítő és terjesztési függvények.
Ez a példa a RANK függvénnyel számít ki egy rangsort a ListPrice alapján, a legmagasabb ár pedig 1:
SELECT TOP 100 ProductID, Name, ListPrice,
RANK() OVER(ORDER BY ListPrice DESC) AS RankByPrice
FROM Production.Product AS p
ORDER BY RankByPrice;
A lekérdezés eredményei a következőképpen nézhetnek ki:
Termékazonosító
Név
listaár
RankByPrice (Ár szerinti rangsorolás)
749
Road-150 Vörös, 62
3578.27
1
750
Road-150 Red, 44
3578.27
1
751
Road - 150 Red, 48
3578.27
1
771
Mountain-100 ezüst, 38
3399.99
4
772
Mountain-100 ezüst, 42
3399.99
4
775
Mountain-100 Fekete, 38
3374.99
6
...
...
...
...
VÉGE
Az OVER záradék használatával partíciókat vagy csoportosításokat definiálhat az adatokon belül. Az alábbi lekérdezés például kiterjeszti az előző példát az egyes kategóriákban lévő termékek áralapú rangsorolásának kiszámítására.
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;
A lekérdezés eredményei a következőképpen nézhetnek ki:
Kategória
Termék
listaár
RankByPrice (Ár szerinti rangsorolás)
Bib-Shorts
Férfi bib-shorts, S
89.99
1
Bib-Shorts
Férfi bib-shorts, M
89.99
1
Kerékpártartók
Hitch Rack - 4-Bike
120
1
Biciklitámaszok
All-Purpose Biciklitartó
159
1
Palackok és ketrecek
Hegyi Kulacstartó
9.99
1
Palackok és ketrecek
Országúti kulacstartó
8.99
2
Palackok és ketrecek
Vízpalack - 30 oz.
4,99
3
Középcsapágyak
HL középcsapágy
121.49
1
Középcsapágyak
ML középcsapágy
101.24
2
Középcsapágyak
LL középcsapágy
53.99
3
...
...
...
...
Megjegyzés:
Figyelje meg, hogy több sor azonos rangértékekkel rendelkezik, és néhány érték kihagyva. Ennek az az oka, hogy csak a RANK függvényt használjuk. A követelménytől függően érdemes lehet elkerülni az azonos rangértéken lévő kötelékeket. Szükség esetén más függvényekkel, DENSE_RANK, NTILE és ROW_NUMBER is szabályozhatja a rangértéket. A függvényekkel kapcsolatos részletekért tekintse meg a Transact-SQL referenciadokumentációját.
Sorhalmazfüggvények
A sorhalmazfüggvények egy olyan virtuális táblát adnak vissza, amely adatforrásként használható a FROM záradékban. Ezek a függvények magukra a sorhalmazfüggvényre jellemző paramétereket veszik figyelembe. Ezek közé tartozik az OPENDATASOURCE, az OPENQUERY, az OPENROWSET, az OPENXML és az OPENJSON.
Az OPENDATASOURCE, AZ OPENQUERY és az OPENROWSET függvényekkel lekérdezést adhat át egy távoli adatbázis-kiszolgálónak. A távoli kiszolgáló ezután eredménysorokat ad vissza. Az alábbi lekérdezés például az OPENROWSET használatával lekéri a lekérdezés eredményeit egy SalesDB nevű SQL Server-példányból.
SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=SalesDB;Trusted_Connection=yes;',
'SELECT Name, ListPrice
FROM AdventureWorks.Production.Product') AS a;
Távoli kiszolgálók használatához engedélyeznie kell bizonyos speciális beállításokat abban az SQL Server-példányban, ahol a lekérdezést futtatja.
Az OPENXML és AZ OPENJSON függvényekkel strukturált adatokat kérdezhet le XML vagy JSON formátumban, és adatokat nyerhet ki egy táblázatos sorhalmazba.
A sorhalmazfüggvények részletes feltárása meghaladja a modul hatókörét. További információ: Transact-SQL referenciadokumentáció.