Агрегация в SQL: COUNT, SUM, GROUP BY, HAVING и условные метрики

Коротко

Агрегат сжимает группу строк в одно значение. Пока не названо, что такое одна строка до группировки, любое такое значение можно прочитать как минимум двумя способами — и отчёт будет выглядеть одинаково правильным при обоих чтениях. Семь частых вопросов про агрегацию ниже — это один вопрос про зерно, заданный с разных сторон. Запросы выполняются в песочнице симулятора на учебной базе «Маяк», суммы там в долларах. Отчёт из вступления и примеры в рублях — условные; рублёвые наборы собраны прямо в запросе.

• count(*) считает строки, count(column) — непустые значения, count(distinct column) — различные непустые значения. На одном наборе это три разных числа.
• NULL выпадает из всех агрегатов кроме count(*), поэтому у avg знаменатель меньше, чем кажется.
• avg(amount) по заказам и средний доход на клиента — разные метрики с разными знаменателями; одна не приближает другую.
• distinct даёт уникальные комбинации выведенных колонок, group by даёт группы, к которым можно прицепить агрегат.
• WHERE фильтрует строки до группировки и не видит алиасов из SELECT; HAVING фильтрует уже собранные группы и умеет сравнивать агрегаты.
• CASE и FILTER считают несколько метрик за один проход по данным — это и единственный честный способ сохранить общий знаменатель.
• Раскладка метрик по колонкам — не отдельная операция PIVOT, а та же условная агрегация, записанная шире.

Сначала назовите, что такое одна строка, потом пишите агрегат

Зерно — это ответ на вопрос «что описывает одна строка». У таблицы заказов зерно обычно «один заказ». После join с позициями заказа зерно становится «одна позиция в заказе». После left join со справочником пользователей появляется третий вариант: «один пользователь, а если у него есть заказы — то один его заказ». В трёх случаях count(*) вернёт три разных числа, и все три будут правдой про свой набор.

Полезная привычка: перед написанием запроса записать зерно результата одной фразой. «Одна строка — канал за месяц». «Одна строка — пользователь». «Одна строка — заказ». Фраза занимает десять секунд и сразу отвечает на половину вопросов: что ставить в group by, что считать числителем доли, где искать дубли и какая метрика тут вообще возможна.

Вторая половина вопросов решается второй фразой: какие строки входят в группу. Удаление строк фильтром меняет и числитель, и знаменатель любой последующей доли. Если из одного отчёта отмены исключены, а из другого нет, расхождение между ними не объясняется ни одной из формул — оно объясняется составом групп.

100 строк, 64 заказа и 51 покупатель — это один и тот же запрос

Вернёмся к отчёту из начала. Он условный, набор в нём один: пользователи канала paid_search, к которым через left join приклеены их заказы. 100 строк. 64 строки имеют заполненный order_id — это реальные заказы. Уникальных user_id среди строк с заказом 51 — столько людей купили хотя бы раз. Запрос ниже собирает тот же отчёт на учебной базе, где вместо заказов оплаты: 1 032 строки, 103 оплаты и 86 покупателей. Дальше всё зависит от того, какую из трёх функций поставить в колонку и как её подписать.

count(*) не смотрит внутрь строки, поэтому считает и строки-пустышки от left join, у которых от оплаты остался только NULL. count(payment_id) пропускает NULL и отвечает на вопрос «сколько оплат». count(distinct p.user_id) убирает повторных покупателей и отвечает «сколько покупателей» — именно по колонке из payments: по u.user_id вышло бы 1 015, все пользователи канала. Подпись колонки — часть расчёта: заголовок «payments» над значением count(*) превращает 1 032 строки в 1 032 оплаты без единой правки кода.

Отдельный случай — пустая группа. У канала, где ни один пользователь не купил, count(*) вернёт число пользователей, count(payment_id) вернёт 0, а sum(amount) вернёт NULL, а не нуль. В учебной базе такая группа — зарегистрированные 29 августа: 38 строк, 0 оплат, пустая сумма. В BI такой NULL обычно рисуется прочерком, и канал выглядит как «нет данных», хотя данные есть: выручки ноль. Поэтому денежные колонки в витринах оборачивают в coalesce(sum(amount), 0) — но только в витринах, где ноль действительно означает «продаж не было», а не «не загрузилось».

