Уютное сообщество C# разработчиков!

Функции смещения T-SQL

107

Работа с базами данных в .NET Framework --- Оконные функции T-SQL --- Функции смещения

Исходник базы данных

Оконные функции смещения делятся на две категории. Первая категория - функции, смещение которых указывается по отношению к текущей строке. Это LAG и LEAD. В функциях второй категории смещение указывается по отношению к началу или концу оконного кадра. Сюда относятся функции FIRST_VALUE, LAST_VALUE и NTH_VALUE. SQL Server 2012 поддерживает LAG, LEAD, FIRST_VALUE и LAST_VALUE и не поддерживает NTH_VALUE.

Функции первой категории (LAG и LEAD) поддерживают предложение секционирования, а также упорядочения окна. Ясно, что вторая часть вносит смысл в смещение. Функции из второй категории (FIRST_VALUE, LAST_VALUE и NTH_VALUE) помимо предложения секционирования и упорядочения окна поддерживают предложение оконного кадра.

Функции LAG и LEAD

Функции LAG и LEAD позволяют возвращать выражение значения из строки в секции окна, которая находится на заданном смещении перед (LAG) или после (LEAD) текущей строки. Смещение по умолчанию — «1», оно применяется, если смещение не указать.<,/

Например, следующий запрос возвращает текущую стоимость для каждого клиентского заказа, а также стоимости предыдущего и последующего заказов этого же клиента: 

SELECT custid, orderdate, orderid, val,
  LAG(val)  OVER(PARTITION BY custid
                 ORDER BY orderdate, orderid) AS prevval,
  LEAD(val) OVER(PARTITION BY custid
                 ORDER BY orderdate, orderid) AS nextval
FROM Sales.OrderValues;
Результат запроса с использованием функций LAG и LEAD

Так как мы явно не задали смещение, по умолчанию предполагается смещение в единицу. Так как данные в функции секционируются по custid, поиск строк выполняется только в рамках той же секции, содержащей данные одного клиента. Что касается упорядочения окон, то понятия «предыдущий» и «следующий» определяются упорядочением по orderdate и orderid в качестве дополнительного параметра. Заметьте, что в результатах запроса LAG возвращает NULL для первой строки оконной секции, потому что перед первой строкой других строк нет; аналогично LEAD возвращает NULL для последней строки.

Если нужно смещение, отличное от единицы, нужно указать его после входного выражения значения, как в этом запросе: 

SELECT custid, orderdate, orderid,
  LAG(val, 3) OVER(PARTITION BY custid
                   ORDER BY orderdate, orderid) AS prev3val
FROM Sales.OrderValues;
Результат запроса LAG со смещением

Как говорилось, LAG и LEAD по умолчанию возвращают NULL, если по заданному смещению нет строки. Если нужно возвращать другое значение, можно указать его в качестве третьего аргумента функции. Например, LAG(val, 3, 0.00) возвращает «0.00», если по смещению 3 перед текущей строкой строки вообще нет.

Для реализации подобного поведения в LAG и LEAD на версии SQL Server, предшествующей SQL Server 2012, можно применить следующий подход:

  1. Напишите запрос, который возвращает номера строк с требуемыми параметрами секционирования и упорядочения, и создайте на его основе табличное выражение.

  2. Соедините множественные табличные выражения так, чтобы они представляли текущую, предыдущую и следующую строки.

  3. В предикате соединения сопоставьте столбцы секционирования различных экземпляров (текущего с предыдущим или последующим). Также в предикате соединения вычислите разницу между числом строк текущего и предыдущего или следующего экземпляра, а затем отфильтруйте на основе значения смещения, которое требуется в ваших вычислениях.

Вот запрос, реализующий этот подход и возвращающий для каждого заказа значения текущего, предыдущего и следующего заказа клиента: 


WITH OrdersRN AS
(
  SELECT custid, orderdate, orderid, val,
    ROW_NUMBER() OVER(ORDER BY custid, orderdate, orderid) AS rn
  FROM Sales.OrderValues
)
SELECT C.custid, C.orderdate, C.orderid, C.val,
  P.val AS prevval,
  N.val AS nextval
FROM OrdersRN AS C
  LEFT OUTER JOIN OrdersRN AS P
    ON C.custid = P.custid
    AND C.rn = P.rn + 1
  LEFT OUTER JOIN OrdersRN AS N
    ON C.custid = N.custid
    AND C.rn = N.rn - 1;

Ясно, что решить эту задачу можно также с помощью простых вложенных запросов.

Функции FIRST_VALUE, LAST_VALUE и NTH_VALUE

В предыдущем разделе я рассказал о функциях смещения LAG и LEAD, которые позволяют задавать смещение относительно текущей строки. Этот раздел посвящен функциям, которые позволяют определять смещение относительно начала или конца оконного кадра. Это функции FIRST_VALUE, LAST_VALUE и NTH_VALUE, причем последняя не реализована в SQL Server 2012.

