TSQL – Over, Ранжирующие функции и нововведения в SQL Server 2012
Предложение Over поддерживается
с SQL Sever 2005. Давайте
разберемся, какие выгоды мы можем извлечь.
В тестовой базе данных создадим таблицу OrderDetails и
заполним ее данными:
CREATE
TABLE OrderDetails(
OrderId int,
ProductName
nvarchar(50),
Quantity int,
Price int);
GO
INSERT
INTO OrderDetails
VALUES (1,'Sugar',10,10);
INSERT
INTO OrderDetails
VALUES (1,'Tea',1,25);
INSERT
INTO OrderDetails
VALUES (1,'Coffee',3,40);
INSERT
INTO OrderDetails
VALUES (2,'Coffee',1,50);
INSERT
INTO OrderDetails
VALUES (3,'Tea',100,19);
INSERT
INTO OrderDetails
VALUES (3,'Coffee',100,29);
Посмотрим на наши записи в таблице:
SELECT * FROM OrderDetails
Предположим, требуется
узнать общее количество товаров в каждом заказе и итоговую стоимость каждого
заказа:
SELECT
OrderId,
SUM(Quantity) AS OrderQuantity,
SUM(Quantity * Price) AS OrderPice
FROM
OrderDetails
GROUP
BY OrderId;
Далее, нужно
совместить эти запросы, что бы одновременно посмотреть все записи в OrderDetails, знать общее
количество товаров в каждом заказе и итоговую стоимость каждого заказа.
В SQL Server практически
всегда можно пойти несколькими путями, к примеру, может возникнуть желание использовать
подзапросы:
SELECT
*,
(SELECT SUM(Quantity)
FROM
OrderDetails
Where
OrderId = ord.OrderId) as OrderQuantity,
(SELECT SUM(Quantity * Price)
FROM
OrderDetails
Where
OrderId = ord.OrderId) as OrderPrice
FROM
OrderDetails as
ord
Или Join:
SELECT
OrderId,
ProductName,
Quantity,Price,
OrderQuantity,
OrderPrice
FROM
OrderDetails
JOIN ( SELECT OrderId
as Id,
SUM(Quantity) AS OrderQuantity,
SUM(Quantity * Price) AS OrderPrice
FROM OrderDetails
GROUP BY OrderId) AS ord
ON OrderId = ord.Id
Результат в обоих случаях будет:
Конечно, Join по
производительности предпочтительнее (IO статистика показывает всего 4
сканирования таблицы), в отличие от подзапросов (13 сканирований).
Теперь добьемся такого же результата, используя
предложение Over:
SELECT
*,
SUM(Quantity) OVER (PARTITION BY OrderId) AS TotalQuantity,
SUM(Quantity * Price) OVER (PARTITION BY OrderId) AS TotalPrice
FROM OrderDetails
Первое что
бросается в глаза - объем кода меньше. По
производительности конструкция JOIN и OVER окажется одинаковой, если посмотреть на план запроса, то и
он окажется практически идентичным. Но это только в конкретном случае, так как SQL Server ищет
наиболее лучший способ для решения поставленной задачи и конструкция OVER дает
больше свободы, чем жесткий JOIN.
Что же собственно
мы просим от SQL Server,
когда используем предложение OVER:
1. Выполни
запрос без OVER и получи основной результат.
2. Сгруппируй
основной результат, как указано в PARTITION BY.
3. Примени
к получившимся группам функцию (функция может быть агрегатной, аналитической,
ранжирующей, или NEXT VALUE FOR).
4. Сопоставь
результаты функции с основным результатом запроса
Естественно,
происходит все совсем не так, SQL Server самостоятельно находит каким образом ему лучше выполнить
запрос, но зная эту последовательность, вы всегда сможете предсказать, как
будет выглядеть результат запроса.
Можно группировать и без условий, используйте Over() и в группу войдут все
строки.
SELECT
*,
SUM(Quantity) OVER (PARTITION BY OrderId) AS OrderQuantity,
SUM(Quantity * Price) OVER (PARTITION BY OrderId) AS OrderPrice,
SUM(Quantity * Price) OVER () AllOrdersPrice
FROM
OrderDetails
Далее нам потребуется ввести понятие окна. Лучше всего на
примере, как думаете, сколько окон было в предыдущем результате? - Целых семь.
Каждое окно
выделено красным прямоугольником, розовым цветом обозначены ячейки, находящиеся
в столбцах указанных в PARTITION BY (благодаря им и происходит деление на окна), желтым –
результат функции для окна.
Выполним запрос:
SELECT *,
SUM(Quantity) OVER
() TotalQuantity
FROM
OrderDetails
В дополнение к основному запросу
будет созданное единственное окно, а результат функции это количество товаров
во всех заказах.
Но если слегка изменить запрос и в конструкцию OVER добавить
предложение ORDER BY:
SELECT
*,
SUM(Quantity) OVER (ORDER BY OrderId) OverQuantity
FROM OrderDetails
То результат изменится:
Заметьте – мы не использовали предложение PARTITION BY, то есть наше выражение не
делит результат запроса на окна, это до сих пор одно единственное окно.
Чтобы получить
такие же результаты на бумаге, нам потребуется последовательно перебрать все
строчки в окне и к каждой строке применить формулу расчета общего количества
товаров к заказам, у которых OrderId менее(ASC)
или равен текущему значению в строке.
Рассчитаем результаты для запроса:
SELECT *,
SUM(Quantity) OVER
(ORDER BY OrderId DESC) OverQuantity
FROM OrderDetails
ORDER BY OrderId
Тут используется DESC сортировка (от большего к меньшему),
по этому результаты будут отличны от предыдущего запроса.
Основной запрос:
SELECT
* FROM OrderDetails ORDER BY OrderId
Использовалось
предложение OVER без PARTITION,
так что окно будет единственным и в него войдут все строки результата.
Сортировка ORDER BY использовалось
с параметром DESC (от
большего к меньшему) поэтому рассчитывать нужно от шестой строчки результата к
первой.
Шестая и пятая
строчки – сумма всех товаров для строк, у которых значение OrderId более(DESC) или равно 3.
Четвертая
строчка – более или равно 2.
Третья, вторая и
первая – более или равно 1.
SQL Server 2012 может еще больше расширить предложение ORDER BY, с помощью ROWS и RANGE предложений.
ROWS означает строки.
RANGE
диапазон.
Вернемся к запросу:
SELECT
*,
SUM(Quantity) OVER (ORDER BY OrderId) OverQuantity
FROM OrderDetails
Так как мы
использовали OVER(), то
здесь всего одно окно, но в окне используется сортировка по столбцу OrderId. Если рассматривать
это окно с позиции ROWS то в нем 6 строк, а с позиции RANGE – 3
диапазона, основанных на уникальных значениях OrderId.
Окно на рисунке
выделено красным прямоугольником, диапазоны голубыми прямоугольниками.
SQL Server 2012 дает нам
возможность составлять условные предложения ROWS и RANGE в которых можно использовать следующие ограничения:
CURRENT ROW
Указывает на текущую строку или диапазон:
SELECT
*,
SUM(Quantity) OVER (ORDER BY OrderId ROWS CURRENT ROW) OverQuantity
FROM OrderDetails
SELECT
*,
SUM(Quantity) OVER (ORDER BY OrderId RANGE CURRENT ROW) OverQuantity
FROM OrderDetails
UNBOUNDED PRECEDING
Указывает, что надо учитывать все строки/диапазоны с
первого и по текущий (хорошо подходит для составления промежуточных
результатов).
SELECT
*,
SUM(Quantity) OVER (ORDER BY OrderId ROWS UNBOUNDED PRECEDING) OverQuantity
FROM OrderDetails
SELECT
*,
SUM(Quantity) OVER (ORDER BY OrderId RANGE UNBOUNDED PRECEDING) OverQuantity
FROM OrderDetails
UNBOUNDED FOLLOWING
Указывает, что надо учитывать все строки/диапазоны с
текущего и по последний. Может быть указанным только в предложении BETWEEN как
конечная точка.
SELECT
*,
SUM(Quantity) OVER (ORDER BY OrderId, ProductName ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) OverQuantity
FROM
OrderDetails
ORDER
BY OrderId, ProductName
SELECT
*,
SUM(Quantity) OVER (ORDER BY OrderId RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) OverQuantity
FROM OrderDetails
ORDER BY OrderId
<Integer>
PRECEDING
Указывает, что нужно учитывать текущую строку и <Integer> строк до нее. Не
допускается в предложении RANGE.
SELECT
*,
SUM(Quantity) OVER (ORDER BY OrderId ROWS 1 PRECEDING) OverQuantity
FROM OrderDetails
<Integer>
FOLLOWING
Указывает, что нужно учитывать диапазон <Integer> строк после
текущей строчки. Может быть использовано только в предложении BETWEEN. Не допускается в предложении RANGE.
SELECT *,
SUM(Quantity) OVER
(ORDER BY OrderId ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING) OverQuantity
FROM OrderDetails
ORDER BY OrderId
BETTWEEN
Используется для указания границ.
Ранжирующие функции
С появлением OVER в
SQL Server 2005 так же появились
ранжирующие функции, все ранжирующие функции используются с конструкцией ORDER BY.
ROW_NUМBER()
Задает каждой
строчке окна уникальный, последовательный номер, начиная с единицы.
SELECT
*,
ROW_NUMBER() OVER (ORDER BY OrderId) ROW_NUМBER_SingleWindow,
ROW_NUMBER() OVER (PARTITION BY OrderId ORDER BY OrderId) ROW_NUМBER_ManyWindows
FROM OrderDetails
ORDER BY OrderId
RANK()
Возвращает ранг
каждой строки в окне. Ранг для каждого уникального значения столбца или
столбцов указанных в ORDER BY вычисляется лишь единожды, при первом нахождении оного. По
формуле единица плюс количество строк до строки от начала окна.
SELECT
*,
RANK() OVER (ORDER BY OrderId) RANK_Many_OrderId_Values_In_Window,
RANK() OVER (PARTITION BY OrderId ORDER BY OrderId) RANK_Single_OrderId_In_Window
FROM OrderDetails
ORDER BY OrderId
DENSE_RANK()
Возвращает ранг
строк в окне без прыжков через значения. Ранг строки равен количеству
уникальных значений указанных в ODER BY,
предшествующих строке, увеличенному на единицу.
SELECT
*,
DENSE_RANK() OVER (ORDER BY OrderId) Many_OrderId_Values_In_Window,
DENSE_RANK() OVER (PARTITION BY OrderId ORDER BY OrderId) Single_OrderId_In_Window
FROM OrderDetails
ORDER BY OrderId
NTILE(<Integer>)
Распределяет
строки в окне на заданное количество групп. Группы нумеруются, начиная с
единицы. Для каждой строки функция NTILE возвращает номер группы, которой принадлежит строка.
SELECT
*,
NTILE(3) OVER (ORDER BY OrderId) NTILE_One_Window,
NTILE(3) OVER (PARTITION BY OrderId ORDER BY OrderId) NTILE_Many_Windows
FROM OrderDetails
ORDER BY OrderId
На этом все, удачи вам и спасибо что прочитали мою статью. Буду рад оставленным комментариям и новым постоянным читателям.
Александр Кобелев.