select
  u.channel,
  count(*)                         as rows_in_join,   -- 1032
  count(p.payment_id)              as payments,       -- 103
  count(distinct p.user_id)        as buyers,         -- 86
  coalesce(sum(p.amount), 0)       as revenue         -- 2467
from users as u
left join payments as p
  on p.user_id = u.user_id
where u.channel = 'paid_search'
group by u.channel;

Агрегаты пропускают NULL, и у AVG из-за этого другой знаменатель

Все агрегаты кроме count(*) игнорируют NULL. Это не особенность диалекта и не настройка, это правило языка. Из него следует неочевидное: у avg знаменатель равен не числу строк в группе, а числу строк с заполненным значением.

Возьмите одиннадцать оплаченных заказов, у десяти из которых сумма заполнена и даёт в сумме 13 000 ₽, а у одиннадцатого amount пустой — продажа прошла по внутреннему промо и сумму не записали. В payments учебной базы пропусков нет: count(amount) равен count(*), 1 251. Поэтому набор собран в запросе. count(*) вернёт 11, count(amount) вернёт 10, sum(amount) вернёт 13 000, а avg(amount) вернёт 1 300, потому что делит 13 000 на 10. Напишите avg(coalesce(amount, 0)) — получите 1 181,8, потому что знаменатель стал 11. Оба числа арифметически верны, и выбирать между ними нужно по смыслу пропуска: пустая сумма означает «бесплатный заказ» или «данные не доехали».

Этот же механизм объясняет, почему sum по сломанной загрузке не выглядит сломанным. Если треть строк приехала с пустой суммой, sum молча сложит остальные две трети и вернёт аккуратное число без предупреждений. Расхождение с бухгалтерией найдут через неделю, и искать будут в атрибуции. Поэтому сравнение count(*) с count(amount) стоит рядом с самой метрикой — как колонка в служебной витрине, а не как отдельная проверка, которую кто-то однажды запустил.

with orders (order_id, user_id, amount) as (
  -- учебная вставка: десять оплаченных заказов семи клиентов и строка без суммы
  values
    (1, 1, 1200), (2, 1, 300), (3, 2, 2000),
    (4, 3, 500),  (5, 3, 700), (6, 3, 600),
    (7, 4, 4800), (8, 5, 900), (9, 6, 1000), (10, 7, 1000),
    (11, 5, null)
)
select
  count(*)                                    as rows_total,      -- 11
  count(amount)                               as rows_with_amount,-- 10
  count(*) - count(amount)                    as rows_missing,    -- 1
  sum(amount)                                 as revenue,         -- 13000
  avg(amount)                                 as avg_nonnull,     -- 1300
  round(cast(avg(coalesce(amount, 0)) as numeric), 1) as avg_with_zeros -- 1181.8
from orders;

Средний чек 1 300 ₽ и средний доход на клиента 1 857 ₽ — оба верны

Возьмите десять заказов с заполненной суммой от семи клиентов. Суммы: 1 200, 300, 2 000, 500, 700, 600, 4 800, 900, 1 000, 1 000. Итого 13 000 ₽. Средний чек — это 13 000 / 10 = 1 300 ₽. Средний доход на клиента считается в два шага: сначала сумма по каждому user_id, потом среднее по клиентам. Суммы по клиентам выходят 1 500, 2 000, 1 800, 4 800, 900, 1 000, 1 000 — те же 13 000 ₽, но уже на семь объектов: 1 857 ₽.

Разница в 43% взялась не из данных, а из знаменателя. Клиент с тремя заказами по 500–700 ₽ тянет средний чек вниз, но в среднем на клиента он стоит столько же, сколько однократный покупатель на 1 800 ₽. Ни одно из двух чисел не «точнее»: первое отвечает на вопрос про типичную покупку, второе — про типичного клиента. Они расходятся ровно настолько, насколько различается частота покупок, и это расхождение само по себе метрика.

