Комбинаторы в ClickHouse

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

В статье будут рассмотрены не все комбинаторы, но если у вас возникнут вопросы с каким-нибудь из них, смело спрашивайте в комментариях. С полным списком можно ознакомиться по ссылке.

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

CREATE TABLE default.uk_price_paid (     `price` UInt32,     `date` Date,     `postcode1` LowCardinality(String),     `postcode2` LowCardinality(String),     `type` Enum8('other' = 0, 'terraced' = 1, 'semi-detached' = 2, 'detached' = 3, 'flat' = 4),     `is_new` UInt8,     `duration` Enum8('unknown' = 0, 'freehold' = 1, 'leasehold' = 2),     `addr1` String,     `addr2` String,     `street` LowCardinality(String),     `locality` LowCardinality(String),     `town` LowCardinality(String),     `district` LowCardinality(String),     `county` LowCardinality(String) ) ENGINE = MergeTree ORDER BY (postcode1, postcode2, addr1, addr2) SETTINGS index_granularity = 8192

-If комбинатор

Начнем с самых простых, но от этого не менее полезных комбинаторов в ClickHouse. Таким комбинатором является -If. В моей практике он — лидер по частоте использования. Он позволяет указывать дополнительное условие для агрегатной функции. В добавление к полю, по которому мы агрегируем, добавляем условие или можем использовать поле из таблицы, при условии, что она принимает значение 1/0.

К примеру, мы хотим узнать отношение средней цены продажи в определённом районе Лондона, пусть это будет Сити, к средней цене продаж во всем Лондоне с начала 2024 года.

SELECT avgIf(price, district = 'CITY OF LONDON') / avg(price) AS ratio_ FROM uk_price_paid WHERE (date >= '2024-01-01') AND (town = 'LONDON')      ┌────────────ratio_─┐ 1. │ 9.245274507120163 │    └───────────────────┘

Этот комбинатор значительно упрощает написание SQL-запросов и позволяет избежать использования джоинов и подзапросов.

-Distinct

Комбинатор позволяет отбирать для агрегатной функции только уникальные значения.

CREATE TABLE random (     `column1` UInt8 ) ENGINE = Memory

INSERT INTO    random   SELECT    *  FROM    generateRandom(     'column1  UInt8', -- нейминг столбца и требуемый тип данных     42 -- random seed   )  LIMIT 10000

SELECT     sum(column1) AS sum_total,     sumDistinct(column1) AS sum_distinct,     sumDistinctIf(column1, column1 < 10) AS sum_distinct_cond FROM random       ┌─sum_total─┬─sum_distinct─┬─sum_distinct_cond─┐ 1. │   1266960 │        32640 │                45 │    └───────────┴──────────────┴───────────────────┘

Получили сумму арифметической прогрессии в случаях sum_distinct и sum_distinct_cond.

State и производные комбинаторы

Следующие комбинаторы позволяют сохранять и работать с промежуточным состоянием агрегатных функций, это такие комбинаторы, как -State, -Merge и -MergeState.

Начнем с -State. Этот комбинатор позволяет сохранить агрегатную функцию в промежуточном состоянии, из которого в дальнейшем можно получить конечный результат. Сейчас, скорее всего, у вас повис вопрос, а зачем это может понадобиться? Есть несколько ситуаций, когда применяют этот комбинатор:

Внутри AggregatingMergeTree — это движок таблиц из семейства MergeTree, подробнее про который можно прочитать по ссылке. Если описать коротко, основой юзер-кейс этого движка — обновление расчёта метрик по мере появления новых данных. Схему можно посмотреть на рисунке ниже.

В данном случае материализованное представление работает как триггер на вставку данных, но следует помнить, что увеличение количества материализованных представлений, подключенных к одной таблице-источнику, ведёт к увеличению времени вставки данных в таблицу-источник.

Другой кейс — это экономия места (предагрегированные функции в большинстве кейсов занимают значительно меньше места в сравнении с исходными данными) и возможность построения гибкой витрины данных. Метрики в предагрированном состоянии можно хранить и в обычной таблице из семейства MergeTree.

Перейдем к логике работы комбинатора State. Для каждой функции наполнение предагрегированного состояния отличается: если для функции avg это будет значение числителя и знаменателя, то для функции uniq это будет хэш-таблица.

Давайте проверим, как работает комбинатор и как он совмещается с другими комбинаторами, на примере датасета, который мы сгенерировали случайным образом.

SELECT     sumState(column1) AS sum_total,     sumDistinctState(column1) AS sum_distinct,     sumDistinctIfState(column1, column1 < 10) AS sum_distinct_cond FROM random

Если мы выполним это запрос, то не получим конечные цифры, а увидим только бинарные данные. Чтобы завершить агрегацию, нам пригодится комбинатор -Merge или функция finalizeAggregation, и мы получим результат, как считали раньше.

