четверг, 28 марта 2013 г.

TSQL – Over, Ранжирующие функции и нововведения в SQL Server 2012



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
 

На этом все, удачи вам и спасибо что прочитали мою статью. Буду рад оставленным комментариям и новым постоянным читателям.


Александр Кобелев.