Отсюда практическое правило: агрегат на одном уровне нельзя получить усреднением агрегата на другом. Средний доход на клиента — это avg по результату группировки, а не avg по заказам. Технически это означает два слоя: CTE с суммами по пользователю, затем внешний агрегат по нему. Тот же двухслойный приём нужен для «среднего числа заказов на клиента», «средней длины сессии на пользователя» и вообще любой метрики, в названии которой есть слово «на».

Длинный хвост добавляет третий вопрос. В тех же семи клиентах медиана дохода — 1 500 ₽, а среднее — 1 857 ₽: один клиент на 4 800 ₽ даёт 37% всей выручки и сдвигает среднее на четверть. Для денежных распределений среднее почти всегда выше медианы, и показывать его в одиночку — значит обещать читателю клиента, которого в данных нет. Рядом со avg держите percentile_cont(0.5) и максимум: три числа вместе описывают форму, одно — только центр тяжести.

На учебной базе устроено так же. Уберите вставку и замените orders на payments: 1 251 оплата на $30 639, средний платёж $24,49; 912 плательщиков, $33,60 на каждого, медиана $29, максимум $117.

with orders (order_id, user_id, amount) as (
  -- учебная вставка: те же десять заказов семи клиентов, без строки с пустой суммой
  values
    (1, 1, 1200), (2, 1, 300), (3, 2, 2000),
    (4, 3, 500),  (5, 3, 700), (6, 3, 600),
    (7, 4, 4800), (8, 5, 900), (9, 6, 1000), (10, 7, 1000)
),
per_user as (
  select user_id, sum(amount) as user_revenue, count(*) as orders_count
  from orders
  group by user_id
)
select
  (select count(*)    from orders)                      as orders_total,   -- 10
  (select sum(amount) from orders)                      as revenue,        -- 13000
  (select round(cast(avg(amount) as numeric), 2) from orders) as avg_order, -- 1300
  count(*)                                              as users,          -- 7
  round(cast(avg(user_revenue) as numeric), 2)          as avg_per_user,   -- 1857.14
  percentile_cont(0.5) within group (order by user_revenue) as median_user, -- 1500
  max(user_revenue)                                     as top_user        -- 4800
from per_user;

MIN(paid_at) без GROUP BY отвечает про таблицу, а не про клиента

min и max — те же агрегаты, и они так же зависят от зерна. min(paid_at) по всей таблице даёт дату первой оплаты в истории продукта — в учебной базе это 6 июня 2026 года. min(paid_at) с group by user_id даёт дату первой покупки каждого клиента — ту, от которой считают когорту, время до второй покупки и срок жизни. Это две разные вещи, и опечатка между ними не вызывает ошибки: просто вся когортная разбивка съезжает к одной дате.

У min/max по времени есть своя ловушка — граничные значения чувствительны к мусору сильнее любых других агрегатов. Одна тестовая запись с датой 1970 года становится «первой покупкой» клиента. Один заказ с датой из будущего становится «последней активностью» и ломает расчёт «дней с последнего визита». Средние такой мусор разбавляют, крайние — показывают целиком. Поэтому фильтр валидного диапазона дат нужен именно там, где берутся крайние значения: paid_at >= date '2020-01-01' and paid_at < current_date + 1.

Третий вопрос — что означает отсутствие события. max(login_at) по пользователю без логинов вернёт NULL, и дальше арифметика current_date - max(login_at) тоже даст NULL. В витрине это превращается в пустую ячейку, в фильтре «не заходил больше 30 дней» — в выпадение такого пользователя из выборки, хотя он как раз самый неактивный. Либо считайте отсутствие отдельной категорией, либо подставляйте дату регистрации, но выбор делайте явно, а не оставляйте NULL течь по всем следующим расчётам.

select
  user_id,
  min(paid_at)                 as first_paid_at,
  max(paid_at)                 as last_paid_at,
  count(*)                     as payments,
  max(paid_at) - min(paid_at)  as lifespan_days
