С помощью функций смещения можно для каждой записи с заказом пользователя вернуть дату прошлого или следующего заказа — previous_order_dt и next_order_dt соответственно. Функция LAG() позволяет возвращать предыдущие записи, а LEAD() — следующие.
У функций есть несколько аргументов:
- LEAD(<поле>, <смещение>, <значение по умолчанию>) OVER (<определение окна>);
- LAG(<поле>, <смещение>, <значение по умолчанию>) OVER (<определение окна>).
Аргумент <поле> указывает, из какого поля нужно вернуть значения.
Аргумент <смещение> показывает, на какое количество строк относительно текущей должно произойти смещение.
Аргумент <значение по умолчанию> указывает, какое значение нужно вернуть в случае, если нужной строки в таблице нет.
Указывать аргумент для смещения нужно в типе integer. Отрицательные значения тоже можно использовать, но тогда функция LEAD() превратится в функцию LAG() и наоборот.
Задача 1
Из таблицы tools_shop.orders выведите поля order_id, user_id, paid_at и к ним добавьте поле paid_at с датой предыдущего заказа для каждого пользователя. Если предыдущего заказа нет, выведите дату 1 января 1980 года.
SELECT order_id, user_id, paid_at,
LAG(paid_at,1,'1980-01-01') OVER (PARTITION BY user_id ORDER BY paid_at )
FROM tools_shop.orders
Задача 2
Выведите поля event_id, event_time, user_id из таблицы tools_shop.events и к ним добавьте поле с датой и временем следующего события для каждого пользователя. Если события нет, оставьте значение NULL.
SELECT event_id, event_time, user_id,
LEAD(event_time) OVER (PARTITION BY user_id ORDER BY event_time)
FROM tools_shop.events
Задача 3
Исправьте предыдущий запрос: замените дату следующего события на интервал между текущим и следующим событием. Значение интервала должно быть положительным. Если события нет, оставьте значение NULL.
SELECT event_id, event_time, user_id,
((LEAD(event_time) OVER (PARTITION BY user_id ORDER BY event_time)) - event_time) AS delta
FROM tools_shop.events