Регистрация

[SQL, Big Data] Clickhouse — оконные функции, которых нет…

Ответить на тему
Автор Сообщение
news_bot ®

Стаж: 6 лет 9 месяцев
Сообщений: 27286

Создавать темы news_bot ® написал(а)
18-Авг-2020 15:36
Цитировать

Работу с колоночными базами данных я начал с BigQuery. Когда пришлось “переехать” на Clickhouse я был неприятно удивлен фактом отсутствия полноценных оконных функций. Есть, конечно, множество функций по работе с массивами, функций высшего порядка и прочие функции (одна функция runningDifferenceStartingWithFirstValue чего стоит). Сразу на ум приходит победитель 1999 года на звание самого длинного слова Donaudampfschifffahrtsgesellschaftskapitänswitwe. Что в переводе с немецкого означает «вдова капитана пароходного общества на Дунае».
Поиск по словосочетанию “оконные функции в 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 }

Разберем по шагам:
  • Оконная функция применяется к набору записей, определенному в выражении 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_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

Разберем по шагам:
  • Сначала конструируем подзапрос, внутри которого происходит нужная сортировка данных (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-х шагов:
  • Запрос с сортировкой. На этом шаге идет подготовка набора записей.
  • Группировка в массивы и выполнение операций с массивом. На этом шаге определяется окно нашей оконной функции.
  • Обратное развертывание в таблицу с применение агрегатных функций.

 ===========
 Источник:
habr.com
===========
Похожие новости: Теги для поиска: #_sql, #_big_data, #_clickhouse, #_sql, #_analitika (аналитика), #_sql, #_big_data
Профиль  ЛС 
Показать сообщения:     
Ответить на тему

Вы не можете начинать темы
Вы не можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете голосовать в опросах
Вы не можете прикреплять файлы к сообщениям
Вы не можете скачивать файлы

Текущее время: 22-Ноя 20:59
Часовой пояс: UTC + 5