Условие внутри агрегата: countIf и sumIf — Курс SQL

Чему научишься

переносить условие ИЗ WHERE внутрь : countIf, sumIf, avgIf, uniqIf
собирать сводку из пяти метрик за один проход вместо пяти запросов
называть цену этого приёма тремя числами, а не словом «быстрее»
различать count(условие) и countIf(условие) — это разные вопросы, и первый почти всегда не тот, который ты хотел задать

Второй вопрос смотрителя

Тот же день, сорок минут спустя. Смотритель возвращается с уточнением: теперь визиты нужны не общей кучей, а по устройствам — терминал зала, планшет или очки.

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

В журнале предшественника ответ уже собран пятью отдельными запросами. Каждый запрос считает одну метрику и заново читает таблицу визитов. Числа верные, но один и тот же набор данных приходится проходить снова и снова.

Значит, вопрос здесь не в том, как получить ответ. Нужно понять, сколько стоит такой способ и можно ли получить те же числа за один проход.

Аварии здесь нет — только выбор между двумя рабочими решениями. Такие ситуации в курсе будут встречаться чаще, чем поломки: дежурный по выдаче большую часть смены не чинит, а выбирает.

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

Пять метрик за один проход по таблице визитов. Условие стоит не в WHERE, а внутри каждого агрегата: так в одной строке можно одновременно посчитать метрики для разных условий.

Почему это не украшение

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

В строчной базе эта разница часто не так заметна: строка всё равно читается целиком. В колоночном хранилище повторное чтение нужной колонки видно гораздо лучше — и цену можно измерить.

Каждый из пяти запросов предшественника заново читает таблицу. В итоге движок пять раз читает одни и те же 5272 строки. Если перенести условие внутрь агрегата, строки читаются один раз, а дальше движок за тот же проход вычисляет пять агрегатов.

Следующая ячейка покажет разницу в трёх числах: сколько кусков, строк и засечек понадобилось каждому варианту.

Ловушка, из-за которой отчёты и расходятся

Условие внутри агрегата важно не только для производительности. Здесь легко написать запрос, который выполнится без ошибки, но посчитает совсем не то.

count(x) и countIf(x) — не синонимы. count(x) считает все значения x, кроме , а результат логического выражения здесь никогда не равен NULL: и «истина», и «ложь» — обычные значения.

Поэтому count(bounced = 1) вернёт ровно столько же, сколько count(), — все визиты подряд. Ошибки не будет, подозрительной подсветки тоже. Запрос вернёт вполне правдоподобное число, поэтому такую ошибку обычно находят позже — когда два отчёта перестают сходиться.

Условная агрегация уже встречалась в курсе SQL в форме SUM(CASE WHEN … THEN 1 END). Здесь новое ровно одно: теперь мы смотрим не только на результат вычисления, но и на цену выбранной формы запроса.

КВЕРИ: Пять запросов и один дают одинаковый ответ. Разница — сколько раз ты отправил зал за одними и теми же строками. Лень свести пять запросов в один тоже имеет тариф.

Два способа получить один и тот же ответ. Ниже — пять отдельных запросов, склеенных в один; выше — та же сводка за один проход. Сравни, сколько данных пришлось прочитать в каждом случае.

Пять запросов читают таблицу пять раз — 26 360 строк; один запрос проходит её однажды — 5272 строки, а пять агрегатов вычисляются уже во время этого прохода.

Найди и исправь ошибку

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

Подзапрос в списке колонок исполняется отдельно и ничего не знает о GROUP BY внешнего запроса. Поэтому он возвращает один и тот же результат для каждой строки. Перенеси условие внутрь агрегата: countIf, uniqIf, sumIf, avgIf.

Вопрос с собеседования

Как это спрашивают на собеседовании

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

Дальше часто проверяют, понимаешь ли ты механику, а не просто знаешь названия функций. Спрашивают: «чем countIf(x > 0) отличается от count(x > 0)?» Ждут, что ты объяснишь: второй считает все значения, кроме , а не только истинные, поэтому вернёт число всех строк группы.

Третий вопрос встречается реже: «а sum(if(cond, x, 0)) — то же самое?» Для чисел результат будет тем же, но sumIf прямо выражает условие и легче читается.

Проверь себя

В сводке по устройствам колонка написана как count(bounced = 1). Что она покажет?

Закрепление: реши задачи

Решено 0 из 4 · для зачёта достаточно 3

Главное из урока

вопрос	приём	цена
Пять метрик по устройствам	пять отдельных запросов	5 кусков / 26 360 строк / 5 засечек
То же самое	один проход, условие внутри	1 кусок / 5272 строки / 1 засечка

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

Главная мысль урока шире названных функций. Агрегат в ClickHouse умеет принять условие внутрь. countIf, sumIf, avgIf, uniqIf — не четыре случайные функции, а проявление одного общего правила.

В следующем уроке разберём общее правило, из которого выросли эти четыре функции.