Предложение LAG (Transact-SQL)
Область применения:
SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure конечной точке аналитики платформы Аналитики Azure Synapse Analytics (PDW) в Microsoft Fabric Хранилище в Microsoft Fabric
Получает доступ к данным из предыдущей строки в том же результирующем наборе без использования самостоятельного соединения, начиная с SQL Server 2012 (11.x). Функция LAG обеспечивает доступ к строке с заданным физическим смещением перед началом текущей строки. Используйте данную аналитическую функцию в инструкции SELECT для сравнения значений текущей строки со значениями из предыдущей.
Соглашения о синтаксисе Transact-SQL
Синтаксис
LAG (scalar_expression [ , offset ] [ , default ] ) [ IGNORE NULLS | RESPECT NULLS ]
OVER ( [ partition_by_clause ] order_by_clause )
Аргументы
scalar_expression
Возвращаемое значение основано на указанном смещении. Это выражение любого типа, возвращающее единичное (скалярное) значение. scalar_expression не может быть аналитической функцией.
offset
Количество строк до строки перед текущей строкой, из которой необходимо получить значение. Если значение аргумента не указано, то по умолчанию принимается 1. offset может быть столбцом, вложенным запросом или другим выражением, с помощью которого вычисляется целая положительная величина, или другим типом, который может быть неявно преобразован в bigint. offset не может быть отрицательным значением или аналитической функцией.
default
Возвращаемое значение, когда offset находится за пределами секции. Если значение по умолчанию не задано, то возвращается NULL. default может быть столбцом, вложенным запросом или другим выражением, но не может быть аналитической функцией. Аргумент default должен быть совместим по типу с аргументом scalar_expression.
[ IGNORE NULLS | RESPECT NULLS ]
Область применения: SQL Server (начиная с SQL Server 2022 (16.x)), База данных SQL Azure, Управляемый экземпляр SQL Azure, Azure SQL Edge
IGNORE NULLS — пропуск значений NULL в наборе данных при вычислении первого значения в секции.
RESPECT NULLS — учет значений NULL в наборе данных при вычислении первого значения в секции. RESPECT NULLS — это поведение по умолчанию, если параметр NULLS не указан.
Исправлена ошибка в SQL Server 2022 CU4, связанная с пропуском значений NULLS и LAG LEAD.
Дополнительные сведения об этом аргументе в Azure SQL Edge см. в разделе "Очиска отсутствующих значений".
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. Значение NULL возвращается в случае, если аргумент scalar_expression может принимать значение NULL или аргумент default имеет значение NULL.
Общие замечания
Функция LAG не детерминирована. Дополнительные сведения см. в разделе Deterministic and Nondeterministic Functions.
Примеры
А. Сравнение значений по годам
В следующем примере показано использование функции LAG для возврата разности в квотах продаж для указанного работника за предыдущие годы. Обратите внимание, что для первой строки возвращается значение, установленное по умолчанию, т. е. нуль (0), так как значение за предыдущие периоды для нее недоступно.
USE AdventureWorks2022;
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
B. Сравнение значений внутри секций
В следующем примере используется функция LAG для сравнения количества продаж за текущий год между сотрудниками. Предложение PARTITION BY указывается для разделения строк результирующего набора по территориям продаж. Функция LAG применяется к каждой секции отдельно, а вычисление начинается заново для каждой секции. Предложение ORDER BY в предложении OVER упорядочивает строки в каждой секции. Предложение ORDER BY в инструкции SELECT сортирует сроки во всем результирующем наборе. Обратите внимание, что для первой строки возвращается значение, установленное по умолчанию, т. е. нуль (0), так как значение за предыдущий период для первой строки каждой секции недоступно.
USE AdventureWorks2022;
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
C. Указание произвольных выражений
В следующем примере показано указание различных произвольных выражений и пропуска значений NULL в синтаксисе функции 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) IGNORE NULLS 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 8
1 5 2
D. Использование IGNORE NULLS для поиска значений, отличных от NULL
В следующем примере запроса показано использование аргумента IGNORE NULLS.
Аргумент IGNORE NULLS используется вместе с LAG и LEAD для демонстрации подстановки значений NULL для предыдущих или следующих значений, отличных от NULL.
- Если предыдущая строка содержит значение NULL, текущая строка использует последнее значение, отличное от NULL
LAG. - Если следующая строка содержит значение NULL, текущая строка использует следующее доступное значение, отличное от NULL
LEAD.
DROP TABLE IF EXISTS #test_ignore_nulls;
CREATE TABLE #test_ignore_nulls (column_a int, column_b int);
GO
INSERT INTO #test_ignore_nulls VALUES
(1, 8),
(2, 9),
(3, NULL),
(4, 10),
(5, NULL),
(6, NULL),
(7, 11);
SELECT column_a, column_b,
[Previous value for column_b] = LAG(column_b) IGNORE NULLS OVER (ORDER BY column_a),
[Next value for column_b] = LEAD(column_b) IGNORE NULLS OVER (ORDER BY column_a)
FROM #test_ignore_nulls
ORDER BY column_a;
--cleanup
DROP TABLE #test_ignore_nulls;
column_a column_b Previous value for column_b Next value for column_b
1 8 NULL 9
2 9 8 10
3 NULL 9 10
4 10 9 11
5 NULL 10 11
6 NULL 10 11
7 11 10 NULL
Е. Использование RESPECT NULLS для хранения значений NULL
Следующий пример запроса демонстрирует использование аргумента RESPECT NULLS, который является поведением по умолчанию, если оно не указано, в отличие от аргумента IGNORE NULLS в предыдущем примере.
- Если предыдущая строка содержит значение NULL
LAG, текущая строка использует последнее значение. - Если следующая строка содержит значение NULL,
LEADтекущая строка использует следующее значение.
DROP TABLE IF EXISTS #test_ignore_nulls;
CREATE TABLE #test_ignore_nulls (column_a int, column_b int);
GO
INSERT INTO #test_ignore_nulls VALUES
(1, 8),
(2, 9),
(3, NULL),
(4, 10),
(5, NULL),
(6, NULL),
(7, 11);
SELECT column_a, column_b,
[Previous value for column_b] = LAG(column_b) RESPECT NULLS OVER (ORDER BY column_a),
[Next value for column_b] = LEAD(column_b) RESPECT NULLS OVER (ORDER BY column_a)
FROM #test_ignore_nulls
ORDER BY column_a;
--Identical output
SELECT column_a, column_b,
[Previous value for column_b] = LAG(column_b) OVER (ORDER BY column_a),
[Next value for column_b] = LEAD(column_b) OVER (ORDER BY column_a)
FROM #test_ignore_nulls
ORDER BY column_a;
--cleanup
DROP TABLE #test_ignore_nulls;
column_a column_b Previous value for column_b Next value for column_b
1 8 NULL 9
2 9 8 NULL
3 NULL 9 10
4 10 NULL NULL
5 NULL 10 NULL
6 NULL NULL 11
7 11 NULL NULL
Примеры: Azure Synapse Analytics и система платформы аналитики (PDW)
А. Сравнение значений по кварталам
В приведенном ниже примере показано использование функции LAG. В запросе функция LAG применяется для возврата разности в квотах продаж для указанного работника за предыдущие кварталы. Обратите внимание, что для первой строки возвращается значение, установленное по умолчанию, т. е. нуль (0), так как значение за предыдущие периоды для нее недоступно.
-- Uses AdventureWorks
SELECT CalendarYear, CalendarQuarter, SalesAmountQuota AS SalesQuota,
LAG(SalesAmountQuota,1,0) OVER (ORDER BY CalendarYear, CalendarQuarter) AS PrevQuota,
SalesAmountQuota - LAG(SalesAmountQuota,1,0) OVER (ORDER BY CalendarYear, CalendarQuarter) AS Diff
FROM dbo.FactSalesQuota
WHERE EmployeeKey = 272 AND CalendarYear IN (2001, 2002)
ORDER BY CalendarYear, CalendarQuarter;
Вот результирующий набор.
Year Quarter SalesQuota PrevQuota Diff
---- ------- ---------- --------- -------------
2001 3 28000.0000 0.0000 28000.0000
2001 4 7000.0000 28000.0000 -21000.0000
2001 1 91000.0000 7000.0000 84000.0000
2002 2 140000.0000 91000.0000 49000.0000
2002 3 7000.0000 140000.0000 -70000.0000
2002 4 154000.0000 7000.0000 84000.0000