Поиск по словосочетанию оконные функции в Clickhouse не дает вразумительных результатов. Эта статья является попыткой обобщить разрозненные данные из интернета, примеры с ClickHouseMeetup и собственный опыт.
Оконные функции синтаксис
Напомню синтаксис оконных функций и вид результата, который мы получим. В примерах будем использовать диалект Standart SQL Google BigQuery. Вот ссылка на документацию об оконных функциях (в документации они называются analytic function более точный перевод звучит как аналитические функции). А здесь сам список функций.
Обобщенный синтаксис выглядит так:
analytic_function_name ( [ argument_list ] ) OVER over_clauseover_clause: { named_window | ( [ window_specification ] ) }window_specification: [ named_window ] [ PARTITION BY partition_expression [, ...] ] [ ORDER BY expression [ { ASC | DESC } ] [, ...] ] [ window_frame_clause ]window_frame_clause: { rows_range } { frame_start | frame_between }rows_range: { ROWS | RANGE }
Разберем по шагам:
- Оконная функция применяется к набору записей, определенному в выражении over_clause,
- Набор записей определяется конструкцией PARTITION BY. Здесь
можно перечислить одно или несколько полей, по которым будет
определяться набор записей. Работает аналогично GROUP BY.
Сортировка записей в рамках набора определяется с помощью ORDER BY. - На предварительно определенный набор записей можно
дополнительно наложить ограничение в виде окна. Окно можно
определить статически. Например, можно брать в качестве окна можно
брать 5 записей, 2 до и 2 после текущей записи и саму текущую
запись. Выглядеть это будет так: ROWS BETWEEN 2 PRECEDING AND 2
FOLLOWING.
Пример конструкции для задания динамически определяемого окна выглядит так RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Эта конструкция определяет окно от первой до текущей записи в соответствии с заданным порядком сортировки.
В качестве примера можно рассмотреть вычисление кумулятивной суммы (пример из документации)
SELECT item, purchases, category, SUM(purchases) OVER ( PARTITION BY category ORDER BY purchases ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS total_purchasesFROM Produce
Результат:
+-------------------------------------------------------+| item | purchases | category | total_purchases |+-------------------------------------------------------+| orange | 2 | fruit | 2 || apple | 8 | fruit | 10 || leek | 2 | vegetable | 2 || cabbage | 9 | vegetable | 11 || lettuce | 10 | vegetable | 21 || kale | 23 | vegetable | 44 |+-------------------------------------------------------+
Что можно сделать в Clickhouse
Попробуем повторить этот пример в ClickHouse. Конечно, в ClickHouse есть функции runningAccumulate, arrayCumSum и groupArrayMovingSum. Но в первом случае нужно определять состояние в подзапросе (подробнее), а во втором случае функция возвращает array, который затем нужно развернуть.
Мы сконструируем самый общий запрос. Сам запрос может выглядеть так:
SELECT items, summ as purchases, category, sumArray(cum_summ) as total_purchasesFROM (SELECT category, groupArray(item) AS items, groupArray(purchases) AS summ, arrayMap(x -> arraySlice(summ, 1, x), arrayEnumerate(summ)) AS cum_summ FROM (SELECT item, purchases, category FROM produce ORDER BY category, purchases) GROUP BY category) ARRAY JOIN items, summ, cum_summGROUP BY category, items, summORDER BY category, purchases
Разберем по шагам:
- Сначала конструируем подзапрос, внутри которого происходит нужная сортировка данных (ORDER BY category, purchases). Сортировка должна соответствовать полям в выражениях PARTITION BY и ORDER BY оконной функции.
- Далее выполняем группировку в массивы всех полей, которые есть
в запросе, но не упомянуты в PARTITION BY. В нашем случае поле item
будет свернуто в массив на этом шаге и развернуто без изменений на
следующем.
Поле purchases также будет свернуто на этом шаге и развернуто на следующем, но его агрегат summ будет использован в конструкторе нового поля. - Самое интересное использование функции ArrayMap. Эта
функция возвращает массив, полученный на основе результатов
применения функции func к каждому элементу массива
arr.
В нашем случае массив arr это массив массив [1, 2, , length(summ)], который генерирует функция arrayEnumerate.
А функция func это arraySlice(summ, 1, x), где единственным аргументом выступает x элемент массива arr, описанного выше. Функция возвращает массив из элементов массива summ начиная с первого и длиной x. Таким образом, в поле cum_sum мы получим массив, в котором каждый элемент представляет собой также массив, сумма элементов которого и будет искомой оконной функцией.
Применяя ArrayMap с функцией arrayEnumerate мы определяем окно, ограничивающее значения, над которыми будет работать агрегатная функция. Ниже пример окна статического размера (размер 3), аналог конструкции ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING.arrayMap(x -> arraySlice(summ, if(x-1 > 0, x-1, 1), if(x-1 > 0, 3, 2)), arrayEnumerate(summ))
Здесь нужно сделать замечание, относительно функций по работе с массивами. Есть 2 класса таких функций в ClickHouse:- Функции высшего порядка особенностью этих функций является невозможность вызова функции внутри функции. То есть нельзя напрямую использовать, скажем, функцию arrayMap в качестве аргумента функции arrayFilter. Но выход есть можно на предыдущей (или последующей без разницы) строке задать синоним (alias) для результата выполнения arrayMap, а затем этот синоним использовать в качестве аргумента функции arrayFilter в том же запросе.
- Функции по работе с массивами здесь ограничений нет. Можно легко использовать, например, функцию arrayReverse в качестве аргумента функции arraySlice.
- Последний шаг мы должны развернуть массивы в таблицу с помощью ARRAY JOIN. Также мы должные применить агрегатную функцию sum с модификатором -Array (в результате агрегатная функция выглядит как sumArray) к результату, возвращаемому функцией ArrayMap.
Вывод
Есть возможность эмулировать работу оконных функций в ClickHouse. Не очень быстро и не очень красиво. Кратко пайплан состоит из 3-х шагов:
- Запрос с сортировкой. На этом шаге идет подготовка набора записей.
- Группировка в массивы и выполнение операций с массивом. На этом шаге определяется окно нашей оконной функции.
- Обратное развертывание в таблицу с применение агрегатных функций.