from payments
where paid_at >= date '2020-01-01'
  and paid_at < current_date + 1
group by user_id
having count(*) > 1                 -- 290 клиентов с повторной оплатой
order by lifespan_days desc, user_id  -- у первых десяти по три оплаты и 60 дней
limit 10;

SUM после соединения один-ко-многим удваивает выручку

Самая дорогая ошибка агрегации выглядит как безобидное добавление колонки. Отчёт по месячной выручке активных подписок работал на таблице subscriptions: 515 подписок на $12 595. Затем понадобилось число оплат — его приклеили из payments. У клиента две оплаты, значит его подписка превратилась в две строки, и её цена вошла в sum(monthly_price) дважды. Строк стало 790, выручка — $19 350. Вдвое выросла не вся сумма: у 187 подписок по две оплаты, у 44 — по три, у 284 одна, и итог больше верного в 1,54 раза. Запрос не сломался: он честно сложил то, что в наборе есть.

Диагностика занимает один запрос: посчитайте count(*) до соединения и после. Если число строк выросло, зерно изменилось, и все суммы в этом запросе теперь считаются по другому объекту. Неравенство count(*) > count(distinct subscription_id) — тот же признак, только его удобнее держать в тесте витрины: здесь это 790 против 515.

Лечится это не distinct, а порядком операций. Сначала агрегируйте на правильном зерне, потом присоединяйте — к уже свёрнутому результату, в котором одна строка означает одну подписку или одного клиента. Если вторая таблица может дать несколько строк на ключ, сверните её до соединения: число оплат и дата последней — одной строкой на клиента, без надежды, что дубль не приедет. sum(distinct monthly_price) в такой ситуации — не решение, а новая ошибка: 290 честных подписок basic по $19 он сложит как одну.

-- join размножил подписки: цена вошла в сумму по разу на каждую оплату
select
  s.plan,
  count(*)                           as rows_in_join,   -- 446, 254, 90
  count(distinct s.subscription_id)  as subscriptions,  -- 290, 169, 56
  sum(s.monthly_price)               as monthly_revenue -- 8474, 7366, 3510
from subscriptions as s
join payments as p on p.user_id = s.user_id
where s.status = 'active'
group by s.plan
order by s.plan;

with paid as (
  select user_id, count(*) as payments, max(paid_at) as last_paid_at
  from payments
  group by user_id
)
select
  s.plan,
  count(*)                 as subscriptions,   -- 290, 169, 56
  sum(s.monthly_price)     as monthly_revenue, -- 5510, 4901, 2184
  sum(pd.payments)         as payments,        -- 446, 254, 90
  max(pd.last_paid_at)     as last_paid_at
from subscriptions as s
join paid as pd on pd.user_id = s.user_id
where s.status = 'active'
group by s.plan
order by s.plan;

DISTINCT прячет повтор, GROUP BY заставляет его объяснить

select distinct и group by по тем же колонкам без агрегатов дают одинаковый результат, и из этого делают вывод, что разницы нет. Разница не в результате, а в том, что можно сделать дальше. distinct возвращает уникальные комбинации выведенных колонок и на этом заканчивается. group by создаёт группы, к которым прицепляется любой агрегат — и первый же агрегат отвечает на вопрос, который distinct только что закрыл: сколько именно строк он свернул.

В таблице events учебной базы повторов нет: за июль 12 752 строки и столько же event_id. Поэтому сырой поток собран в запросе: CTE raw_events доставляет каждое шестое событие повторно, как в материале о дедупликации. В таком июле 14 877 строк, 12 752 различных event_id, 2 688 различных user_id. Первые два числа расходятся на 2 125 строк — это 14,3% потока, приехавшего дважды с тем же идентификатором. select distinct по колонкам события эти 2 125 строк уберёт, отчёт станет аккуратным, и никто не узнает, что загрузка дублирует события. group by event_id having count(*) > 1 покажет те же 2 125 событий как проблему, с примерами и временем появления.

