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

Использование оконной функции LAG для доступа к предыдущим строкам
Раздел: Аналитические функции, Оконные
LAG(scalar_expression [, offset [, default]] OVER [PARTITION BY ] ORDER BY ): Same as scalar_expression
типовые примерыLAG на mySQL, Oracle, PostgreSQL, SQLiteLAG изменения sqlпримеры расширенные

Функция LAG в MS SQL Server

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

Когда используется:

  • Расчет разницы между последовательными записями (например, день к дню).
  • Анализ трендов и изменений во временных рядах.
  • Выявление аномалий путем сравнения с предыдущим периодом.
  • Заполнение пропущенных данных на основе предыдущих значений.

Синтаксис и аргументы:

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

  • scalar_expression - значение, которое будет возвращено из предыдущей строки. Может быть столбцом, выражением или результатом подзапроса.
  • offset (необязательный) - количество строк назад, от которых нужно получить значение. По умолчанию 1. Должно быть положительным целым числом.
  • default (необязательный) - значение, которое возвращается, если функция LAG ссылается на строку, выходящую за пределы раздела. По умолчанию NULL.
  • OVER ( ... ) - определяет секционирование и порядок строк. PARTITION BY делит результирующий набор на разделы (секции), в рамках которых работает функция. ORDER BY определяет порядок строк внутри каждого раздела.

Возвращаемый тип данных совпадает с типом scalar_expression.

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

Пример 1: Сравнение продаж с предыдущим днем.

SELECT 
    OrderDate,
    TotalDue,
    LAG(TotalDue) OVER (ORDER BY OrderDate) AS PreviousDaySales
FROM Sales.SalesOrderHeader
WHERE OrderDate BETWEEN '2011-07-01' AND '2011-07-05'
ORDER BY OrderDate;
OrderDate   TotalDue    PreviousDaySales
2011-07-01  23153.2339  NULL
2011-07-02  1457.0828   23153.2339
2011-07-03  36865.8012  1457.0828
2011-07-04  32474.9324  36865.8012
2011-07-05  472.3108    32474.9324

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

SELECT 
    OrderDate,
    TotalDue,
    LAG(TotalDue, 2, 0.00) OVER (ORDER BY OrderDate) AS SalesTwoDaysAgo
FROM Sales.SalesOrderHeader
WHERE OrderDate BETWEEN '2011-07-01' AND '2011-07-05'
ORDER BY OrderDate;
OrderDate   TotalDue    SalesTwoDaysAgo
2011-07-01  23153.2339  0.00
2011-07-02  1457.0828   0.00
2011-07-03  36865.8012  23153.2339
2011-07-04  32474.9324  1457.0828
2011-07-05  472.3108    36865.8012

Пример 3: Работа с разделами (PARTITION BY).

SELECT 
    TerritoryID,
    OrderDate,
    TotalDue,
    LAG(TotalDue) OVER (PARTITION BY TerritoryID ORDER BY OrderDate) AS PrevSaleInTerritory
FROM Sales.SalesOrderHeader
WHERE OrderDate BETWEEN '2011-07-01' AND '2011-07-05'
ORDER BY TerritoryID, OrderDate;
TerritoryID OrderDate   TotalDue    PrevSaleInTerritory
1           2011-07-01  23153.2339  NULL
1           2011-07-02  1457.0828   23153.2339
4           2011-07-01  36865.8012  NULL
4           2011-07-03  32474.9324  36865.8012
4           2011-07-04  472.3108    32474.9324

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

  • LEAD() - предоставляет доступ к данным из следующей строки. Аналогична LAG, но смотрит вперед. Используется, когда нужно сравнить с будущим значением.
  • FIRST_VALUE() - возвращает первое значение в упорядоченном наборе. Полезна для сравнения всех строк с начальным значением, а не только с непосредственно предыдущей.
  • ROW_NUMBER(), RANK(), DENSE_RANK() - ранжирующие функции. Не возвращают значения из других строк напрямую, но помогают в нумерации и анализе порядка, что можно комбинировать с LAG.
  • Самостоятельное соединение (Self Join) - классический способ получить предыдущее значение через JOIN t ON t.id = t_prev.id + 1. Функция LAG обычно производительнее и читаемее.

LAG предпочтительнее использовать для последовательного доступа к предыдущей строке в рамках логического окна. LEAD - для доступа к следующей строке. FIRST_VALUE - для фиксации на конкретной (первой) строке раздела.

Типичные ошибки

1. Отсутствие ORDER BY в предложении OVER. Это обязательный параметр для LAG.

-- ОШИБКА
SELECT LAG(TotalDue) OVER () FROM Sales.SalesOrderHeader;
Ошибка: Функция 'LAG' должна содержать предложение ORDER BY в предложении OVER.

2. Неправильный порядок сортировки. При анализе временных рядов порядок должен быть хронологическим (ORDER BY OrderDate ASC). Использование DESC может дать неинтуитивный результат.

3. Путаница между LAG и LEAD. LAG смотрит назад, LEAD - вперед.

-- LAG возвращает значение из предыдущей (более ранней) строки.
-- LEAD возвращает значение из следующей (более поздней) строки.
-- При ORDER BY Date ASC:
-- LAG(Date) -> вчера
-- LEAD(Date) -> завтра

4. Ожидание значения по умолчанию для всех NULL. Функция возвращает default только при выходе за границы раздела. Если в предыдущей строке было значение NULL, оно и будет возвращено.

