Примеры использования 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_contracts_count. 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 был интересен :)