Аналитик открывает сводную таблицу в Excel — продажи по регионам за год — и нажимает «Обновить». Проходит двадцать секунд. Он идёт за кофе.
Данных много: миллиард строк чеков в ClickHouse. Инженеры данных знают об этом и ещё месяц назад построили агрегат — маленькую таблицу «продажи по магазинам по дням», из которой тот же отчёт собирается за доли секунды. Агрегат работает, его видно в базе, он обновляется вместе с ней.
Аналитик нажимает «Обновить». Двадцать секунд.
Эта статья о том, почему так происходит, что с этим делать, и почему ответ в итоге лежит не в базе.
Как сводная Excel попадает в ClickHouse
Для дальнейшего нужно понимать одно: сводная таблица Excel ничего не считает сама. Каждое действие пользователя — раскрыть регион, поставить фильтр, добавить колонку — превращается в запрос к серверу, а сервер превращает его в SQL и отправляет в базу. Сводная на скриншоте ниже работает поверх ClickHouse через XLTable — сервер, который отдаёт Excel кубы из аналитических баз.
Вот в какой SQL такая сводная обычно превращается — это распространённая форма запроса, в которую BI-инструменты переводят отчёт «регионы по месяцам», в упрощённом виде:
SELECT st.region_name, toStartOfMonth(s.date) AS month, sum(s.amount) AS amount FROM sales AS s JOIN stores AS st ON st.store_id = s.store_id -- присоединили справочник WHERE st.region_name IN ('Central', 'South') -- отфильтровали по названию AND s.date >= '2025-01-01' AND s.date < '2026-01-01' GROUP BY st.region_name, month -- сгруппировали по названию
В жизни запрос длиннее — сводная просит ещё итоги и другие справочники, — и в этой форме, на нашем стенде, он выполняется те самые двадцать секунд. Но для разбора достаточно упрощённого варианта: справочник присоединяется к фактам, фильтр и группировка идут по названию.
Важно: это не ошибка, а форма из учебника. Так пишут BI-инструменты, так пишет человек, и ровно так напишет языковая модель — она училась на таком SQL. И до недавнего времени эта форма была лучшей из возможных: оптимизатор сам проталкивает фильтры к таблице фактов, JOIN с маленьким справочником стоит копейки, а подзапросы, наоборот, старые движки оптимизировали плохо — BI-инструменты избегали их сознательно. Три секунды на миллиард строк без всяких агрегатов — для базы отличный результат.
Правила поменяли сами базы. Проекции в ClickHouse, переписывание запросов на materialized views в StarRocks, smart tuning в BigQuery — всё это появилось и созрело в 2021–2024 годах. И оказалось, что форма, оптимальная для «честного» чтения таблицы, мешает базе увидеть предагрегат. Запомните эту форму — мы к ней вернёмся.
Что такое предагрегат и кто его подставляет
Предагрегат — это заранее посчитанная таблица. Вместо миллиарда чеков — полмиллиона строк «магазин, день, сумма продаж, количество». Ответить из неё на вопрос «продажи по регионам по месяцам» — значит прочитать полмиллиона строк, а не миллиард. В разных базах это называется по-разному: materialized view (материализованное представление, дальше — MV), projection, rollup, summary table, — но суть одна.
Есть только одна проблема: кто-то должен подставить предагрегат вместо большой таблицы. Запрос-то написан к таблице sales. Вариантов два:
База подставляет сама. Оптимизатор смотрит на запрос, видит, что на него можно ответить из предагрегата, и молча переписывает запрос. Пользователь и BI-инструмент ничего об этом не знают. Так умеют ClickHouse (проекции), StarRocks, BigQuery, Snowflake.
Подставляет тот, кто пишет запрос. База хранит предагрегат как обычную таблицу, обращаться к нему надо по имени. Так устроено в Databricks, Trino, Greenplum. В DuckDB предагрегатов как механизма нет вообще.
Полная таблица по восьми базам — в приложении. Пока важно другое: у нас ClickHouse, он умеет подставлять сам. Агрегат есть. Почему не сработало?
Эксперимент: проекция есть, а база её не видит
Стенд: таблица продаж на 1 миллиард строк, 500 магазинов, 5000 товаров, 32 месяца, 7,5 ГБ на диске. ClickHouse 25.3 на 12 ядрах. Добавим проекцию — так в ClickHouse называется встроенный предагрегат, который живёт внутри таблицы и обновляется вместе с ней:
ALTER TABLE sales ADD PROJECTION p_store_day ( SELECT store_id, date, sum(amount), sum(qty), count() GROUP BY store_id, date ); ALTER TABLE sales MATERIALIZE PROJECTION p_store_day;
Полмиллиона строк, 5 мегабайт, построилась за 16 секунд. Теперь запускаем запрос сводной — тот самый, из предыдущего раздела — и смотрим план выполнения:
Aggregating Join ReadFromMergeTree (sales) ← читает таблицу фактов Granules: 45752/45752 ← целиком за год ReadFromMergeTree (stores)
Проекция не использована. 375 миллионов строк прочитано, 5 гигабайт, 3 секунды. Как будто её нет.
Причина проста, и она не в ClickHouse. Проекция — это агрегат по одной таблице: «сверни sales по магазину и дню». А в запросе агрегация стоит после JOIN и группирует по колонке из другой таблицы — region_name. С точки зрения оптимизатора это уже не «сверни sales», это «сверни результат склейки двух таблиц». Такого предагрегата у него нет, и он честно идёт в сырые данные.
Обратите внимание: в запросе всё нужное для проекции есть. Фильтр по двум регионам — на самом деле фильтр по 200 магазинам. Группировка по региону — группировка по магазину с последующей склейкой. Но оптимизатор не переписывает запрос так глубоко. Он консервативен, и это нормально: он не знает, что справочник магазинов маленький, а связь «магазин → регион» однозначная.
Сначала свернуть, потом склеить
Раз база не переставляет операции сама, переставим их за неё. Правило одно: сначала свернуть таблицу фактов по ключам, потом присоединить справочники к результату.
SELECT st.region_name, f.month, sum(f.amount) AS amount FROM ( SELECT store_id, toStartOfMonth(date) AS month, sum(amount) AS amount FROM sales WHERE store_id IN (SELECT store_id FROM stores -- фильтр по ключу, WHERE region_name IN ('Central', 'South')) -- не по названию AND date >= '2025-01-01' AND date < '2026-01-01' GROUP BY store_id, month -- свернули по ключам ) AS f JOIN stores AS st ON st.store_id = f.store_id -- справочник — к результату GROUP BY st.region_name, f.month
Внутри — простой запрос к одной таблице: «сгруппируй sales по магазину и месяцу». Именно такое ClickHouse умеет направлять в проекцию:
Aggregating Join Aggregating ReadFromMergeTree (p_store_day) ← читает проекцию Granules: 24/45752 ReadFromMergeTree (stores)
184 тысячи строк вместо 375 миллионов. 37 миллисекунд вместо 3 секунд.
Тот же отчёт, та же проекция в таблице, тот же результат до копейки. Изменилась только форма запроса.
Отдельно стоит отметить: проекция построена по дням (GROUP BY store_id, date), а запрос просит месяцы (toStartOfMonth(date) AS month … GROUP BY store_id, month). ClickHouse досуммировал дневные значения до месячных сам — это называется rollup, и его умеют все четыре базы из «умного» класса, но только для мер, которые можно складывать. К этому вернёмся.
Есть и второй выигрыш, заметный даже без проекции. Фильтр «по названию после JOIN» заставляет базу прочитать всю таблицу за период — названия в ней нет, отбрасывать строки можно только после склейки. Фильтр «по ключу до JOIN» попадает в первичный индекс. Для широкого фильтра (200 магазинов из 500) это даёт 164 миллиона строк вместо 375. Для сводной, отфильтрованной до пяти магазинов, разница уже в сто раз:
375 миллионов строк против 4 миллионов, 1,7 секунды против 34 миллисекунд — и это без всякой проекции. Справочник, который был нужен только для фильтра, теперь вообще не присоединяется к фактам.
И наконец, JOIN. В первой форме справочник присоединяется к 375 миллионам строк, во второй — к двум сотням строк результата. Все четыре ступени на одном графике:
Проекция не помогла первой форме вообще (3,2 с → 3,0 с), а второй — в 18 раз (684 мс → 37 мс).
А если просто денормализовать?
Любой, кто работал с ClickHouse, скажет: не делайте JOIN, положите название региона прямо в таблицу продаж — ClickHouse так и рекомендует. Совет правильный для того, что он оптимизирует, но у аналитического слоя есть причины держать справочники отдельно. Атрибуты меняются: магазин переводят в другой регион, товар — в другую категорию, и переписывать ради этого миллиард строк фактов никто не станет. Атрибутов десятки, и все в факт не положишь. Модель общая для нескольких инструментов. Права доступа и иерархии живут в справочниках, а не в чеках.
Но главное — тезис статьи от денормализации не зависит. Положим region_name прямо в sales. Запрос станет однотабличным, и всё равно проекция по (store_id, date) его не обслужит: база не знает, что регион однозначно определяется магазином, и для неё GROUP BY region_name — это другой агрегат. Проекцию придётся строить ровно по тем колонкам, по которым группируют, и держать по одной на каждое сочетание. Знание «регион зависит от магазина, а месяц — от дня» — это снова семантика, которой у базы нет. К этому мы ещё вернёмся.
И наконец, у ClickHouse есть собственный способ сделать то же, что делает наш подзапрос, — словари. Свернуть факты по ключу магазина и получить название региона через dictGet поверх результата — это буквально «сначала свернуть, потом склеить», только без слова JOIN. Так что описанная форма запроса не спорит с рекомендациями ClickHouse, она им следует.
То же самое в других базах
Мы измеряли на ClickHouse, но правило шире. Посмотрим на четыре базы, которые умеют подставлять предагрегат сами.
Snowflake. Materialized view может быть построена только по одной таблице — JOIN в определении запрещён прямо в документации. Значит, чтобы оптимизатор её подставил, в запросе должна быть изолированная агрегация по этой таблице. Та же форма.
BigQuery. Materialized view с автоподстановкой (Google называет это smart tuning) должна использовать «тот же набор базовых таблиц, что и запрос». Если MV построена по sales, а запрос склеивает sales со stores и группирует по региону — набор таблиц другой. Та же форма.
ClickHouse — мы уже видели: проекция строго однотабличная.
StarRocks — единственное исключение. Его асинхронные materialized views переписывают запросы с JOIN: оптимизатор умеет сопоставить «склеили и свернули» с «свернули и склеили» и сам делает нужную перестановку. На StarRocks обе формы запроса работают одинаково хорошо. Но и вторая форма ему не мешает — а значит, её можно использовать как единую для всех.
Вывод: в трёх из четырёх «умных» баз предагрегат однотабличный, и одна и та же форма запроса открывает его во всех четырёх. Это не особенность ClickHouse — это свойство того, как оптимизаторы сопоставляют запрос с предагрегатом.
А что с остальными четырьмя? В Databricks, Trino и Greenplum materialized views есть, но обращаться к ним надо по имени: никакая форма запроса к sales не заставит базу заглянуть в агрегат. В DuckDB агрегат — это просто таблица, которую вы пересобираете скриптом. Здесь подставлять должен тот, кто генерирует SQL, — BI-инструмент. И вот тут начинается самое интересное.
Чего база не знает
Представьте, что база — даже самая умная — подставляет предагрегат идеально. Все ли вопросы сводной она сможет из него ответить?
Нет. Потому что часть знаний о данных в базе просто отсутствует:
Иерархии. Что дни складываются в месяцы, месяцы в кварталы, а магазины в регионы — это знает аналитик и модель данных. База видит колонки.
Неаддитивные меры. Сумму продаж можно досуммировать из дневного агрегата в месячный. Средний чек — нельзя: среднее из средних — ошибка. Из агрегата его можно получить, только если там лежат отдельно сумма и количество. Остатки на складе нельзя складывать по дням вообще — нужен остаток на последний день.
Уникальные значения. «Сколько разных покупателей» из агрегата по дням не сложить в месяц — один покупатель приходил тридцать раз. Все четыре «умные» базы решают это либо приближённо (HyperLogLog), либо точно, но специальными структурами (bitmap в StarRocks). BI-слою нужно знать, что именно лежит в агрегате.
Права доступа. Пользователь видит только свой регион. Фильтр прав должен применяться и к сырым данным, и к агрегату — иначе агрегат посчитан по всем, а сводная показывает лишнее.
Свежесть. Агрегат обновился в шесть утра, продажи идут весь день. Можно ли отвечать из агрегата в три часа дня? Зависит от отчёта. Базы здесь ведут себя по-разному: проекция ClickHouse свежа всегда (она физически часть таблицы), BigQuery дочитывает разницу из основной таблицы, StarRocks спрашивает у вас, сколько отставания вы терпите, а Trino по умолчанию молча отдаёт старое, пока кто-то не запустит REFRESH.
Всё это — знание о смысле данных, а не об их хранении. Место для него давно придумано и называется семантическим слоем: описание того, что такое магазин и регион, какие меры складываются, какие уровни есть у времени. И индустрия последовательно переносит задачу предагрегатов именно туда. У Looker это называется aggregate awareness и существует много лет; в Power BI — «агрегации»; AtScale построен вокруг этого целиком. Самый свежий пример — Databricks: в 2026 году ответом на вопрос «как заставить запросы использовать предагрегаты» стала не новая фича базы, а семантический слой — metric views, для которых движок сам подбирает материализацию (пока в статусе preview).
Логика везде одна. База хранит и обновляет агрегаты — это она умеет лучше всех. А решает, какой агрегат подходит под этот вопрос, движок запросов, у которого есть семантическая модель: он знает про иерархии, аддитивность, права и свежесть. Ему же проще всего сформировать запрос в форме «сначала свернуть, потом склеить» — той, при которой база подставит свой предагрегат сама, если умеет.
Кто будет писать запросы завтра
До сих пор запросы к базе писал человек — или BI-инструмент по действиям человека. Это меняется: аналитикой начинают заниматься агенты. Модель получает вопрос «почему просели продажи в Поволжье», и дальше сама решает, что спросить у данных.
Два следствия. Первое: агент, которому дали доступ к таблице фактов, напишет ровно ту форму запроса, с которой мы начали, — JOIN справочника, фильтр по названию, GROUP BY по названию. Это самая естественная форма для человека, и для модели тоже: она училась на таком SQL. Второе: агент делает не один запрос, а десятки подряд — уточняет, сужает, перепроверяет, сравнивает с прошлым годом. Если каждый идёт в миллиард строк, это уже не «аналитик ждёт двадцать секунд» — это база, которую положили одним вопросом.
Поэтому агент не должен писать SQL к таблице фактов. Он должен спрашивать семантический слой — «продажи по регионам за 2025 год» — а движок под ним обязан превратить это в оптимальный запрос: с ключами, со свёрткой до склейки, с агрегатом, если он есть. Протокол для такого разговора уже сложился — MCP, и семантические слои его осваивают.
Вот как выглядит тот же вопрос, что задавала сводная Excel, когда его задаёт агент — реальный вызов инструмента query_cube через MCP к кубу на нашем стенде:
{ "cube": "blog_sales", "dimensions": ["Region", "Month"], "measures": ["Sales Amount"], "filters": [ {"level": "Year", "type": "in", "values": ["2025"]}, {"level": "Region", "type": "in", "values": ["Central", "South"]} ] }
И ответ — 24 строки, те же числа, что в сводной:
{"rows": [ {"Region": "Central", "Month": "2025-01", "Sales Amount": 8792166529.94}, {"Region": "Central", "Month": "2025-02", "Sales Amount": 7928933171.09}, ... {"Region": "South", "Month": "2025-12", "Sales Amount": 8784483049.25} ], "row_count": 24}
Ни одной строки SQL. Агент перед этим вызвал describe_cube, узнал, что у куба есть Region, Month и Sales Amount, и задал вопрос в этих терминах. Какой SQL при этом уходит в базу и насколько он оптимален — целиком ответственность движка под MCP. Аналитик, агент и сводная Excel этого не видят и не должны видеть. В этом и цель.
Что проверить у себя
Узнайте класс своей базы (таблица в приложении): подставляет ли она предагрегаты сама, и по какой форме запроса.
Посмотрите план запроса, который генерирует ваш BI-инструмент для типичной сводной. В ClickHouse —
EXPLAIN indexes = 1; если в плане нет имени проекции — она не используется.Если агрегация в запросе стоит после JOIN — предагрегат не сработает почти нигде. Нужна форма «сначала свернуть, потом склеить».
Меры вида «среднее», «уникальные», «остаток» планируйте отдельно: агрегат должен хранить компоненты (сумма и количество, HLL-скетч, остаток на дату), а BI-слой — знать об этом.
Определите политику свежести для каждого агрегата и проверьте, что она совпадает с ожиданиями пользователей отчёта.
Если ваша база из класса «подставляй сам» — ищите aggregate awareness в BI-инструменте. Без него агрегаты будут лежать без дела.
Если к данным подключаете агентов — давайте им семантический слой, а не таблицу фактов.
Приложение А. Восемь баз: кто подставляет предагрегат сам
MV — materialized view, материализованное представление; rollup — ответ на запрос более грубой гранулярности из агрегата более тонкой (месяц из дней).
База | Механизм | Подставляет сама | JOIN в предагрегате | Rollup | Точный count distinct | Свежесть | Ограничения |
|---|---|---|---|---|---|---|---|
ClickHouse | projections | да | нет, одна таблица | да | состояния uniqExact | всегда (часть таблицы) | не работает с FINAL и lightweight delete |
StarRocks | sync / async MV | да | да, 7 типов JOIN | да, с таблицей соответствий функций | да, через bitmap | политика: | JDBC-каталоги без rewrite |
BigQuery | materialized views (smart tuning) | да, только инкрементальные MV | INNER JOIN в определении, но «тот же набор таблиц» | да | нет, только HLL | всегда: дельта дочитывается из таблицы; опц. | 100 MV на таблицу; не для LEFT JOIN/UNION ALL |
Snowflake | materialized views | да, не гарантировано | нет, одна таблица | да | нет, только APPROX | всегда, фоновый сервис за кредиты | только Enterprise Edition и выше |
Databricks | MV в Unity Catalog; metric views | нет (metric views — да, но запрос должен идти к metric view) | да | metric views: для аддитивных мер | нет | по расписанию / TRIGGER ON UPDATE | нужен serverless; материализация metric views — Preview |
Trino | MV в Iceberg-коннекторе | нет (issue #20850 открыт) | да | — | — | GRACE PERIOD, по умолчанию бесконечный | refresh только вручную/внешним планировщиком |
Greenplum | MV (наследие PostgreSQL) | нет (AQUMV — только в форке Cloudberry) | да | — | — | REFRESH вручную; в 7.7 — инкрементально, но без агрегатов | — |
DuckDB | нет | нет | — | — | — | — | MV в roadmap как «Future Work / Looking for Funding» |
Источники — официальная документация по состоянию на сентябрь 2026: ClickHouse projections, ALTER PROJECTION; StarRocks query rewrite, CREATE MATERIALIZED VIEW; BigQuery smart tuning, limitations; Snowflake materialized views, editions; Databricks materialized views, metric views materialization; Trino CREATE MATERIALIZED VIEW, Iceberg connector, issue #20850; Greenplum materialized views, Cloudberry AQUMV; DuckDB roadmap, discussion #3638.
Честные пробелы: формальные правила сопоставления GROUP BY для агрегатных проекций ClickHouse в официальной документации не описаны (rollup «день → месяц» подтверждён нашим экспериментом и сторонними разборами); у BigQuery сценарий «MV покрывает часть JOIN-запроса» как поддерживаемый не описан.
Приложение Б. Как измеряли
ClickHouse 25.3 (Managed Service, Yandex Cloud), один узел, 12 vCPU, 48 ГБ памяти. Таблица sales: 1 000 000 000 строк за 2024-01-01…2026-08-31, PARTITION BY toYYYYMM(date), ORDER BY (store_id, date, product_id), после OPTIMIZE FINAL — 32 парта, 7,56 ГиБ; справочники stores (500 строк) и products (5000 строк). Данные синтетические, без сезонности. Проекция p_store_day — 487 тыс. строк, 4,9 МиБ, материализация 16 с. Каждый запрос выполнялся четыре раза, первый прогон отброшен; время — медиана query_duration_ms из system.query_log, прочитанные строки — read_rows оттуда же; кэш запросов выключен. Скрипты генерации данных и замера вышлю по запросу.
Запрос | Без проекции | С проекцией |
|---|---|---|
JOIN первым, 200 магазинов | 3,25 с · 375 млн строк | 3,03 с · 375 млн строк |
Свернуть первым, 200 магазинов | 0,68 с · 164 млн строк | 37 мс · 184 тыс. строк |
JOIN первым, 5 магазинов | 1,69 с · 375 млн строк | 1,71 с · 375 млн строк |
Свернуть первым, 5 магазинов | 34 мс · 4,3 млн строк | 15 мс · 184 тыс. строк |
