Агрегации и аналитические функции

ClickHouse расширяет привычный GROUP BY десятками быстрых аналитических функций.

Агрегатная функция сворачивает множество строк группы в одно значение: сумму, среднее, число уникальных и т. п.

Начнём с привычного

Базовые агрегаты — те же, что в любом SQL, и они работают в нашей песочнице:

CREATE TABLE sales (
  city TEXT,
  amount INTEGER
);
INSERT INTO sales (city, amount) VALUES
  ('Москва', 100), ('Москва', 200),
  ('Казань', 50),  ('Казань', 70), ('Казань', 30);
SELECT city,
       COUNT(*)   AS deals,
       SUM(amount) AS total,
       AVG(amount) AS avg_check
FROM sales
GROUP BY city
ORDER BY total DESC;

Вывод:

Москва | 2 | 300 | 150.0
Казань | 3 | 150 | 50.0

Где начинается специфика ClickHouse

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

SELECT
    count()                       AS rows,
    uniq(user_id)                 AS approx_users,
    uniqExact(user_id)            AS exact_users,
    quantile(0.95)(duration)      AS p95,
    topK(3)(url)                  AS top3_urls
FROM events;

uniq / uniqExact / uniqCombined

Точно посчитать число уникальных значений на миллиардах строк дорого. uniq и uniqCombined дают приближённую оценку числа уникальных по вероятностным алгоритмам (HyperLogLog-подобным) — в разы быстрее и почти без памяти, с маленькой погрешностью. uniqExact — точно, но дороже. Для дашбордов почти всегда хватает приближённого.

quantile

quantile(0.95)(duration) — 95-й перцентиль: «значение, ниже которого 95% наблюдений». Для метрик задержек перцентили важнее среднего, ведь среднее скрывает редкие, но болезненные «хвосты».

topK

topK(N)(col) приближённо находит N самых частых значений — например, топ-страниц или топ-ошибок — без полного подсчёта и сортировки всего.

Сводная памятка

Функция	Что считает
`count()`	число строк
`sum/avg/min/max`	стандартные агрегаты
`uniq(x)`	приближённое число уникальных
`uniqExact(x)`	точное число уникальных (дороже)
`quantile(p)(x)`	перцентиль
`topK(n)(x)`	топ-N частых значений

Как работает под капотом

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

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

Везде uniqExact. На больших данных это дорого; для дашбордов берите uniq/uniqCombined.
Смотреть только на среднее. Для задержек важны перцентили (quantile), а не avg.
Тащить все строки в приложение и считать там. Считайте в ClickHouse — он для этого создан.

Итоги

Базовые агрегаты как в обычном SQL, плюс специальные функции ClickHouse.
uniq/uniqCombined — быстрый приближённый подсчёт уникальных.
quantile — перцентили, topK — частые значения.
Считайте агрегаты в базе, а не в приложении.