Различие event_id и user_id в той же тройке — про другое. 12 752 события на 2 688 людей означает почти пять событий на человека, и это нормальное поведение, а не дубль. Поэтому правило уникальности формулируется до запроса и отдельно для каждого уровня: событие уникально по event_id, пользователь — по user_id, пара «пользователь и день» — по их комбинации. Без такого правила distinct становится ставкой на то, что лишние строки окажутся лишними.

У distinct есть ещё одно свойство, из-за которого его ставят зря: он работает по всем выведенным колонкам целиком. Достаточно одной технической колонки с уникальным значением — loaded_at, row_id, etl_batch — и дубли перестают склеиваться, а стоимость сортировки остаётся. В том же потоке select distinct * вернёт все 14 877 строк: у копий разное loaded_at. Запрос выглядит защищённым, фактически не делает ничего, и это замечают только при сверке.

with raw_events as (
  select e.*, event_time + interval '2 seconds' as loaded_at
  from events as e
  union all  -- учебная вставка: каждое шестое событие доставлено повторно, через четыре минуты
  select e.*, event_time + interval '4 minutes'
  from events as e
  where event_id % 6 = 0
)
select
  count(*)                    as rows_total,    -- 14877
  count(distinct event_id)    as unique_events, -- 12752
  count(distinct user_id)     as unique_users   -- 2688
from raw_events
where event_time >= timestamp '2026-07-01 00:00:00'
  and event_time <  timestamp '2026-08-01 00:00:00';

with raw_events as (
  select e.*, event_time + interval '2 seconds' as loaded_at
  from events as e
  union all  -- учебная вставка: каждое шестое событие доставлено повторно, через четыре минуты
  select e.*, event_time + interval '4 minutes'
  from events as e
  where event_id % 6 = 0
)
select event_id, count(*) as copies, min(loaded_at) as first_seen, max(loaded_at) as last_seen
from raw_events
where event_time >= timestamp '2026-07-01 00:00:00'
  and event_time <  timestamp '2026-08-01 00:00:00'
group by event_id
having count(*) > 1
order by copies desc, event_id
limit 20;

WHERE не видит алиас, потому что на его шаге алиаса ещё нет

Запрос читается сверху вниз, а выполняется в другом порядке: сначала определяются источники строк и соединения, затем where отбрасывает строки, затем group by собирает группы, having отбрасывает группы, select вычисляет выражения и алиасы, и только потом работают order by и limit. Из этого порядка следуют два правила, которые иначе приходится запоминать как исключения.

Первое: алиас revenue, объявленный в select, на шаге where ещё не существует — как колонка он появится только через три шага. Поэтому where revenue >= 3000 в PostgreSQL даёт ошибку column "revenue" does not exist, а не тихий неверный результат. DuckDB в песочнице алиас подставляет и останавливается шагом позже: WHERE clause cannot contain aggregates. В order by тот же алиас работает, потому что сортировка идёт после select; в group by он работает в PostgreSQL и MySQL, но не в каждом диалекте, так что опираться на это в общем коде не стоит.

Второе: where выполняется до группировки и физически не может сравнивать агрегат — на его шаге групп ещё нет. Условие по sum, count или avg живёт в having. А если одно и то же выражение нужно и показать, и отфильтровать, и отсортировать, его поднимают в CTE: во внешнем запросе агрегат становится обычной колонкой, к которой применим обычный where.

Дальше деление фильтров становится механическим. Условие относится к отдельной строке — payment_type = 'first', paid_at в периоде, channel in (...) — это where. Условие относится к собранной группе — «каналов, где не меньше 50 оплат», «клиентов с выручкой выше $50» — это having. Ошибка в этом выборе редко бывает безобидной: она либо падает, либо меняет смысл отчёта, не меняя его внешнего вида.

select
  u.channel,
  count(*)       as payments_count,        -- organic 210, referral 126
  sum(p.amount)  as revenue                -- organic 5250, referral 3094
from payments as p
join users as u on u.user_id = p.user_id
where p.paid_at >= date '2026-07-01'       -- уровень строки
  and p.paid_at <  date '2026-08-01'       -- уровень строки
