Теория: PREV_VALUE

В этом уроке мы рассмотрим функции смещения. Их всего две:

• LEAD()
• LAG()

Синтаксис функций

LEAD(expression [, offset[, default_value]]) OVER(ORDER BY columns)
LAG(expression [, offset, default_value]]) OVER(ORDER BY columns)

LEAD()

Слово lead переводится с английского как «вести». С помощью этой функции мы ведем функцию к следующей строке — то есть обращаемся к данным из следующей строки набора данных. Эту функцию можно использовать, чтобы сравнить текущее значение со следующим.

У нее есть три параметра:

• Столбец, для которого нужно вернуть значение следующего значения параметра.
• Количество строк для смещения. По умолчанию это всегда одна строка, но мы можем поменять количество строк на любое, которое нам нужно.
• Значение, которое нужно вернуть в том случае, если после смещения возвращается значение NULL.

Для примера посмотрим изменение цен на криптобирже.

SELECT
    id,
    price,
    date_and_time,
    LEAD(
        price
    ) OVER (
        PARTITION BY DATE(date_and_time)
    ) AS ld
FROM
    bitcoin_prices;

View on DB Fiddle

Посмотрим на значения в ld - в каждой строке мы получили значение из следующей строки. У некоторых строк значение - NULL, так как следующего значения в этой группе (партиции) нет.

Мы можем указать дефолтное значение, которое будет подставляться, если не найдено значение. Для этого необходимо передать в LEAD() шаг смещения и само значение

-- получение следующего значения
SELECT
    id,
    price,
    date_and_time,
    LEAD(
        price, 1, 0.0
    ) OVER (
        PARTITION BY DATE(date_and_time)
    ) AS ld
FROM
    bitcoin_prices

View on DB Fiddle

LAG()

Слово lag переводится как «задержка». Эта функция подобна LEAD(), но функция обращается к данным из предыдущей строки набора.

С помощью функции LAG() можно сравнить текущее значение с предыдущим. Она также имеет три параметра:

• Столбец, который нужно вернуть.
• Количество строк смещения.
• Значение, на которое нужно сместить.

В целом, функция LAG() работает абсолютно так же, как и LEAD(), просто мы берем не последующие значения, а предыдущие.

-- получение предыдущего значения
SELECT
    id,
    price,
    date_and_time,
    LAG(
        price
    ) OVER (
        PARTITION BY DATE(date_and_time)
    ) AS lg
FROM
    bitcoin_prices

View on DB Fiddle

Применение LEAD и LAG

С помощью LEAD() и LAG() мы можем вычислять изменение значений со временем. Например, так можно получить динамику цен.

Попробуем решить задачу по поиску роста цен, относительно предыдущего периода. Для этого находим, следующую и предыдущую цену. Наша цена должна быть больше предыдущей и меньше следующей

-- находим записи, когда у нас цены росли относительно предыдущих в эти же дни
WITH tab AS (
    SELECT
        price,
        date_and_time,
        LAG(price) OVER (
            PARTITION BY DATE(date_and_time)
        ) AS lg,
        LEAD(price) OVER (
            PARTITION BY DATE(date_and_time)
        ) AS ld
    FROM
        bitcoin_prices
    ORDER BY
        date_and_time
)

SELECT * FROM tab
WHERE (price > lg OR lg IS NULL) AND (price < ld OR ld IS NULL);

View on DB Fiddle

Выводы

• Функции LEAD() и LAG() в SQL используются для доступа к значениям следующей или предыдущей строки внутри результирующего набора.
• Функция LAG() возвращает значение столбца из предыдущей строки, а функция LEAD() возвращает значение из следующей строки.
• Обе функции могут использоваться с опциональными параметрами, такими как смещение (offset) и значением по умолчанию (default value), что делает их более гибкими.
• Функции LEAD и LAG полезны при анализе временных рядов, сравнении текущих значений с предыдущими или следующими значениями, а также при выполнении различных операций с данными внутри запроса SQL.