Примеры использования 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;Часть результатов запроса выглядит следующим образом.
Пример результатов выполнения запроса о количестве договоров после выполнения месячного плана в 50 договоров по месяцам и дням
Как видно, относительно сложная задача аккумулирование просто решается с sumState и runningAccumulate в ClickHouse.
Примеры доступны по ссылке. Надеюсь, краткий обзор sumState и runningAccumulate был интересен :)
