Предыдущий платёж клиента

Задача

Аналитик изучает поведение клиентов во времени: «По одному клиенту разложи его платежи по датам и рядом с каждым покажи, сколько он заплатил в прошлый раз. Хочу видеть динамику: чек растёт или падает от визита к визиту».

Верни id платежа, дату, сумму и сумму предыдущего платежа этого клиента.

Схема данных

Учебная база проката dvdrental:

• payment(payment_id, customer_id, amount, payment_date, ...) — платежи; payment_date задаёт порядок во времени.

Решение

SELECT p.payment_id,
       p.customer_id,
       to_char(p.payment_date, 'YYYY-MM-DD HH24:MI') AS payment_date,
       p.amount,
       lag(p.amount) OVER (PARTITION BY p.customer_id
                           ORDER BY p.payment_date, p.payment_id) AS prev_amount
FROM payment p
WHERE p.customer_id = 1
ORDER BY p.payment_date, p.payment_id;

Разбор

Когда нужно сравнить строку с соседней — «а что было в прошлый раз?» — на помощь приходит LAG. Эта оконная функция заглядывает на одну строку назад в пределах окна и достаёт оттуда значение. lag(p.amount) рядом с текущим платежом кладёт сумму предыдущего — ровно то, что просил аналитик.

Две части в OVER (...) делают всю работу. PARTITION BY p.customer_id — окно живёт внутри одного клиента: LAG не «перепрыгнет» на чужого клиента, у каждого своя лента платежей. ORDER BY p.payment_date, p.payment_id задаёт, что значит «предыдущий», — предыдущий по времени. Без этой сортировки понятие «прошлый платёж» вообще не определено: строки в таблице не упорядочены, и движку неоткуда взять «назад». Второй ключ payment_id — tie-break на случай, если два платежа попали на один и тот же момент.

Важная деталь: у самого первого платежа клиента предыдущего нет, и LAG вернёт NULL. Это не ошибка, а честный сигнал «сравнивать не с чем» — так и должно быть у начала ряда. Если хочется подставить туда, скажем, ноль, у LAG есть третий аргумент: lag(p.amount, 1, 0). Про то, что вообще такое оконная функция и почему она не сворачивает строки, — в вопросе что такое оконные функции.

WHERE p.customer_id = 1 оставляет одного клиента — так виден смысл: лента его платежей и сдвиг на строку. Зеркальная функция LEAD смотрит наоборот, на строку вперёд.

Если нужно не «предыдущее значение», а место клиента в общем рейтинге трат, — смотри задачу ранжирование клиентов по тратам.

Ожидаемый результат

payment_id | customer_id | payment_date | amount | prev_amount
--- | --- | --- | --- | ---
18495 | 1 | 2007-02-14 23:22 | 5.99 | NULL
18496 | 1 | 2007-02-15 16:31 | 0.99 | 5.99
18497 | 1 | 2007-02-15 19:37 | 9.99 | 0.99
18498 | 1 | 2007-02-16 13:47 | 4.99 | 9.99
18499 | 1 | 2007-02-18 07:10 | 4.99 | 4.99
18500 | 1 | 2007-02-18 12:02 | 0.99 | 4.99
18501 | 1 | 2007-02-21 04:53 | 3.99 | 0.99
22680 | 1 | 2007-03-01 07:19 | 4.99 | 3.99
22681 | 1 | 2007-03-02 14:05 | 3.99 | 4.99
22682 | 1 | 2007-03-02 16:30 | 0.99 | 3.99
… всего строк: 30 (показаны первые 10)