-- Если предыдущая строка содержит NULL, будет возвращен NULL, а не 'default'.

Изменения в последних версиях

Функция LAG была введена в SQL Server 2012. С тех пор ее синтаксис и поведение оставались стабильными.

Основные улучшения связаны с общим развитием оконных функций и оптимизатором запросов:

  • SQL Server 2012: Первоначальное введение LAG и других оконных функций.
  • Более поздние версии: Улучшения в производительности обработки оконных функций, особенно при использовании индексов columnstore и пакетного режима выполнения (batch mode).

Новых параметров или изменений сигнатуры функции для LAG в версиях 2014, 2016, 2017, 2019, 2022 не добавлялось.

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

Пример 1: Расчет помесячного изменения в процентах.

Пример sql
WITH MonthlySales AS (
    SELECT 
        YEAR(OrderDate) AS OrderYear,
        MONTH(OrderDate) AS OrderMonth,
        SUM(TotalDue) AS TotalSales
    FROM Sales.SalesOrderHeader
    GROUP BY YEAR(OrderDate), MONTH(OrderDate)
)
SELECT 
    OrderYear,
    OrderMonth,
    TotalSales,
    LAG(TotalSales) OVER (ORDER BY OrderYear, OrderMonth) AS PrevMonthSales,
    ROUND((TotalSales - LAG(TotalSales) OVER (ORDER BY OrderYear, OrderMonth)) 
          / LAG(TotalSales) OVER (ORDER BY OrderYear, OrderMonth) * 100.0, 2) AS GrowthPercent
FROM MonthlySales
ORDER BY OrderYear, OrderMonth;
OrderYear OrderMonth TotalSales   PrevMonthSales GrowthPercent
2011      7          1184563.899  NULL           NULL
2011      8          2844062.806  1184563.899    140.10
2011      9          777474.1153  2844062.806    -72.67

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

Пример sql
WITH IDs AS (
    SELECT 1 AS ID UNION SELECT 2 UNION SELECT 4 UNION SELECT 5 UNION SELECT 8
)
SELECT 
    ID AS CurrentID,
    LAG(ID) OVER (ORDER BY ID) AS PreviousID,
    ID - LAG(ID) OVER (ORDER BY ID) AS GapSize
FROM IDs
WHERE ID - LAG(ID) OVER (ORDER BY ID) > 1 
   OR LAG(ID) OVER (ORDER BY ID) IS NULL;
CurrentID PreviousID GapSize
1         NULL       NULL
4         2          2
8         5          3

Пример 3: Анализ сессий пользователя - вычисление времени между событиями.

Пример sql
SELECT 
    UserID,
    EventTime,
    EventName,
    LAG(EventTime) OVER (PARTITION BY UserID ORDER BY EventTime) AS PrevEventTime,
    DATEDIFF(SECOND, LAG(EventTime) OVER (PARTITION BY UserID ORDER BY EventTime), EventTime) AS SecondsSinceLastEvent
FROM UserEvents
ORDER BY UserID, EventTime;

Пример 4: Скользящее среднее с использованием нескольких предыдущих значений (эмулируемое через LAG).

Пример sql
SELECT 
    Date,
    Sales,
    LAG(Sales,1) OVER (ORDER BY Date) AS SalesLag1,
    LAG(Sales,2) OVER (ORDER BY Date) AS SalesLag2,
    (Sales + LAG(Sales,1,0) OVER (ORDER BY Date) + LAG(Sales,2,0) OVER (ORDER BY Date)) / 3.0 AS MovingAvg3
FROM DailySales
ORDER BY Date;

Пример 5: Определение изменения статуса заказа.

Пример sql
SELECT 
    OrderID,
    StatusChangedDate,
    NewStatus,
    LAG(NewStatus) OVER (PARTITION BY OrderID ORDER BY StatusChangedDate) AS PreviousStatus,
    CASE 
        WHEN NewStatus <> LAG(NewStatus) OVER (PARTITION BY OrderID ORDER BY StatusChangedDate) THEN 'Status Changed'
        ELSE 'No Change'
    END AS ChangeFlag
FROM OrderStatusHistory
ORDER BY OrderID, StatusChangedDate;

Аналоги в других СУБД и языках

MySQL (с версии 8.0): Синтаксис идентичен SQL Server.

SELECT 
    val,
    LAG(val) OVER (ORDER BY id) AS prev_val
FROM t;

PostgreSQL: Полная поддержка стандартного синтаксиса оконных функций, включая LAG.

SELECT 
    val,
    LAG(val) OVER (ORDER BY ts) AS prev_val
FROM t;

Oracle: Поддерживает LAG. Также имеет нестандартный синтаксис LAG(...) IGNORE NULLS для пропуска NULL-значений.

SELECT 
    empno,
    sal,
    LAG(sal, 1, 0) OVER (ORDER BY hiredate) AS prev_sal
FROM emp;

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

SELECT 
    value,
    LAG(value, 1, 'N/A') OVER (ORDER BY date) AS previous_value
FROM events;

Sybase ASE & SQL Anywhere: Поддерживают LAG с аналогичным синтаксисом.

Отличия в основном касаются необязательных расширений, таких как IGNORE NULLS в Oracle, или производительности на больших окнах. Базовый синтаксис стандартизирован.

MS SQL LAG function comments

En
LAG Accesses data from a previous row in the same result set without a self-join