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
Nazwa
Cena Katalogowa
RankingWedługCeny
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
Mountain-100 Srebrny, 38
3399.99
4
772
Mountain-100 Srebrny, 42
3399.99
4
775
Mountain-100 Czarny, 38
3374.99
6
...\
...\
...\
...\
KONIEC
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
Produkt
Cena Katalogowa
RankingWedługCeny
Bib-Shorts
Spodenki męskie z szelkami, S
89.99
1
Bib-Shorts
Męskie gipelszorty, M
89.99
1
Stojaki rowerowe
Bagażnik rowerowy na hak - 4-rowerowy
120
1
Stojaki rowerowe
stoisko rowerowe All-Purpose
159
1
Butelki i klatki
Uchwyt na butelkę górski
9.99
1
Butelki i klatki
Koszyk na bidon do roweru szosowego
8.99
2
Butelki i klatki
Butelka wody - 30 uncji.
4,99
3
Nawiasy dolne
HL — suport dolny
121.49
1
Nawiasy dolne
Dolny wspornik ML
101.24
2
Nawiasy dolne
Dolny nawias LL
53.99
3
...\
...\
...\
...\
Uwaga / Notatka
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 wymagań, możesz chcieć unikać remisów na tej samej pozycji rankingu. 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ą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 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ąTransact-SQL.