Использование функций ранжирования и набора строк
Функции ранжирования и набора строк не скалярные функции, так как они не возвращают одно значение. Эти функции принимают набор строк в качестве входных данных и возвращают набор строк в качестве выходных данных.
Ранжирование функций
Функции ранжирования позволяют выполнять вычисления для определяемого пользователем набора строк. К этим функциям относятся функции ранжирования, смещения, агрегирования и распределения.
В этом примере функция RANK используется для вычисления ранга на основе ListPrice, где самой высокой цене присваивается ранг 1.
SELECT TOP 100 ProductID, Name, ListPrice,
RANK() OVER(ORDER BY ListPrice DESC) AS RankByPrice
FROM Production.Product AS p
ORDER BY RankByPrice;
Результаты запроса могут выглядеть следующим образом:
ProductID
Имя
ПрейскурантнаяЦена
РейтингПоЦене
749
Road-150 Red, 62
3578,27
1
750
Road-150 Red, 44
3578,27
1
751
Улица-150 Красная, 48
3578,27
1
771
Mountain-100 Серебряный, 38
3399,99
4
772
Гора-100 Silver, 42
3399,99
4
775
Mountain-100 Black, 38
3374,99
6
...
...
...
...
Завершение
Предложение OVER можно использовать для определения секций или группирования в данных. Например, следующий запрос расширяет предыдущий пример для вычисления ранжирования на основе цен для продуктов в каждой категории.
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;
Результаты этого запроса могут выглядеть примерно так:
Категория
Продукт
ПрейскурантнаяЦена
РанжироватьПоЦене
Велошорты
Мужские биб-шорты, S
89.99
1
Шорты с лямками
Мужские биб-шорты, M
89.99
1
Багажники для велосипедов
Крепление для велосипедов на 4 велосипеда
120
1
Велосипедные стойки
All-Purpose стойка для велосипеда
159
1
Бутылки и клетки
Держатель для бутылок на горный велосипед
9.99
1
Бутылки и клетки
Держатель для бутылок для дорожного велосипеда
8.99
2
Бутылки и клетки
Бутылка воды - 30 унций.
4,99
3
Нижние скобки
Нижняя скобка HL
121.49
1
Нижние скобки
Каретка ML
101.24
2
Нижние скобки
Нижняя скобка LL
53.99
3
...
...
...
...
Замечание
Обратите внимание, что несколько строк имеют одно и то же значение ранжирования, а некоторые значения пропускаются. Это связано с тем, что мы используем только РАНГ. В зависимости от требования может потребоваться избежать связей с одинаковым значением ранжирования. При необходимости можно управлять ранжированием с помощью других функций, таких как DENSE_RANK, NTILE и ROW_NUMBER. Дополнительные сведения об этих функциях см. в справочной документации поTransact-SQL.
Функции набора строк
Функции набора строк возвращают виртуальную таблицу, которую можно использовать в предложении FROM в качестве источника данных. Эти функции принимают параметры, относящиеся к самой функции набора строк. К ним относятся OPENDATASOURCE, OPENQUERY, OPENROWSET, OPENXML и OPENJSON.
Функции OPENDATASOURCE, OPENQUERY и OPENROWSET позволяют передавать запрос на удаленный сервер базы данных. Затем удаленный сервер вернет набор результирующих строк. Например, следующий запрос использует OPENROWSET для получения результатов запроса из экземпляра SQL Server с именем SalesDB.
SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=SalesDB;Trusted_Connection=yes;',
'SELECT Name, ListPrice
FROM AdventureWorks.Production.Product') AS a;
Чтобы использовать удаленные серверы, необходимо включить некоторые дополнительные параметры в экземпляре SQL Server, где выполняется запрос.
Функции OPENXML и OPENJSON позволяют запрашивать структурированные данные в формате XML или JSON и извлекать значения в табличный набор строк.
Подробное изучение функций набора строк выходит за рамки этого модуля. Дополнительные сведения см. в справочной документации поTransact-SQL.