Доля платежа в выручке клиента

Задача

Аналитик по удержанию разбирает структуру трат: «Возьми пару клиентов и по каждому их платежу посчитай, какую долю он занимает в общей сумме этого клиента. Хочу видеть, ровно человек платит или одним крупным чеком».

Верни id платежа, имя клиента, сумму платежа и его долю в процентах от всех трат этого клиента.

Схема данных

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

• customer(customer_id, first_name, last_name, ...) — клиенты.
• payment(payment_id, customer_id, amount, ...) — платежи; у одного клиента их несколько.

Решение

SELECT p.payment_id,
       c.first_name, c.last_name,
       p.amount,
       round(100.0 * p.amount
             / sum(p.amount) OVER (PARTITION BY p.customer_id), 2) AS pct_of_customer
FROM payment p
JOIN customer c USING (customer_id)
WHERE p.customer_id IN (1, 2)
ORDER BY p.customer_id, p.payment_id;

Разбор

Задача на «долю от целого», и оконный агрегат тут подходит идеально. Обычный SUM с GROUP BY схлопнул бы все платежи клиента в одну строку с итогом — и отдельных платежей мы бы уже не увидели. А SUM(p.amount) OVER (PARTITION BY p.customer_id) считает ту же сумму по клиенту, но не сворачивает строки: рядом с каждым платежом остаётся и он сам, и итог по его клиенту. Дальше делим одно на другое — получаем долю.

PARTITION BY p.customer_id — это раздел окна: сумма считается заново для каждого клиента, а не по всей таблице. У платежей клиента №1 в знаменателе будет его итог, у клиента №2 — его. Если бы PARTITION BY убрали, знаменателем стала бы выручка вообще всех, и доли получились бы микроскопические. Чем окно отличается от группировки — в вопросе что такое оконные функции.

Пара технических деталей. 100.0 * (а не 100 *) заставляет деление идти в числах с плавающей точкой — иначе целочисленное деление округлило бы всё до нуля. round(..., 2) оставляет два знака — проценты не нужно показывать с десятком цифр после запятой. Внутри окна ORDER BY не нужен: нам важна сумма по всему разделу, а не нарастающий итог, поэтому пишем голый PARTITION BY без сортировки.

WHERE p.customer_id IN (1, 2) сужает вывод до двух клиентов, чтобы результат было видно целиком и чтобы понять смысл — на границе между клиентами знаменатель меняется. Проверь сам: доли внутри каждого клиента складываются в 100%. Внешний ORDER BY p.customer_id, p.payment_id даёт стабильный порядок.

Если вместо доли нужен нарастающий итог по времени — смотри задачу накопительная выручка по дням.

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

payment_id | first_name | last_name | amount | pct_of_customer
--- | --- | --- | --- | ---
18495 | Mary | Smith | 5.99 | 5.22
18496 | Mary | Smith | 0.99 | 0.86
18497 | Mary | Smith | 9.99 | 8.71
18498 | Mary | Smith | 4.99 | 4.35
18499 | Mary | Smith | 4.99 | 4.35
18500 | Mary | Smith | 0.99 | 0.86
18501 | Mary | Smith | 3.99 | 3.48
22680 | Mary | Smith | 4.99 | 4.35
22681 | Mary | Smith | 3.99 | 3.48
22682 | Mary | Smith | 0.99 | 0.86
… всего строк: 56 (показаны первые 10)