LEAD: примеры (SQL)

Основные примеры использования LEAD

Пример 1: Получение следующего значения по порядку.

SELECT 
  EmployeeID, 
  OrderDate, 
  TotalAmount,
  LEAD(TotalAmount) OVER (ORDER BY OrderDate) AS NextAmount
FROM SalesOrders;

EmployeeID  OrderDate   TotalAmount  NextAmount
----------  ----------  -----------  ----------
101         2023-01-10  1500.00      2000.00
101         2023-01-12  2000.00      1800.00
102         2023-01-15  1800.00      NULL

Пример 2: Использование смещения и значения по умолчанию.

SELECT 
  ProductID, 
  Price,
  LEAD(Price, 2, 0.00) OVER (ORDER BY ProductID) AS PriceAfterTwo
FROM Products;

ProductID  Price  PriceAfterTwo
---------  -----  --------------
1          10.00  14.00
2          12.00  15.00
3          14.00  0.00
4          15.00  0.00

Пример 3: Применение с разделением на секции.

SELECT 
  Department, 
  Employee, 
  Salary,
  LEAD(Salary) OVER (PARTITION BY Department ORDER BY Salary DESC) AS NextSalaryInDept
FROM Employees;

Department  Employee  Salary  NextSalaryInDept
----------  --------  ------  ----------------
IT          Ivan      5000    4500
IT          Petr      4500    4000
HR          Anna      3500    NULL

Похожие функции в MS SQL

• LAG: обращается к предыдущей строке. Применяется для сравнения с предшествующими данными, например, для расчета прироста.
• FIRST_VALUE и LAST_VALUE: получают первое и последнее значение в окне. Полезны для анализа границ набора.
• ROW_NUMBER, RANK, DENSE_RANK: ранжируют строки. Используются для нумерации или определения позиции.
• Функцию LEAD предпочтительнее выбирать для доступа к последующим строкам, а LAG — к предыдущим. FIRST_VALUE подходит для фиксации начального значения в разделе.

Распространенные ошибки

Ошибка 1: Отсутствие предложения ORDER BY в OVER.

SELECT ID, LEAD(Value) OVER () FROM TestTable; -- Ошибка

Сообщение об ошибке: Оконная функция 'LEAD' должна содержать предложение ORDER BY.

Ошибка 2: Использование отрицательного или нецелого значения offset.

SELECT ID, LEAD(Value, -1) OVER (ORDER BY ID) FROM TestTable; -- Ошибка

Сообщение: Смещение для оконной функции должно быть положительным целым числом.

Ошибка 3: Некорректное указание default, несовместимое по типу данных с expression.

SELECT ID, LEAD(NumericColumn, 1, 'text') OVER (ORDER BY ID) FROM TestTable; -- Ошибка приведения типов

История изменений функции

Функция LEAD была введена в Microsoft SQL Server начиная с версии 2012. С момента добавления синтаксис и поведение функции остаются стабильными. В последующих выпусках, таких как SQL Server 2016, 2019 и 2022, значительных изменений в работу функции не вносилось. Улучшения касались общей производительности оконных функций и оптимизации запросов.

Расширенные примеры применения LEAD

Пример 1: Расчет разницы между текущей и следующей датой.

SELECT 
  OrderID,
  CustomerID,
  OrderDate,
  LEAD(OrderDate) OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS NextOrderDate,
  DATEDIFF(day, OrderDate, LEAD(OrderDate) OVER (PARTITION BY CustomerID ORDER BY OrderDate)) AS DaysBetweenOrders
FROM Orders;

OrderID  CustomerID  OrderDate   NextOrderDate  DaysBetweenOrders
-------  ----------  ----------  -------------  -----------------
1        1001        2023-05-01  2023-05-10     9
2        1001        2023-05-10  NULL           NULL
3        1002        2023-05-02  2023-05-03     1

Пример 2: Использование в общем табличном выражении (CTE) для сложной логики.

WITH RankedSales AS (
  SELECT 
    Salesperson,
    SaleMonth,
    Revenue,
    LEAD(Revenue, 1, 0) OVER (PARTITION BY Salesperson ORDER BY SaleMonth) AS NextMonthRevenue
  FROM MonthlySales
)
SELECT 
  Salesperson,
  SaleMonth,
  Revenue,
  NextMonthRevenue,
  CASE WHEN NextMonthRevenue > Revenue THEN 'Increase' ELSE 'Decrease or Stable' END AS Trend
FROM RankedSales;

Пример 3: Поиск пропусков в последовательности идентификаторов.

SELECT 
  CurrentID = ID,
  NextID = LEAD(ID) OVER (ORDER BY ID),
  GapSize = LEAD(ID) OVER (ORDER BY ID) - ID - 1
FROM Items
WHERE LEAD(ID) OVER (ORDER BY ID) - ID > 1;

CurrentID  NextID  GapSize
---------  ------  -------
5          8       2
10         15      4

Пример 4: Комбинация с агрегатными функциями.

SELECT 
  Year,
  ProductCategory,
  TotalSales,
  LEAD(TotalSales) OVER (PARTITION BY ProductCategory ORDER BY Year) AS NextYearSales,
  (LEAD(TotalSales) OVER (PARTITION BY ProductCategory ORDER BY Year) - TotalSales) * 100.0 / TotalSales AS PercentChange
FROM AnnualCategorySales;

Аналоги функции в других СУБД

PostgreSQL и Oracle: поддерживают идентичный синтаксис LEAD. Пример для PostgreSQL:

SELECT id, value, LEAD(value) OVER (ORDER BY id) FROM table;

id  value  lead
--  -----  ----
1   100    200
2   200    NULL

MySQL: с версии 8.0 также включает функцию LEAD с аналогичными параметрами.

SQLite: с версии 3.25.0 поддерживает оконные функции, включая LEAD. Пример:

SELECT value, LEAD(value, 1, 'N/A') OVER (ORDER BY id) FROM items;

Sybase ASE: не имеет прямой функции LEAD, требуется эмуляция через подзапросы или соединения.

В языках программирования, например, в Python библиотеке pandas, аналогичная операция выполняется методом shift.