Работу с колоночными базами данных я начал с BigQuery. Когда пришлось “переехать” на Clickhouse я был неприятно удивлен фактом отсутствия полноценных оконных функций. Есть, конечно, множество функций по работе с массивами, функций высшего порядка и прочие функции (одна функция runningDifferenceStartingWithFirstValue чего стоит). Сразу на ум приходит победитель 1999 года на звание самого длинного слова Donaudampfschifffahrtsgesellschaftskapitanswitwe. Что в переводе с немецкого означает «вдова капитана пароходного общества на Дунае».

Поиск по словосочетанию “оконные функции в Clickhouse” не дает вразумительных результатов. Эта статья является попыткой обобщить разрозненные данные из интернета, примеры с ClickHouseMeetup и собственный опыт.

Оконные функции — синтаксис

Напомню синтаксис оконных функций и вид результата, который мы получим. В примерах будем использовать диалект Standart SQL Google BigQuery. Вот ссылка на документацию об оконных функциях (в документации они называются analytic function — более точный перевод звучит как аналитические функции). А здесь сам список функций.

Обобщенный синтаксис выглядит так:

analytic_function_name ( [ argument_list ] ) OVER over_clause
over_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 }

Разберем по шагам:

1. Оконная функция применяется к набору записей, определенному в выражении over_clause,
2. Набор записей определяется конструкцией PARTITION BY. Здесь можно перечислить одно или несколько полей, по которым будет определяться набор записей. Работает аналогично GROUP BY.
   Сортировка записей в рамках набора определяется с помощью ORDER BY.
3. На предварительно определенный набор записей можно дополнительно наложить ограничение в виде окна. Окно можно определить статически. Например, можно брать в качестве окна можно брать 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_purchases
FROM 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_purchases
FROM (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_summ
GROUP BY category, items, summ
ORDER BY category, purchases

Разберем по шагам:

1. Сначала конструируем подзапрос, внутри которого происходит нужная сортировка данных (ORDER BY category, purchases). Сортировка должна соответствовать полям в выражениях PARTITION BY и ORDER BY оконной функции.
2. Далее выполняем группировку в массивы всех полей, которые есть в запросе, но не упомянуты в PARTITION BY. В нашем случае поле item будет свернуто в массив на этом шаге и развернуто без изменений на следующем.
   Поле purchases также будет свернуто на этом шаге и развернуто на следующем, но его агрегат summ будет использован в конструкторе нового поля.
3. Самое интересное — использование функции 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:
   
   
   • Функции высшего порядка — особенностью этих функций является невозможность вызова функции внутри функции[edited]возможность вызова функции внутри функции — см. комментарии[/edited]. То есть нельзя напрямую использовать, скажем, функцию arrayMap в качестве аргумента функции arrayFilter. Читаемость кода будет снижаться при вызове ФВП внутри ФВП. Но выход есть — можно на предыдущей (или последующей — без разницы) строке задать синоним (alias) для результата выполнения arrayMap, а затем этот синоним использовать в качестве аргумента функции arrayFilter в том же запросе.
   • Функции по работе с массивами — здесь ограничений нет. Можно легко использовать, например, функцию arrayReverse в качестве аргумента функции arraySlice.
   
   
4. Последний шаг — мы должны развернуть массивы в таблицу с помощью ARRAY JOIN. Также мы должные применить агрегатную функцию sum с модификатором -Array (в результате агрегатная функция выглядит как sumArray) к результату, возвращаемому функцией ArrayMap.

Вывод

Есть возможность эмулировать работу оконных функций в ClickHouse. Не очень быстро и не очень красиво. Кратко пайплан состоит из 3-х шагов:

1. Запрос с сортировкой. На этом шаге идет подготовка набора записей.
2. Группировка в массивы и выполнение операций с массивом. На этом шаге определяется окно нашей оконной функции.
3. Обратное развертывание в таблицу с применение агрегатных функций.