Существуют базы данных различного вида, и для колоночных баз данных, таких как, например, 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.
![Пример результатов выполения запроса с информацией о выполнении плана по количеству договоров по дням Пример результатов выполения запроса с информацией о выполнении плана по количеству договоров по дням](https://habrastorage.org/getpro/habr/upload_files/1f8/799/661/1f8799661d9bfef891a320df781d481f.png)
Как видно, относительно сложная логика подсчета накопленного итога легко реализуется при помощи 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 договоров по месяцам и дням Пример результатов выполнения запроса о количестве договоров после выполнения месячного плана в 50 договоров по месяцам и дням](https://habrastorage.org/getpro/habr/upload_files/542/39a/ed5/54239aed547e35cecc6dc3926c830965.png)
Как видно, относительно сложная задача аккумулирование просто решается с sumState и runningAccumulate в ClickHouse.
Примеры доступны по ссылке. Надеюсь, краткий обзор sumState и runningAccumulate был интересен :)
Комментарии (2)
koanse Автор
10.09.2024 07:55Спасибо, да, баги - это другая сторона ClickHouse :)
Интересно, что в русской документации не упоминается DEPRICATED, только warning
runningAccumulate не DEPRICATED в https://clickhouse.com/docs/ru/sql-reference/functions/other-functions В английской документации есть DEPRICATED
runningAccumulate не DEPRICATED в https://clickhouse.com/docs/en/sql-reference/functions/other-functions
Sablast
Откажитесь от неё и на всякий случай перепроверьте свои результаты.
Function runningAccumulate is deprecated since its usage is error-prone (see docs).Please use proper window function or set `allow_deprecated_error_prone_window_functions` setting to enable it. (DEPRECATED_FUNCTION)