Ранжирование клиентов по тратам

Задача

Отдел лояльности хочет раздать статусы: «Проставь клиентам места по сумме их трат — кто первый, кто второй и так далее. И покажи заодно два варианта нумерации, чтобы мы решили, как считать при равных суммах».

Верни имя, фамилию, сумму трат и место клиента — двумя способами.

Схема данных

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

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

Решение

SELECT c.first_name, c.last_name,
       sum(p.amount) AS total_spent,
       rank()       OVER (ORDER BY sum(p.amount) DESC) AS rank_pos,
       dense_rank() OVER (ORDER BY sum(p.amount) DESC) AS dense_pos
FROM payment p
JOIN customer c USING (customer_id)
GROUP BY c.customer_id, c.first_name, c.last_name
ORDER BY total_spent DESC, c.customer_id
LIMIT 15;

Разбор

Здесь красиво сходятся две вещи: агрегат и окно в одном запросе. Сначала GROUP BY сворачивает платежи в сумму по каждому клиенту, а потом оконные функции ранжируют уже готовые суммы. Это законно: окно вычисляется после группировки, поэтому внутри RANK() OVER (...) можно писать сам агрегат sum(p.amount) — движок видит его как результат группировки. Что вообще делает окно и почему оно не схлопывает строки — в вопросе что такое оконные функции.

Теперь про две колонки. RANK и DENSE_RANK раздают места по убыванию суммы, но по-разному ведут себя на равных значениях. Если два клиента потратили поровну, оба получат одно место — скажем, 5-е. Дальше разница: RANK пропустит следующий номер (после двух пятых мест идёт 7-е, «спортивная» нумерация), а DENSE_RANK не пропускает — после пятого сразу шестое. Отсюда и названия: dense — «плотный», без дырок. Полный разбор с третьей функцией — в вопросе ROW_NUMBER, RANK и DENSE_RANK: разница.

Обрати внимание: у окон свой ORDER BY внутри OVER (...) — он задаёт, по чему раздавать места, и живёт отдельно от внешнего ORDER BY, который отвечает за порядок вывода строк. Внешний ORDER BY total_spent DESC, c.customer_id добавляет customer_id вторым ключом ради детерминизма — иначе клиенты с равной суммой встали бы в непредсказуемом порядке, и LIMIT 15 мог бы отрезать разных.

Если нужно не место, а оставить только клиентов выше среднего чека, — это уже подзапрос, смотри задачу клиенты с тратами выше среднего.

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

first_name | last_name | total_spent | rank_pos | dense_pos
--- | --- | --- | --- | ---
Eleanor | Hunt | 211.55 | 1 | 1
Karl | Seal | 208.58 | 2 | 2
Marion | Snyder | 194.61 | 3 | 3
Rhonda | Kennedy | 191.62 | 4 | 4
Clara | Shaw | 189.60 | 5 | 5
Tommy | Collazo | 183.63 | 6 | 6
Ana | Bradley | 167.67 | 7 | 7
Curtis | Irby | 167.62 | 8 | 8
Marcia | Dean | 166.61 | 9 | 9
Mike | Way | 162.67 | 10 | 10
… всего строк: 15 (показаны первые 10)