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

Статья
05/22/2024

Область применения: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure конечной точке аналитики платформы Аналитики Azure Synapse Analytics (PDW) в Microsoft Fabric Хранилище в Microsoft Fabric

Получает доступ к данным из последующей строки в том же результирующем наборе без использования самостоятельного соединения, начиная с SQL Server 2012 (11.x). LEAD предоставляет доступ к строке с заданным физическим смещением, которое следует текущей строке. Используйте эту функцию аналитики в инструкции для сравнения значений в текущей SELECT строке со значениями в следующей строке.

Соглашения о синтаксисе Transact-SQL

Синтаксис

LEAD ( scalar_expression [ , offset ] [ , default ] ) [ IGNORE NULLS | RESPECT NULLS ]
    OVER ( [ partition_by_clause ] order_by_clause )

Аргументы

scalar_expression

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

offset

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

default

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

[ IGNORE NULLS | RESPECT NULLS ]

Область применения: 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, связанной с IGNORE 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 является обязательным. Дополнительные сведения см. в предложении SELECT — OVER.

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

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

Функция LEAD не детерминирована. Дополнительные сведения см. в разделе Deterministic and Nondeterministic Functions.

Примеры

Примеры кода Transact-SQL в этой статье используют AdventureWorks2022 базу данных или AdventureWorksDW2022 пример базы данных, которую можно скачать с домашней страницы примеров и проектов сообщества Microsoft SQL Server.

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

Запрос использует LEAD функцию для возврата разницы в квотах продаж для конкретного сотрудника в течение последующих лет. Так как для последней строки нет значения свинца, возвращается значение по умолчанию 0.

USE AdventureWorks2022;
GO

SELECT BusinessEntityID,
    YEAR(QuotaDate) AS SalesYear,
    SalesQuota AS CurrentQuota,
    LEAD(SalesQuota, 1, 0) OVER (ORDER BY YEAR(QuotaDate)) AS NextQuota
FROM Sales.SalesPersonQuotaHistory
WHERE BusinessEntityID = 275 AND YEAR(QuotaDate) IN ('2005', '2006');

Вот результирующий набор.

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

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

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

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

Вот результирующий набор.

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

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

В следующем примере показано, как указать различные произвольные выражения и игнорировать NULL значения в синтаксисе LEAD функции.

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,
    LEAD(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           5           -2
2           NULL        NULL
3           1           0
1           NULL        2
2           4           2
1           -3          8

D. Использование IGNORE NULLS для поиска значений, отличных от NULL

В следующем примере запроса показано использование аргумента IGNORE NULLS .

Аргумент IGNORE NULLS используется как с LAG, так и LEAD для демонстрации подстановки значений для предыдущих NULL или следующих значений, отличных от NULL.

Если указанная выше строка содержится NULL LAG, текущая строка использует последнее значение, отличноеNULL от значения.
Если следующая строка содержит с NULL LEAD, текущая строка использует следующее доступное значение, отличноеNULL от значения.

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)

А. Сравнение значений по кварталам

В следующем примере показана LEAD функция. Запрос возвращает разницу между значениями квот на продажу для указанного сотрудника в следующие друг за другом кварталы. Так как после последней строки нет значения свинца, используется значение по умолчанию 0.

-- Uses AdventureWorks

SELECT CalendarYear AS Year,
    CalendarQuarter AS Quarter,
    SalesAmountQuota AS SalesQuota,
    LEAD(SalesAmountQuota, 1, 0) OVER (ORDER BY CalendarYear, CalendarQuarter) AS NextQuota,
    SalesAmountQuota - LEAD(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  NextQuota  Diff
---- -------  ----------  ---------  -------------
2001 3        28000.0000   7000.0000   21000.0000
2001 4         7000.0000  91000.0000  -84000.0000
2001 1        91000.0000 140000.0000  -49000.0000
2002 2       140000.0000   7000.0000    7000.0000
2002 3         7000.0000 154000.0000   84000.0000
2002 4       154000.0000      0.0000  154000.0000

Поделиться через

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

Синтаксис

Аргументы

scalar_expression

offset

default

[ IGNORE NULLS | RESPECT NULLS ]

OVER ( [ partition_by_clause ] order_by_clause )

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

Примеры

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

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

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

D. Использование IGNORE NULLS для поиска значений, отличных от NULL

Е. Использование RESPECT NULLS для хранения `NULL` значений

Примеры: Azure Synapse Analytics и система платформы аналитики (PDW)

А. Сравнение значений по кварталам

Обратная связь

Дополнительные ресурсы

Поделиться через

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

Синтаксис

Аргументы

scalar_expression

offset

default

[ IGNORE NULLS | RESPECT NULLS ]

OVER ( [ partition_by_clause ] order_by_clause )

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

Примеры

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

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

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

D. Использование IGNORE NULLS для поиска значений, отличных от NULL

Е. Использование RESPECT NULLS для хранения NULL значений

Примеры: Azure Synapse Analytics и система платформы аналитики (PDW)

А. Сравнение значений по кварталам

Связанный контент

Обратная связь

Дополнительные ресурсы

Е. Использование RESPECT NULLS для хранения `NULL` значений