Пошук уроків, статей та іншого контенту
Отримуйте попередні й наступні значення у впорядкованому наборі за допомогою LAG та LEAD.
LAG і LEADLAG та LEAD — віконні функції PostgreSQL, які дають змогу звернутися до іншого рядка в межах впорядкованого набору:
LAG отримує значення з попереднього рядка;
LEAD отримує значення з наступного рядка.
На відміну від GROUP BY, ці функції не об’єднують рядки. Кожен рядок зберігається, а поруч із ним додається значення з іншого рядка.
Це корисно, коли потрібно:
порівняти поточне значення з попереднім;
знайти зміну між двома послідовними значеннями;
визначити час до наступної події;
знайти початок або кінець періоду;
аналізувати історію змін.
LAG (значення [, зміщення [, значення_за_замовчуванням]])
OVER (
[PARTITION BY групування]
ORDER BY сортування
)LEAD (значення [, зміщення [, значення_за_замовчуванням]])
OVER (
[PARTITION BY групування]
ORDER BY сортування
)Параметри:
значення — стовпець або вираз, значення якого потрібно отримати;
зміщення — кількість рядків уперед або назад, за замовчуванням 1;
значення_за_замовчуванням — значення, яке повертається, якщо потрібного рядка не існує;
ORDER BY — порядок, у якому PostgreSQL визначає попередні та наступні рядки;
PARTITION BY — необов’язкове розділення рядків на незалежні групи.
Напрямок залежить від функції:
LAG(value) -- значення з попереднього рядка
LEAD(value) -- значення з наступного рядкаСтворимо тимчасову таблицю з денними продажами товарів:
CREATE TEMP TABLE daily_sales (
product_id integer NOT NULL,
sale_date date NOT NULL,
amount numeric(10, 2) NOT NULL
);
INSERT INTO daily_sales (product_id, sale_date, amount)
VALUES
(1, DATE '2026-03-01', 100.00),
(1, DATE '2026-03-02', 135.00),
(1, DATE '2026-03-03', 120.00),
(1, DATE '2026-03-04', 180.00),
(2, DATE '2026-03-01', 80.00),
(2, DATE '2026-03-02', 95.00),
(2, DATE '2026-03-03', 110.00);Потрібно для кожного продажу отримати суму попереднього та наступного дня:
SELECT
product_id,
sale_date,
amount,
LAG(amount) OVER (
PARTITION BY product_id
ORDER BY sale_date
) AS previous_amount,
LEAD(amount) OVER (
PARTITION BY product_id
ORDER BY sale_date
) AS next_amount
FROM daily_sales
ORDER BY product_id, sale_date;Результат матиме приблизно такий вигляд:
product_id | sale_date | amount | previous_amount | next_amount
------------+------------+--------+-----------------+------------
1 | 2026-03-01 | 100.00 | NULL | 135.00
1 | 2026-03-02 | 135.00 | 100.00 | 120.00
1 | 2026-03-03 | 120.00 | 135.00 | 180.00
1 | 2026-03-04 | 180.00 | 120.00 | NULL
2 | 2026-03-01 | 80.00 | NULL | 95.00
2 | 2026-03-02 | 95.00 | 80.00 | 110.00
2 | 2026-03-03 | 110.00 | 95.00 | NULLPARTITION BY product_id гарантує, що перший продаж товару 2 не порівнюватиметься з останнім продажем товару 1.
Часто потрібно не лише отримати попереднє значення, а й обчислити різницю:
SELECT
product_id,
sale_date,
amount,
LAG(amount) OVER (
PARTITION BY product_id
ORDER BY sale_date
) AS previous_amount,
amount - LAG(amount) OVER (
PARTITION BY product_id
ORDER BY sale_date
) AS change_amount
FROM daily_sales
ORDER BY product_id, sale_date;Для першого рядка кожної групи попереднього значення немає, тому LAG повертає NULL. Арифметична операція з NULL також повертає NULL.
Щоб отримати відсоткову зміну, спочатку збережімо попереднє значення у CTE:
WITH sales_with_previous AS (
SELECT
product_id,
sale_date,
amount,
LAG(amount) OVER (
PARTITION BY product_id
ORDER BY sale_date
) AS previous_amount
FROM daily_sales
)
SELECT
product_id,
sale_date,
amount,
previous_amount,
CASE
WHEN previous_amount IS NULL OR previous_amount = 0 THEN NULL
ELSE ROUND(
(amount - previous_amount) / previous_amount * 100,
2
)
END AS change_percent
FROM sales_with_previous
ORDER BY product_id, sale_date;CTE потрібен для зручності: спочатку обчислюється previous_amount, а потім це значення використовується в іншому виразі.
LEADLEAD зручно використовувати для визначення часу до наступної події.
Наприклад, є історія статусів замовлення:
CREATE TEMP TABLE order_status_history (
order_id integer NOT NULL,
status_changed_at timestamp NOT NULL,
status text NOT NULL
);
INSERT INTO order_status_history (
order_id,
status_changed_at,
status
)
VALUES
(101, TIMESTAMP '2026-03-01 09:00:00', 'new'),
(101, TIMESTAMP '2026-03-01 10:30:00', 'paid'),
(101, TIMESTAMP '2026-03-02 14:00:00', 'shipped'),
(102, TIMESTAMP '2026-03-01 11:00:00', 'new'),
(102, TIMESTAMP '2026-03-01 12:15:00', 'cancelled');Знайдемо час наступної зміни статусу:
SELECT
order_id,
status,
status_changed_at,
LEAD(status_changed_at) OVER (
PARTITION BY order_id
ORDER BY status_changed_at
) AS next_status_changed_at
FROM order_status_history
ORDER BY order_id, status_changed_at;Тепер можна обчислити тривалість перебування замовлення у кожному статусі:
WITH status_intervals AS (
SELECT
order_id,
status,
status_changed_at,
LEAD(status_changed_at) OVER (
PARTITION BY order_id
ORDER BY status_changed_at
) AS next_status_changed_at
FROM order_status_history
)
SELECT
order_id,
status,
status_changed_at,
next_status_changed_at,
next_status_changed_at - status_changed_at AS status_duration
FROM status_intervals
ORDER BY order_id, status_changed_at;Для останнього статусу кожного замовлення наступної події немає, тому next_status_changed_at буде NULL.
Другий аргумент визначає, на скільки рядків потрібно зміститися.
Отримання значення два рядки тому:
SELECT
product_id,
sale_date,
amount,
LAG(amount, 2) OVER (
PARTITION BY product_id
ORDER BY sale_date
) AS amount_two_days_ago
FROM daily_sales
ORDER BY product_id, sale_date;Отримання значення через два рядки:
SELECT
product_id,
sale_date,
amount,
LEAD(amount, 2) OVER (
PARTITION BY product_id
ORDER BY sale_date
) AS amount_in_two_rows
FROM daily_sales
ORDER BY product_id, sale_date;Зміщення рахується в рядках, а не в календарних днях. Якщо в даних відсутня дата, LAG(amount, 1) візьме попередній наявний рядок, а не обов’язково попередній календарний день.
Третій аргумент дає змогу замінити NULL, якщо рядка на потрібній позиції немає:
SELECT
product_id,
sale_date,
amount,
LAG(amount, 1, 0) OVER (
PARTITION BY product_id
ORDER BY sale_date
) AS previous_amount,
LEAD(amount, 1, 0) OVER (
PARTITION BY product_id
ORDER BY sale_date
) AS next_amount
FROM daily_sales
ORDER BY product_id, sale_date;Для першого рядка кожного товару previous_amount буде 0, а для останнього — next_amount буде 0.
Значення за замовчуванням потрібно вибирати обережно. 0 означає реальне числове значення, тоді як NULL означає відсутність відповідного рядка. Для подальшої аналітики ці два випадки можуть мати різний зміст.
ORDER BYLAG та LEAD працюють відносно порядку, заданого у вікні:
LAG(amount) OVER (
PARTITION BY product_id
ORDER BY sale_date
)Без ORDER BY попередній і наступний рядки не мають визначеного змісту. PostgreSQL може повернути рядки в іншому порядку, і результат залежатиме від плану виконання запиту.
Якщо значення сортування можуть повторюватися, додайте додатковий унікальний стовпець. Наприклад:
LAG(status) OVER (
PARTITION BY order_id
ORDER BY status_changed_at, status
)У реальному застосунку краще використовувати однозначний ідентифікатор події:
LAG(status) OVER (
PARTITION BY order_id
ORDER BY status_changed_at, event_id
)Так PostgreSQL матиме стабільний порядок навіть для подій з однаковим часом.
PARTITION BY і незалежні послідовностіБез PARTITION BY усі рядки обробляються як одна послідовність:
SELECT
product_id,
sale_date,
amount,
LAG(amount) OVER (
ORDER BY sale_date
) AS previous_amount
FROM daily_sales
ORDER BY product_id, sale_date;У такому випадку попереднім рядком для першого продажу товару 2 може стати рядок товару 1. Для незалежного порівняння всередині кожного товару потрібно вказати:
PARTITION BY product_idРозділення не змінює результат на окремі групи, як це робить GROUP BY. Воно лише визначає межі, у яких LAG і LEAD шукають сусідні рядки.
Віконні функції обчислюються після WHERE у тому самому запиті. Тому умова може змінити набір рядків, між якими відбувається порівняння.
Наприклад:
SELECT
product_id,
sale_date,
amount,
LAG(amount) OVER (
PARTITION BY product_id
ORDER BY sale_date
) AS previous_amount
FROM daily_sales
WHERE sale_date >= DATE '2026-03-02'
ORDER BY product_id, sale_date;У цьому запиті дата 2026-03-01 спочатку виключається. Отже, для 2026-03-02 попереднього рядка в уже відфільтрованому наборі немає.
Якщо потрібно спочатку знайти попереднє значення в усій історії, а відфільтрувати результат після цього, використовуйте CTE:
WITH sales_with_previous AS (
SELECT
product_id,
sale_date,
amount,
LAG(amount) OVER (
PARTITION BY product_id
ORDER BY sale_date
) AS previous_amount
FROM daily_sales
)
SELECT
product_id,
sale_date,
amount,
previous_amount
FROM sales_with_previous
WHERE sale_date >= DATE '2026-03-02'
ORDER BY product_id, sale_date;Тепер previous_amount для 2026-03-02 буде значенням за 2026-03-01, хоча цей рядок не потрапить до фінального результату.
LAG(amount) OVER (PARTITION BY product_id)Такий запис не визначає, що саме є попереднім рядком. Додавайте ORDER BY за полем, яке описує послідовність.
PARTITION BYЯкщо дані містять кілька товарів, користувачів або замовлень, без PARTITION BY сусідніми можуть стати рядки з різних сутностей.
LAG(amount, 1)означає «попередній рядок», а не «попередній календарний день». Для роботи з пропущеними датами потрібно окремо враховувати, чи існує потрібна дата в даних.
NULLNULL у першому результаті LAG або в останньому результаті LEAD означає, що відповідного рядка немає. Не завжди правильно замінювати його на 0 через COALESCE.
WHEREТакий запит некоректний:
SELECT
sale_date,
amount,
LAG(amount) OVER (ORDER BY sale_date) AS previous_amount
FROM daily_sales
WHERE previous_amount < amount;Для фільтрації за результатом LAG спочатку обчисліть його в CTE або підзапиті, а потім використайте зовнішній SELECT.
LAG повертає значення з попереднього рядка.
LEAD повертає значення з наступного рядка.
ORDER BY визначає послідовність рядків.
PARTITION BY створює незалежні послідовності для груп.
Другий аргумент задає зміщення на кілька рядків.
Третій аргумент задає значення за замовчуванням.
На межах послідовності LAG і LEAD зазвичай повертають NULL.
Для порівняння поточного та попереднього значення зручно використовувати CTE.
Фільтруйте дані до або після обчислення віконної функції залежно від потрібної логіки.