Агрегаты и группировка по шагам

Вопрос

Отдел лояльности хочет поблагодарить покупателей, которые уже получили не меньше трёх заказов. Нужны три самых активных из них: номер покупателя, сколько заказов доставлено и когда был сделан последний из них.

Такой запрос не пишут сразу целиком. Его собирают по шагам, и после каждого шага смотрят на результат — так ошибку видно сразу, а не в конце. Шаги идут в том же порядке, в каком PostgreSQL их выполняет (статья «Как читается SQL-запрос»).

Шаг 1. FROM и WHERE: какие строки считаем

Заказы лежат в orders — 200 строк. Нам нужны только доставленные, поэтому сразу отбрасываем остальные в WHERE:

SELECT id, customer_id, created_atFROM ordersWHERE status = 'delivered'ORDER BY customer_id, idLIMIT 6

 id  | customer_id |     created_at
-----+-------------+---------------------
  60 |           1 | 2025-05-12 04:00:00
  73 |           2 | 2025-06-19 22:00:00
 121 |           4 | 2025-04-15 19:00:00
  52 |           5 | 2025-04-11 00:00:00
 128 |           5 | 2025-07-01 17:00:00
  41 |           6 | 2025-04-11 19:00:00
(6 rows)

Это сырьё для группировки: по строке на заказ. ORDER BY customer_id и LIMIT здесь только чтобы посмотреть начало: видно, что у покупателя 5 два доставленных заказа. WHERE работает со строками до группировки — всё, что он отбросил, ни в одном счётчике дальше не появится.

Шаг 2. GROUP BY: строка на покупателя

SELECT customer_id, COUNT(*) AS deliveredFROM ordersWHERE status = 'delivered'GROUP BY customer_idORDER BY customer_idLIMIT 8

 customer_id | delivered
-------------+-----------
           1 |         1
           2 |         1
           4 |         1
           5 |         2
           6 |         2
           7 |         3
           9 |         2
          10 |         3
(8 rows)

GROUP BY customer_id складывает строки с одинаковым customer_id в одну стопку, и дальше запрос работает со стопками, а не со строками: одна стопка — одна строка результата. COUNT(*) считает строки в стопке. Два заказа покупателя 5 из шага 1 превратились в одну строку с числом 2. Покупателей 3 и 8 нет: заказы у них есть, но ни одного доставленного — все их строки отбросил WHERE, и стопки не появилось. Подробнее — карточки group-by и count.

После GROUP BY в SELECT можно писать только колонки из GROUP BY и агрегаты. Отдельную колонку заказа, например created_at, вывести нельзя — какую из дат стопки показать? Об этом в «Частых ошибках» ниже.

Шаг 3. HAVING: какие группы оставить

Нужны покупатели минимум с тремя доставленными заказами. Это условие на группу, а не на строку — число заказов известно только после группировки. Для таких условий есть HAVING:

SELECT customer_id, COUNT(*) AS delivered, MAX(created_at) AS last_delivered_orderFROM ordersWHERE status = 'delivered'GROUP BY customer_idHAVING COUNT(*) >= 3ORDER BY customer_id

 customer_id | delivered | last_delivered_order
-------------+-----------+----------------------
           7 |         3 | 2025-08-29 21:00:00
          10 |         3 | 2025-05-23 00:00:00
          27 |         5 | 2025-07-10 05:00:00
          31 |         4 | 2025-08-22 23:00:00
          33 |         3 | 2025-06-14 18:00:00
          36 |         3 | 2025-08-29 17:00:00
          37 |         4 | 2025-08-24 00:00:00
(7 rows)

Из 37 покупателей с доставленными заказами условие выдержали семь. Заодно добавили MAX(created_at) — самую позднюю дату в стопке, то есть последний доставленный заказ. WHERE и HAVING отличаются не синтаксисом, а шагом: WHERE фильтрует строки до группировки, HAVING — готовые группы после. В HAVING пишут агрегат целиком: имя delivered появится только на шаге SELECT, позже HAVING (карточка having).

Шаг 4. ORDER BY и LIMIT: самые активные

SELECT customer_id, COUNT(*) AS delivered, MAX(created_at) AS last_delivered_orderFROM ordersWHERE status = 'delivered'GROUP BY customer_idHAVING COUNT(*) >= 3ORDER BY delivered DESC, customer_idLIMIT 3

 customer_id | delivered | last_delivered_order
-------------+-----------+----------------------
          27 |         5 | 2025-07-10 05:00:00
          31 |         4 | 2025-08-22 23:00:00
          37 |         4 | 2025-08-24 00:00:00
(3 rows)

ORDER BY выполняется после SELECT, поэтому здесь имя delivered уже работает. У покупателей 31 и 37 по четыре заказа — без второго ключа customer_id их порядок между собой не гарантирован. А будь LIMIT 4, стало бы хуже: четвёртое место делят четыре покупателя с тремя заказами, и кто из них попадёт в результат, решал бы случай (карточки order-by и limit). Ответ готов: запрос читается сверху вниз, а строится — в порядке шагов.

Чтобы вместо номеров вывести имена, соедини результат с customers (статья «JOIN на пальцах») и группируй по c.id — по первичному ключу остальные колонки покупателя брать можно (карточка group-by).

Частые ошибки

Колонка не из GROUP BY и не в агрегате.

SELECT customer_id, created_at, COUNT(*)FROM ordersWHERE status = 'delivered'GROUP BY customer_id

ERROR: column "orders.created_at" must appear in the GROUP BY clause or be used in an aggregate function

В стопке покупателя несколько заказов, и у каждого своя дата — PostgreSQL не станет выбирать одну наугад. Реши, какая дата нужна: последняя — MAX(created_at), первая — MIN(created_at). Если же нужна каждая дата отдельно, то группировка не нужна вовсе — или нужна оконная функция (статья «Оконные функции против GROUP BY»). Добавить created_at в GROUP BY формально можно, но тогда стопкой станет «покупатель + момент заказа», и в этих данных счётчик в каждой строке станет равен 1.

Условие на агрегат в WHERE. Запрос … WHERE COUNT(*) >= 3 GROUP BY customer_id падает с ERROR: aggregate functions are not allowed in WHERE: на шаге WHERE групп ещё нет, считать нечего. Условие на количество — в HAVING, условие на отдельный заказ (статус, дата) — в WHERE. Если перепутать наоборот и написать HAVING status = 'delivered', PostgreSQL тоже откажет: в группе много статусов, и status нет в GROUP BY.