Напомню, что LAG и LEAD поддерживают предложения секционирования и упорядочение, но не поддерживают предложение кадрирования окна. Это разумно, если смещение указывается относительно текущей строки. В функциях, в которых смещение указывается по отношению к началу или концу окна, кадрирование имеет смысл. Функции FIRST_VALUE и LAST_VALUE возвращают запрошенное выражение значения соответственно из первой и последней строки в кадре. Вот запрос, демонстрирующий, как возвращать с каждым заказом клиента текущее значение этого заказа, а также значения первого и последнего заказа: 

SELECT custid, orderdate, orderid, val,
  FIRST_VALUE(val) OVER(PARTITION BY custid
                        ORDER BY orderdate, orderid) AS val_firstorder,
  LAST_VALUE(val)  OVER(PARTITION BY custid
                        ORDER BY orderdate, orderid
                        ROWS BETWEEN CURRENT ROW
                                 AND UNBOUNDED FOLLOWING) AS val_lastorder
FROM Sales.OrderValues;
Результат запроса с использованием FIRST_VALUE и LAST_VALUE

С технической точки зрения нам нужны значения из первой и последней строки секции. С FIRST_VALUE просто, потому что можно использовать кадрирование по умолчанию. Как вы помните, если поддерживается кадрирование и не указать предложение кадрирования окна, по умолчанию будет применяться RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Но с LAST_VALUE кадрирование по умолчанию бесполезно, потому что последней является текущая строка. Поэтому в этом примере используется явное определение кадра с UNBOUNDED FOLLOWING в качестве нижней границы кадра.

Обычно не возвращают первое или последнее значение вместе со всеми подробностями строк, как в предыдущем примере — в вычислениях обычно работают с одной цифрой и значением, возвращенным оконной функцией. В следующем примере запрос возвращает, вместе с каждым клиентским заказом, стоимость текущего заказа, а также разницу между ней и стоимостью первого и последнего заказа клиента: 

SELECT custid, orderdate, orderid, val,
  val - FIRST_VALUE(val) OVER(PARTITION BY custid
                              ORDER BY orderdate, orderid) AS difffirst,
  val - LAST_VALUE(val)  OVER(PARTITION BY custid
                              ORDER BY orderdate, orderid
                              ROWS BETWEEN CURRENT ROW
                                       AND UNBOUNDED FOLLOWING) AS difflast
FROM Sales.OrderValues;
Результат запроса с использованием функций смещения

Как я говорил, стандартная функция NTH_VALUE не реализована в SQL Server 2012. Эта функция позволяет запрашивать выражение значения, которое находится на заданном смещении, выраженном в числе строк, от первой или последней строки в оконном кадре. Смещение задается во втором входном значении после выражения значения и ключевого слова FROM_FIRST или FROM_LAST, которое указывает, от какой строки отсчитывать смещение — от первой или последней. Например, следующее выражение возвращает значение из третьей строки, если считать от самой нижней в секции: 

NTH_VALUE(val, 3) FROM LAST OVER(ROWS BETWEEN CURRENT ROW
    AND UNBOUNDED FOLLOWING)

Представим, что нам надо обеспечить функциональность, которую реализуют функции FIRST_VALUE, LAST_VALUE и NTH_VALUE, в версии, предшествующей SQL Server 2012. Для этого можно использовать такие конструкции, как обобщенные табличные выражения (CTE), функцию ROW_NUMBER и выражение CASE, группировку и соединение, следующим образом: 

WITH OrdersRN AS
(
  SELECT custid, val,
    ROW_NUMBER() OVER(PARTITION BY custid
                      ORDER BY orderdate, orderid) AS rna,
    ROW_NUMBER() OVER(PARTITION BY custid
                      ORDER BY orderdate DESC, orderid DESC) AS rnd
  FROM Sales.OrderValues
),
Agg AS
(
  SELECT custid,
    MAX(CASE WHEN rna = 1 THEN val END) AS firstorderval,
    MAX(CASE WHEN rnd = 1 THEN val END) AS lastorderval,
    MAX(CASE WHEN rna = 3 THEN val END) AS thirdorderval
  FROM OrdersRN
  GROUP BY custid
)
SELECT O.custid, O.orderdate, O.orderid, O.val,
  A.firstorderval, A.lastorderval, A.thirdorderval
FROM Sales.OrderValues AS O
  JOIN Agg AS A
    ON O.custid = A.custid
ORDER BY custid, orderdate, orderid;
Результат запроса с использованием нескольких функций смещения

В первом CTE по имени OrdersRN определяются номера строк как возрастающем, так и убывающем порядке для отметки позиций строк по отношению к первой и последней строке в секции. Во втором CTE по имени Agg используется выражение CASE для фильтрации только нужных номеров строк, группировки данных по элементу секционирования {custid) и применения агрегата к результату выражения CASE, чтобы вернуть запрошенное значение для каждой группы. Наконец во внешнем запросе результат группового запроса соединяется с исходной таблицей для сопоставления детализованной информации с агрегатами.

Лучший чат для C# программистов