group by u.channel
having count(*) >= 50                      -- уровень группы
   and sum(p.amount) >= 3000               -- уровень группы
order by revenue desc, u.channel;

Фильтр по дате в HAVING даёт тот же ответ и другой счёт

Формально having умеет сравнивать не только агрегаты: условие по колонке, входящей в group by, там тоже допустимо. group by plan, paid_at having paid_at >= date '2026-08-01' выполнится и вернёт правильные строки. По логическому порядку счёт при этом другой: сначала 229 групп по всем дням истории, потом 141 из них выброшена. PostgreSQL 14 и DuckDB лишнюю работу не делают: планировщик сам переносит условие без агрегатов к чтению таблицы, в explain фильтр стоит до агрегации. Но это работа оптимизатора, а не правило языка.

Главная причина держать фильтр периода в where — смысл, а не скорость. Читатель отчёта понимает, за какой период он смотрит, по условиям в where — это место, где принято объявлять границы выборки. Дата, спрятанная в having среди порогов по выручке, делает период невидимым, и через месяц кто-то добавит рядом ещё один фильтр, не заметив первый.

Обратная крайность — having без group by. Это законная конструкция: весь набор считается одной группой, и having sum(amount) > 0 работает как фильтр по итогу. Смысл при этом понятен только автору: одна группа нигде не объявлена, и читать такой запрос приходится через правило языка, а не через текст. Тот же результат в CTE с внешним where читается без подсказок.

Четыре метрики за один проход: CASE WHEN и FILTER

Когда в отчёте нужны активные пользователи, создавшие пространство, создавшие отчёт и сделавшие выгрузку, первый порыв — написать четыре запроса. Проблема не в объёме работы, а в том, что у четырёх запросов четыре набора условий, и они расходятся. В одном период начинается первого июля, в другом — с понедельника; в одном исключены внутренние аккаунты, в другом забыли. Таблица собирается аккуратная, а доли в ней несопоставимы, потому что относятся к разным базам.

Условная агрегация решает это структурно: база фиксируется один раз в where, а дальше каждая метрика — отдельное условие внутри своего агрегата. count(*) filter (where ...) в PostgreSQL и DuckDB и sum(case when ... then 1 else 0 end) в любом диалекте делают одно и то же; filter короче и не требует объяснять, почему единица складывается.

Разница между count(case when ... then 1 end) и sum(case when ... then 1 else 0 end) тоньше, чем кажется. В первом варианте case без else возвращает NULL для непопавших строк, а count пропускает NULL — и получается корректный счётчик. Во втором нули складываются и дают то же число. Но стоит написать count(case when ... then 1 else 0 end) — и счётчик сломается: count посчитает и нули, вернув общее число строк. Эта опечатка не падает и не выглядит подозрительно.

Доли считаются в том же проходе, с двумя оговорками. Делить нужно на nullif(знаменатель, 0), иначе пустая группа уронит запрос делением на ноль. И приводить к дробному типу — cast(... as numeric) или умножение на 1.0, — иначе целочисленное деление в PostgreSQL вернёт 0 вместо 0,42. DuckDB в песочнице делит целые дробно и эту ошибку не покажет.

select
  cast(date_trunc('week', event_time) as date)                             as week,
  count(distinct user_id)                                                  as active_users,
  count(distinct user_id) filter (where event_name = 'workspace_created')  as workspace_users,
  count(distinct user_id) filter (where event_name = 'report_created')     as report_users,
  count(distinct user_id) filter (where event_name = 'export_completed')   as export_users,
  count(*)                filter (where event_name = 'app_open')           as app_open_events,
  round(
    cast(count(distinct user_id) filter (where event_name = 'report_created') as numeric)
    / nullif(count(distinct user_id), 0), 4
  )                                                                        as report_rate
from events
where event_time >= timestamp '2026-07-06 00:00:00'   -- понедельник
  and event_time <  timestamp '2026-08-03 00:00:00'   -- понедельник через четыре недели
group by cast(date_trunc('week', event_time) as date)
order by week;

