Примеры использования state функций в ClickHouse

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

Из документации ClickHouse не всегда легко сразу понять ценность функций для имплементации бизнес-логики, в частности, ценность функции runningAccumulate. Например, несмотря на богатые возможности runningAccumulate, неотформатированный код и имена вида k и sum_k из документации могут немного ввести в заблуждение.

SELECT k, runningAccumulate(sum_k) AS res FROM (SELECT number as k, sumState(k) AS sum_k FROM numbers(10) GROUP BY k ORDER BY k);

Если Вам интересно рассмотреть state функции ClickHouse на паре примеров с более понятной логикой, то добро пожаловать 🙂

State-функции в ClickHouse заканчиваются на State (например, sumState, minState, maxState), и позволяют хранить промежуточные результаты агрегации в запросе.

Получить итоговый результат агрегации можно, например, при помощи sumMerge над полем, в котором хранится sumState. Функция runningAccumulate, в свою очередь позволяет рассчитать накопленный итог по строкам. Рассмотрим пару примеров для runningAccumulate.

Информация о выполнении плана по количеству договоров по дням

Предложим, требуется отчет по дням месяца day, количеству заключенных договоров в день contracts_count и накопленному количеству числа залюченных договоров по дням running_amount. ClickHouse предоставляет прекрасную возможность для получения такого отчета — функция runningAccumulate.

SELECT day, contracts_count, runningAccumulate(sumState_contracts_count) AS running_contracts_count FROM (SELECT number + 1 as day, rand() % 10 AS contracts_count, sumState(contracts_count) AS sumState_contracts_count       FROM numbers(30)       GROUP BY day, contracts_count       ORDER BY day);

Здесь генерируются тестовые данные за 30 дней с помощью таблицы numbers, также генерируется количество договоров в день rand() % 10 AS contracts_count, и также рассчитывается state агрегация sumState(contracts_count) AS sumState_contracts_count. Такой вид агрегации позволяет далее рассчитать накопленный итог по дням в runningAccumulate(sumState_contracts_count) AS running_contracts_count.

Как видно, относительно сложная логика подсчета накопленного итога легко реализуется при помощи sumState и runningAccumulate в ClickHouse.

Информация о количестве договоров после выполнения месячного плана в 50 договоров по месяцам и дням

Рассмотрим интересную возможность runningAccumulate — аккумулирование в рамках группы. Предположим, требуется отчет по месяцам и дням месяцев по накопленному количеству заключенных договоров для дней, которые идут после достижения плана в 50 заключенных договоров. Рассматриваются 366 дней, начиная с 1 января 2024 года. Несмотря на то, что условие может показаться относительно сложным, для решения задачи аккумулирования в рамках месяца достаточно всего лишь передать поле month, по которому идет группировка, в runningAccumulate(sumState_contracts_count, month) AS running_contracts_count.

SELECT month,        day,        contracts_count,        date,        runningAccumulate(sumState_contracts_count, month) AS running_contracts_count FROM (          SELECT toMonth(date)                 as month,                 toDayOfMonth(date)            as day,                 toDate('2024-01-01') + number as date,                 rand() % 10                   as contracts_count,                 sumState(contracts_count)     AS sumState_contracts_count          FROM numbers(366)          GROUP BY contracts_count, date          ORDER BY date ASC          ) WHERE running_contracts_count > 50;

Часть результатов запроса выглядит следующим образом.

Как видно, относительно сложная задача аккумулирование просто решается с sumState и runningAccumulate в ClickHouse.

Примеры доступны по ссылке. Надеюсь, краткий обзор sumState и runningAccumulate был интересен 🙂