Менее известные особенности Clickhouse. Часть 2

Составные агрегационные функции

Агрегационные функции в Clickhouse можно расширять суффиксами, которые меняют поведение этой функции.

Классический пример, суффикс -If, позволяет учитывать в агрегационной функции только значения, соответствующие условию:

SELECT topKIf(10)(sales, date >= '2024-01-01')
FROM sales_table;

Вычислит топ 10 значений продаж в 2024 году.

Еще один пример: суффикс -Array повзоляет применить агрегационную функцию на все элементы в массивах, во всем столбце. При этом это будет аналогично использованию arrayJoin (аналог Unnest в PostgreSQL) с последующим применением агрегационной функции без суффикса.

-Array можно применять вместе с суффиксом -If, что также позволит отсеять значения по условию.

SELECT sumArrayIf(sales_array, region = 'Europe')
FROM world_sales;

Позволит просуммировать все значения продаж по Европе, если их значения хранятся в массиве. Запрос выше является более простым способом применения следующего запроса:

SELECT
    sum(arrayJoin(sales_array)) t
FROM world_sales
WHERE region = 'Europe`;

Однако мы не вседа хотим использовать arrayJoin, так как он изменит структуру результата.

Суффиксы можно комбинировать практически не ограниченно (влоть до конструкции вроде sumForEachStateIfArrayIfState), порядок указания может быть важен, однако, к сожалению, не всегда прозрачно указан в документации.

Другие суффиксы можно посмотреть в документации к Clickhouse.

Лямбда-функции и пользовательские функции

Кликхаус обладает набором функций, которые являеются функциями высшего порядка. Такие функции могут принимать в качестве аргументов только лямбда-функции.

WITH ['<b>Hello</b>', '<i>World!</i>'] AS html_array
SELECT
    arrayMap(s -> extractTextFromHTML(s), html_array)

Лямбда-функции имеют следующий синтаксис:

• x -> 2 * x
• (x, y) -> x + y

С левой стороны перечисляются аргументы, если аргументов больше одного, их необходимо заключать в круглые скобки. После символа -> идет выражение, использующее объявленные аргументы.

За счет лямбда-функций пользователь может объявлять собственные функции (UDF).

• для этого используется CREATE FUNCTION

CREATE FUNCTION mergeCityAddress AS (city_str, address_str) -> concat(city_str, ' ', 'address_str);
SELECT mergeCityAddress(city, address) FROM company_address;

После объявления их можно использовать как регулярные функции из стандартного набора Clickhouse.

У пользовательских функций есть ряд ограничений:

• Имя функции не должно пересекаться с набором системных функций.
• Рекурсивные функции не допускаются.
• В выражении допускается использовать только перечисленные в параметрах переменные.

Подробнее про пользовательские функции в документации