SELECT     sumMerge(sum_total) AS sum_total,     sumDistinctMerge(sum_distinct) AS sum_distinct,     sumDistinctIfMerge(sum_distinct_cond) AS sum_distinct_cond FROM (     SELECT         sumState(column1) AS sum_total,         sumDistinctState(column1) AS sum_distinct,         sumDistinctIfState(column1, column1 < 10) AS sum_distinct_cond     FROM random )      ┌─sum_total─┬─sum_distinct─┬─sum_distinct_cond─┐ 1. │   1266960 │        32640 │                45 │    └───────────┴──────────────┴───────────────────┘ ; -- или SELECT     finalizeAggregation(sum_total) AS sum_total,     finalizeAggregation(sum_distinct) AS sum_distinct,     finalizeAggregation(sum_distinct_cond) AS sum_distinct_cond FROM (     SELECT         sumState(column1) AS sum_total,         sumDistinctState(column1) AS sum_distinct,         sumDistinctIfState(column1, column1 < 10) AS sum_distinct_cond     FROM random )     ┌─sum_total─┬─sum_distinct─┬─sum_distinct_cond─┐ 1. │   1266960 │        32640 │                45 │    └───────────┴──────────────┴──────────────────

Также в случае проектирования таблиц нам пригодится такая функция, как toTypeName, которая подскажет, какой тип данных у наших предагрегированных функций (AggregateFunction), что поможет минимизировать вероятность ошибиться с типом данных.

SELECT     toTypeName(sumState(column1)) AS sum_total,     toTypeName(sumDistinctState(column1)) AS sum_distinct,     toTypeName(sumDistinctIfState(column1, column1 < 10)) AS sum_distinct_cond FROM random     ┌─sum_total─────────────────────┬─sum_distinct──────────────────────────┬─sum_distinct_cond──────────────────────────────┐ 1. │ AggregateFunction(sum, UInt8) │ AggregateFunction(sumDistinct, UInt8) │ AggregateFunction(sumDistinctIf, UInt8, UInt8) │    └───────────────────────────────┴───────────────────────────────────────┴────────────────────────────────────────────────┘

Также стоит помнить, что для алиасов, таких как median и другие, будет использоваться функция, отвечающая за расчёт. Например, для median это quantile. Мы это можем увидеть, выполнив следующий запрос:

SELECT toTypeName(medianState(column1)) FROM random      ┌─toTypeName(medianState(column1))───┐ 1. │ AggregateFunction(quantile, UInt8) │    └────────────────────────────────────┘

Если же говорить про комбинатор -MergeState, то он объединяет промежуточные состояния агрегатных функций в одно. К примеру, это может пригодиться при объединении State-функций типа uniq.

SELECT uniqMerge(vs) FROM (     SELECT uniqMergeState(column1) AS vs     FROM     (         SELECT uniqState(number) AS column1         FROM numbers(1, 5)         UNION ALL         SELECT uniqState(number) AS column1         FROM numbers(4, 10)     ) )     ┌─uniqMerge(vs)─┐ 1. │            13 │    └───────────────┘

SELECT uniq(number) AS vs FROM (     SELECT number     FROM numbers(1, 5)     UNION ALL     SELECT number     FROM numbers(4, 10) )    ┌─vs─┐    │ 13 │    └────┘

Рассмотрим ситуацию, когда нам может пригодиться -MergeState. Допустим, у нас есть две таблицы: в одной хранится предагрегированный таймспент пользователей при взаимодействии с видео, а в другой — предагрегированный таймспент пользователей при взаимодействии с аудио. Мы хотим получить общее предагрегированное состояние для пользователей. Можем сделать avgMergeState над предагригированным таймспентом и посчитать, сколько в среднем пользователи проводят времени в нашем продукте, учитывая данные и с аудио, и с видео.

Перейдем к данным, представленным в начале статьи, и посмотрим, как можно сэкономить место и построить гибкую витрину. Для начала разберемся с тем, что мы хотим посчитать. Пусть это будет средняя цена. Также нужно выбрать измерения, по которым мы хотим агрегировать информацию. Это будет город, район и дата, таким образом, получаем следующий запрос:

SELECT     date,     town,     district,     avgState(price) FROM uk_price_paid GROUP BY     date,     town,     district

SELECT     toTypeName(date),     toTypeName(town),     toTypeName(district),     toTypeName(avgState)(price) FROM uk_price_paid GROUP BY     date,     town,     district limit 1

Теперь нужно создать подходящую таблицу для этих данных:

CREATE TABLE default.uk_price_paid_agg (     `date` Date,     `town` LowCardinality(String),     `district` LowCardinality(String),     `avg_price` AggregateFunction(avg, UInt32) ) ENGINE = MergeTree ORDER BY (town, district, date) SETTINGS index_granularity = 8192

Посмотрим, сколько заняла наша таблица:

SELECT     database,     `table`,     formatReadableSize(sum(data_compressed_bytes + data_uncompressed_bytes)) AS table_size,     sum(rows) AS rows FROM system.parts WHERE (active = 1) AND (database LIKE '%') AND (`table` LIKE '%') GROUP BY     database,     `table` ORDER BY table_size DESC      ┌─database─┬─table─────────────┬─table_size─┬─────rows─┐ 1. │ default  │ uk_price_paid_agg │ 129.76 MiB │  6469138 │ 2. │ default  │ uk_price_paid     │ 1.02 GiB   │ 29145919 │    └──────────┴───────────────────┴────────────┴──────────┘ 

Таблица занимает почти в 8 раз меньше места, чем источник. Благодаря этой витрине, мы можем гибко считать значение средней цены без обращения к данным в таблице-исходнике, что ускорит выполнение запроса и позволит построить гибкие дашборды, используя только агрегированную витрину. То есть, если мы будем фильтровать данные по городам или датам с районами, мы всегда будем получать корректное значение среднего. Это позволяет строить дашборды над источниками с большим количеством измерений для более удобной визуализации.