Предложение LAG (Transact-SQL)

Обращается к данным из предыдущей строки того же результирующего набора без использования самосоединения в SQL Server 2012. Функция LAG обеспечивает доступ к строке с заданным физическим смещением перед началом текущей строки. Используйте данную аналитическую функцию в инструкции SELECT для сравнения значений текущей строки со значениями из предыдущей.

Синтаксические обозначения в Transact-SQL (Transact-SQL)

Синтаксис

LAG (scalar_expression [,offset] [,default])
    OVER ( [ partition_by_clause ] order_by_clause )

Аргументы

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

• offset
  Количество строк до строки перед текущей строкой, из которой необходимо получить значение. Если значение аргумента не указано, то по умолчанию принимается 1. offset может быть столбцом, вложенным запросом или другим выражением, с помощью которого вычисляется целая положительная величина, или другим типом, который может быть неявно преобразован в bigint. offset не может быть отрицательным значением или аналитической функцией.

• default
  Значение, возвращаемое, если scalar_expression по смещению offset равно NULL. Если значение по умолчанию не задано, то возвращается NULL. default может быть столбцом, вложенным запросом или другим выражением, но не может быть аналитической функцией. Значение default должно быть совместимо по типу с scalar_expression.

• OVER ( [ partition_by_clause ] order_by_clause**)**
  partition_by_clause делит результирующий набор, полученный с помощью предложения FROM, на секции, к которым применяется функция. Если этот параметр не указан, функция обрабатывает все строки результирующего набора запроса как отдельные группы. order_by_clause определяет порядок данных перед применением функции. Если partition_by_clause задан, он определяет порядок данных в секции. Атрибут order_by_clause является обязательным. Дополнительные сведения см. в разделе Предложение OVER (Transact-SQL).

Типы возвращаемых данных

Тип данных указанного выражения scalar_expression. Если параметр scalar_expression допускает значение NULL или параметр default установлен равным NULL, то возвращается NULL.

Примеры

А.Сравнение значений по годам

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

USE AdventureWorks2012;
GO
SELECT BusinessEntityID, YEAR(QuotaDate) AS SalesYear, SalesQuota AS CurrentQuota, 
       LAG(SalesQuota, 1,0) OVER (ORDER BY YEAR(QuotaDate)) AS PreviousQuota
FROM Sales.SalesPersonQuotaHistory
WHERE BusinessEntityID = 275 and YEAR(QuotaDate) IN ('2005','2006');

Ниже приводится результирующий набор.

BusinessEntityID SalesYear   CurrentQuota          PreviousQuota
---------------- ----------- --------------------- ---------------------
275              2005        367000.00             0.00
275              2005        556000.00             367000.00
275              2006        502000.00             556000.00
275              2006        550000.00             502000.00
275              2006        1429000.00            550000.00
275              2006        1324000.00            1429000.00

Б.Сравнение значений внутри секций

В следующем примере используется функция LAG для сравнения количества продаж за текущий год между сотрудниками. Предложение PARTITION BY указывается для разделения строк результирующего набора по территориям продаж. Функция LAG применяется к каждой секции отдельно, а вычисление начинается заново для каждой секции. Предложение ORDER BY в предложении OVER упорядочивает строки в каждой секции. Предложение ORDER BY в инструкции SELECT сортирует строки во всём результирующем наборе. Обратите внимание, что для первой строки возвращается значение, установленное по умолчанию, т. е. нуль (0), так как значение за предыдущий период для первой строки каждой секции недоступно.

USE AdventureWorks2012;
GO
SELECT TerritoryName, BusinessEntityID, SalesYTD, 
       LAG (SalesYTD, 1, 0) OVER (PARTITION BY TerritoryName ORDER BY SalesYTD DESC) AS PrevRepSales
FROM Sales.vSalesPerson
WHERE TerritoryName IN (N'Northwest', N'Canada') 
ORDER BY TerritoryName;

Ниже приводится результирующий набор.

TerritoryName            BusinessEntityID SalesYTD              PrevRepSales
-----------------------  ---------------- --------------------- ---------------------
Canada                   282              2604540.7172          0.00
Canada                   278              1453719.4653          2604540.7172
Northwest                284              1576562.1966          0.00
Northwest                283              1573012.9383          1576562.1966
Northwest                280              1352577.1325          1573012.9383

В.Указание произвольных выражений

В следующем примере демонстрируется указание ряда произвольных выражений в синтаксисе функции LAG.

CREATE TABLE T (a int, b int, c int); 
GO
INSERT INTO T VALUES (1, 1, -3), (2, 2, 4), (3, 1, NULL), (4, 3, 1), (5, 2, NULL), (6, 1, 5); 

SELECT b, c, 
    LAG(2*c, b*(SELECT MIN(b) FROM T), -c/2.0) OVER (ORDER BY a) AS i
FROM T;

Ниже приводится результирующий набор.

b           c           i
----------- ----------- -----------
1           -3          1
2           4           -2
1           NULL        8
3           1           -6
2           NULL        NULL
1           5           NULL

См. также

Справочник

Предложение LEAD (Transact-SQL)