COUNT(DISTINCT user_id) FILTER: числитель обязан быть подмножеством знаменателя

Условная агрегация даёт сопоставимость, но не проверяет её. Проверка — отдельная арифметика, и она простая: каждый числитель должен быть подмножеством знаменателя, а значит не превышать его. За те же четыре недели на учебной базе, без разбивки по неделям: 2 777 зарегистрированных к концу периода, 2 720 активных, 869 создавших пространство, 557 создавших отчёт. Неравенство 557 ≤ 869 ≤ 2 720 ≤ 2 777 выполняется — и это первое, что стоит проверить после сборки, потому что нарушение сразу называет причину.

Нарушения бывают двух видов. Первый: числитель считается по другому набору строк, чем знаменатель — например, активированных берут из всех событий, а активных только из мобильного приложения. Тогда активированных может оказаться больше активных, и доля перевалит за 100%. Второй: числитель и знаменатель считают разные объекты. count(*) filter (...) в числителе и count(distinct user_id) в знаменателе дадут «конверсию» больше единицы просто потому, что один человек совершил несколько действий: в блоке выше это 9 916 событий app_open на 2 720 активных. Смешивать count(*) и count(distinct) в одной доле можно только осознанно, и тогда подпись должна называть объект: «открытий на активного пользователя», а не «конверсия».

Отдельная развилка — окно события, и SQL её не решает. «Активация» может означать «ключевое действие в день регистрации», «в течение семи дней» и «когда-нибудь». Три определения дают три разных числа на одних данных, и все три корректны. На учебной базе этого не увидеть: отчёт там создают только в день регистрации. Поэтому окно пишется в название колонки или в комментарий рядом: activated_7d, а не activated. Колонка без окна через квартал превращается в спор, в котором нет правой стороны.

Последняя проверка — на устойчивость к дублям. Добавьте в тестовый набор копию одного события и пересчитайте: count(distinct user_id) не должен измениться, count(*) изменится. Если пользовательская метрика дрогнула, значит где-то в ней считаются строки, а не люди.

Колонки вместо PIVOT: широкий отчёт — это та же условная агрегация

В BI часто нужна одна строка на сегмент и отдельная колонка на каждую метрику. Слово PIVOT для этого не обязательно: стандартного оператора в PostgreSQL нет, а нужная операция уже описана выше. Категорию превращают в колонку тем же filter или case, просто условие теперь проверяет не событие, а значение признака.

Ключевое требование — собрать широкий отчёт поверх одного длинного слоя, а не соединять несколько отдельных агрегатов по ключу сегмента. Соединение даёт две типовые поломки: сегмент, которого нет в одном из агрегатов, теряется целиком при join и приносит NULL при left join; и у каждого агрегата оказывается свой фильтр, который никто не сверял. Один group by поверх общего слоя снимает оба вопроса сразу.

Сумма колонок при этом становится проверкой. В отчёте ниже статус подписки разложен по странам: активных 294 + 105 + 58 + 54 + 4 = 515, с просроченной оплатой 125 + 52 + 33 + 16 + 2 = 228, ушедших 101 + 38 + 19 + 10 + 1 = 169 — ровно те же итоги, что даёт group by status по таблице subscriptions. Если сумма по сегментам меньше общего итога, у части строк признак сегмента пустой. Четыре названные страны дают 4 558 пользователей из 4 613: у 55 страна не заполнена. Из отчёта со списком стран в фильтре они молча выпали бы; им нужна своя строка «не определена», а не исключение из таблицы.

NULL в широкой таблице читается иначе, чем в длинной. Пустая ячейка в колонке churned_subs означает либо «таких не было», либо «условие вообще не проверялось». Поэтому счётчики оборачивают в coalesce(..., 0), а доли оставляют пустыми — ноль в доле утверждал бы, что знаменатель был. И последнее: широкая схема фиксирована. Появится четвёртый статус — он не появится в отчёте сам, его придётся дописать руками. Длинный слой при этом примет новое значение без правок, поэтому его и держат источником, а широкий вид собирают сверху.

