Korzystanie z funkcji klasyfikacji i zestawów wierszy
Funkcje klasyfikacji i zestawu wierszy nie są funkcjami skalarnych, ponieważ nie zwracają jednej wartości. Te funkcje akceptują zestaw wierszy jako dane wejściowe i zwracają zestaw wierszy jako dane wyjściowe.
Funkcje klasyfikowania
Funkcje klasyfikacji umożliwiają wykonywanie obliczeń względem zestawu wierszy zdefiniowanych przez użytkownika. Funkcje te obejmują funkcje klasyfikacji, przesunięcia, agregacji i dystrybucji.
W tym przykładzie użyto funkcji RANK, aby obliczyć klasyfikację na podstawie wartości ListPrice z najwyższą ceną w rankingu 1:
SELECT TOP 100 ProductID, Name, ListPrice,
RANK() OVER(ORDER BY ListPrice DESC) AS RankByPrice
FROM Production.Product AS p
ORDER BY RankByPrice;
Wyniki zapytania mogą wyglądać następująco:
ProductID
Nazwisko
ListPrice
RankByPrice
749
Road-150 Czerwony, 62
3578.27
1
750
Road-150 Czerwony, 44
3578.27
1
751
Road-150 Czerwony, 48
3578.27
1
771
Górskie — 100 srebrnych, 38
3399.99
100
772
Górskie — 100 srebrnych, 42
3399.99
100
775
Górskie — 100 czarnych, 38
3374.99
6
...
...
...
...
PONAD
Klauzulę OVER można użyć do zdefiniowania partycji lub grupowania w danych. Na przykład poniższe zapytanie rozszerza poprzedni przykład, aby obliczyć rankingi oparte na cenach dla produktów w każdej kategorii.
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;
Wyniki tego zapytania mogą wyglądać mniej więcej tak:
Kategoria
Rezultat
ListPrice
RankByPrice
Bib-Shorts
Bib-Shorts mężczyzn, S
89.99
1
Bib-Shorts
Bib-Shorts mężczyzn, M
89.99
1
Stojaki rowerowe
Stojak hitch - 4-Rower
120
1
Stojaki rowerowe
Stojak na rowery ogólnego przeznaczenia
159
1
Butelki i klatki
Mountain Bottle Cage
9,99
1
Butelki i klatki
Road Bottle Cage
8.99
2
Butelki i klatki
Butelka wody - 30 uncji.
4,99
3
Wsporniki dolne
HL — dolny nawias kwadratowy
121.49
1
Wsporniki dolne
Dolny nawias uczenia maszynowego
101.24
2
Wsporniki dolne
LL Bottom Bracket
53.99
3
...
...
...
...
Uwaga
Zwróć uwagę, że kilka wierszy ma tę samą wartość rangi, a niektóre wartości zostały pominięte. Dzieje się tak, ponieważ używamy tylko funkcji RANK. W zależności od wymagania możesz unikać powiązań z tą samą wartością klasyfikacji. Możesz kontrolować wartość rangi za pomocą innych funkcji, DENSE_RANK, NTILE i ROW_NUMBER zgodnie z potrzebami. Aby uzyskać szczegółowe informacje na temat tych funkcji, zobacz dokumentację referencyjną języka Transact-SQL.
Funkcje zestawu wierszy
Funkcje zestawu wierszy zwracają tabelę wirtualną, która może być używana w klauzuli FROM jako źródło danych. Te funkcje przyjmują parametry specyficzne dla samej funkcji zestawu wierszy. Obejmują one PLIKI OPENDATASOURCE, OPENQUERY, OPENROWSET, OPENXML i OPENJSON.
Funkcje OPENDATASOURCE, OPENQUERY i OPENROWSET umożliwiają przekazywanie zapytania do zdalnego serwera bazy danych. Następnie serwer zdalny zwróci zestaw wierszy wyników. Na przykład następujące zapytanie używa metody OPENROWSET, aby uzyskać wyniki zapytania z wystąpienia programu SQL Server o nazwie SalesDB.
SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=SalesDB;Trusted_Connection=yes;',
'SELECT Name, ListPrice
FROM AdventureWorks.Production.Product') AS a;
Aby korzystać z serwerów zdalnych, należy włączyć niektóre zaawansowane opcje w wystąpieniu programu SQL Server, w którym uruchamiasz zapytanie.
Funkcje OPENXML i OPENJSON umożliwiają wykonywanie zapytań dotyczących danych strukturalnych w formacie XML lub JSON i wyodrębnianie wartości do zestawu wierszy tabelarycznych.
Szczegółowa eksploracja funkcji zestawu wierszy wykracza poza zakres tego modułu. Aby uzyskać więcej informacji, zobacz dokumentację referencyjną języka Transact-SQL.