Комбинаторы в 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

Теперь мы имеем таблицы с 10 000 случайных значений, принадлежащих к диапазону от 0 до 255.

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 это будет хэш-таблица.

Пример хранящихся значений для sum и avg

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

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 │
   └───────────────┘

Получаем такой же результат, что и при простом вызове функции uniq.

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

Определим нужный тип данных для столбцов таблицы

В этом нам поможет рассмотренная ранее функция toTypeName.

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