with user_state as (
  select
    u.user_id,
    coalesce(u.country, 'не определена')  as country_name,
    s.status                              -- active / past_due / churned, пусто — подписки нет
  from users as u
  left join subscriptions as s on s.user_id = u.user_id
)
select
  country_name,
  count(*)                                     as users_total,
  count(*) filter (where status = 'active')    as active_subs,
  count(*) filter (where status = 'past_due')  as past_due_subs,
  count(*) filter (where status = 'churned')   as churned_subs,
  count(*) filter (where status is null)       as no_subscription,
  round(cast(count(*) filter (where status = 'active') as numeric)
        / nullif(count(status), 0), 3)         as active_share
from user_state
group by country_name
order by users_total desc, country_name;

Проверки, которые ловят неверный агрегат до дашборда

Агрегация почти никогда не падает. Она возвращает число, которое выглядит разумно и отвечает не на тот вопрос. Поэтому проверки здесь арифметические: вы сравниваете полученное с тем, что обязано получиться по определению метрики.

• Зерно результата записано одной фразой, и count(*) на этом зерне равен count(distinct ключ).
• count(*) не изменился после добавления нового join — иначе суммы в запросе больше не про тот объект.
• У каждой денежной колонки рядом посчитано count(*) - count(column): пропуски найдены, а не пропущены.
• Сумма по разбивке совпадает с итогом без разбивки; строки с пустым признаком сегмента не исчезли.
• Каждая доля проверена неравенством «числитель не больше знаменателя» и считает в обеих частях один тип объекта.
• В названии метрики «на клиента» стоит два слоя агрегации, а не один avg по строкам.
• Фильтры периода и технических аккаунтов стоят в where один раз и одинаковы для всех колонок отчёта.
• Окно события указано в имени колонки: activated_7d, paid_30d, а не activated и paid.
• Дубль одного события в тестовом наборе не сдвигает пользовательские метрики.
• Пустая группа даёт 0 там, где ноль означает факт, и NULL там, где данных действительно нет.

Практика: один набор заказов, пять разных ответов

Возьмите вставку из блока про AVG: десять оплаченных заказов от семи клиентов с суммами 1 200, 300, 2 000, 500, 700, 600, 4 800, 900, 1 000, 1 000 и одиннадцатая строка, у которой amount пустой. Посчитайте на ней пять чисел: число строк, число заказов с суммой, число клиентов, средний чек и средний доход на клиента. Сверьте с 11, 10, 7, 1 300 и 1 857 — расхождение означает, что где-то знаменатель не тот, о котором вы думали.

Потом возьмите активные подписки учебной базы и приклейте к ним оплаты. Сумма месячных цен вырастет с $12 595 до $19 350, и задача — объяснить на сколько именно: на цену подписки за каждую оплату сверх первой, всего $6 755. Перепишите запрос так, чтобы оплаты сворачивались до соединения, и убедитесь, что $12 595 вернулись.

Третий шаг — условная агрегация. Соберите по неделям пять колонок: активные, создавшие пространство, создавшие отчёт, сделавшие выгрузку и доля создавших отчёт от активных. Проверьте вложенность подмножеств, затем добавьте через union all копию одного события и посмотрите, какие колонки дрогнули. Те, что дрогнули, считают строки; если среди них есть колонка с подписью «пользователи», её нужно переписать на count(distinct user_id).

И последнее: разверните статусы подписки по странам в широкий вид и сложите строки. Сумма должна совпасть с итогом по таблице. Уберите строку «не определена» — разница в 55 человек и есть пользователи с пустой страной.

Итог: агрегат считает то зерно, которое вы назвали

Семь вопросов из начала решаются одним ходом. count(*) против count(payment_id) — вопрос о том, строка или заполненное значение. distinct против group by — вопрос о том, нужен ли дальше агрегат. where против having — вопрос о том, фильтруется строка или группа. Условная агрегация и раскладка по колонкам — способ задать все вопросы к одной базе сразу, чтобы ответы остались сравнимыми. Во всех случаях сначала определяется объект, потом выбирается функция.

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