Комбинаторы агрегатных функций

К имени агрегатной функции можно добавить суффикс. Это изменяет способ работы агрегатной функции.

-If

Суффикс -If может быть добавлен к имени любой агрегатной функции. В этом случае агрегатная функция принимает дополнительный аргумент – условие (тип Uint8). Агрегатная функция обрабатывает только строки, которые удовлетворяют условию. Если условие не было выполнено ни разу, она возвращает значение по умолчанию (обычно нули или пустые строки).

Примеры: sumIf(column, cond), countIf(cond), avgIf(x, cond), quantilesTimingIf(level1, level2)(x, cond), argMinIf(arg, val, cond) и так далее.

С помощью условных агрегатных функций вы можете вычислять агрегаты для нескольких условий одновременно, без использования подзапросов и JOINs. Например, условные агрегатные функции могут быть использованы для реализации функциональности сравнений сегментов.

-Array

Суффикс -Array может быть добавлен к любой агрегатной функции. В этом случае агрегатная функция принимает аргументы типа 'Array(T)' (массивы) вместо аргументов типа 'T'. Если агрегатная функция принимает несколько аргументов, они должны быть массивами одинаковой длины. При обработке массивов агрегатная функция работает как оригинальная агрегатная функция для всех элементов массива.

Пример 1: sumArray(arr) - Суммирует все элементы всех массивов 'arr'. В этом примере это могло быть записано проще: sum(arraySum(arr)).

Пример 2: uniqArray(arr) – Считает количество уникальных элементов во всех массивах 'arr'. Это можно сделать проще: uniq(arrayJoin(arr)), но не всегда возможно добавить 'arrayJoin' в запрос.

-If и -Array могут быть комбинированы. Однако 'Array' должен идти первым, затем 'If'. Примеры: uniqArrayIf(arr, cond), quantilesTimingArrayIf(level1, level2)(arr, cond). Из-за этого порядка аргумент 'cond' не будет массивом.

-Map

Суффикс -Map может быть добавлен к любой агрегатной функции. Это создаст агрегатную функцию, которая принимает тип Map в качестве аргумента и агрегирует значения каждого ключа карты отдельно с использованием заданной агрегатной функции. Результат также имеет тип Map.

Пример

CREATE TABLE map_map(
    date Date,
    timeslot DateTime,
    status Map(String, UInt64)
) ENGINE = Log;

INSERT INTO map_map VALUES
    ('2000-01-01', '2000-01-01 00:00:00', (['a', 'b', 'c'], [10, 10, 10])),
    ('2000-01-01', '2000-01-01 00:00:00', (['c', 'd', 'e'], [10, 10, 10])),
    ('2000-01-01', '2000-01-01 00:01:00', (['d', 'e', 'f'], [10, 10, 10])),
    ('2000-01-01', '2000-01-01 00:01:00', (['f', 'g', 'g'], [10, 10, 10]));

SELECT
    timeslot,
    sumMap(status),
    avgMap(status),
    minMap(status)
FROM map_map
GROUP BY timeslot;

┌────────────timeslot─┬─sumMap(status)───────────────────────┬─avgMap(status)───────────────────────┬─minMap(status)───────────────────────┐
│ 2000-01-01 00:00:00 │ {'a':10,'b':10,'c':20,'d':10,'e':10} │ {'a':10,'b':10,'c':10,'d':10,'e':10} │ {'a':10,'b':10,'c':10,'d':10,'e':10} │
│ 2000-01-01 00:01:00 │ {'d':10,'e':10,'f':20,'g':20}        │ {'d':10,'e':10,'f':10,'g':10}        │ {'d':10,'e':10,'f':10,'g':10}        │
└─────────────────────┴──────────────────────────────────────┴──────────────────────────────────────┴──────────────────────────────────────┘

-SimpleState

Если вы применяете этот комбинатор, агрегатная функция возвращает то же значение, но с другим типом. Это SimpleAggregateFunction(...), которое может храниться в таблице для работы с таблицами AggregatingMergeTree.

Синтаксис

<aggFunction>SimpleState(x)

Аргументы

x — Параметры агрегатной функции.

Возвращаемые значения

Значение агрегатной функции с типом SimpleAggregateFunction(...).

Пример

Запрос:

WITH anySimpleState(number) AS c SELECT toTypeName(c), c FROM numbers(1);

Результат:

┌─toTypeName(c)────────────────────────┬─c─┐
│ SimpleAggregateFunction(any, UInt64) │ 0 │
└──────────────────────────────────────┴───┘

-State

Если вы применяете этот комбинатор, агрегатная функция не возвращает результирующее значение (например, количество уникальных значений для функции uniq), а промежуточное состояние агрегации (для uniq это хеш-таблица для подсчета количества уникальных значений). Это AggregateFunction(...), которое может быть использовано для дальнейшей обработки или сохранено в таблице для завершения агрегации позже.

примечание

Обратите внимание, что -MapState не является инвариантом для одних и тех же данных, поскольку порядок данных в промежуточном состоянии может изменяться, хотя это не влияет на прием этих данных.

Чтобы работать с этими состояниями, используйте:

AggregatingMergeTree движок таблиц.
finalizeAggregation функция.
runningAccumulate функция.
-Merge комбинатор.
-MergeState комбинатор.

-Merge

Если вы применяете этот комбинатор, агрегатная функция принимает промежуточное состояние агрегации в качестве аргумента, комбинирует состояния для завершения агрегации и возвращает результирующее значение.

-MergeState

Объединяет промежуточные состояния агрегации так же, как комбинатор -Merge. Однако он не возвращает результирующее значение, а промежуточное состояние агрегации, аналогичное комбинатору -State.

-ForEach

Преобразует агрегатную функцию для таблиц в агрегатную функцию для массивов, которая агрегирует соответствующие элементы массивов и возвращает массив результатов. Например, sumForEach для массивов [1, 2], [3, 4, 5] и [6, 7] возвращает результат [10, 13, 5] после сложения соответствующих элементов массивов.

-Distinct

Каждое уникальное сочетание аргументов будет агрегировано только один раз. Повторяющиеся значения игнорируются. Примеры: sum(DISTINCT x) (или sumDistinct(x)), groupArray(DISTINCT x) (или groupArrayDistinct(x)), corrStable(DISTINCT x, y) (или corrStableDistinct(x, y)) и так далее.

-OrDefault

Изменяет поведение агрегатной функции.

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

-OrDefault может быть использован с другими комбинаторами.

Синтаксис

<aggFunction>OrDefault(x)

Аргументы

x — Параметры агрегатной функции.

Возвращаемые значения

Возвращает значение по умолчанию для возвращаемого типа агрегатной функции, если нечего агрегировать.

Тип зависит от используемой агрегатной функции.

Пример

Запрос:

SELECT avg(number), avgOrDefault(number) FROM numbers(0)

Результат:

┌─avg(number)─┬─avgOrDefault(number)─┐
│         nan │                    0 │
└─────────────┴──────────────────────┘

Также -OrDefault может быть использован с другими комбинаторами. Это полезно, когда агрегатная функция не принимает пустой вход.

Запрос:

SELECT avgOrDefaultIf(x, x > 10)
FROM
(
    SELECT toDecimal32(1.23, 2) AS x
)

Результат:

┌─avgOrDefaultIf(x, greater(x, 10))─┐
│                              0.00 │
└───────────────────────────────────┘

-OrNull

Изменяет поведение агрегатной функции.

Этот комбинатор преобразует результат агрегатной функции в тип данных Nullable. Если у агрегатной функции нет значений для вычисления, она возвращает NULL.

-OrNull может быть использован с другими комбинаторами.

Синтаксис

<aggFunction>OrNull(x)

Аргументы

x — Параметры агрегатной функции.

Возвращаемые значения

Результат агрегатной функции, преобразованный в тип Nullable.
NULL, если нечего агрегировать.

Тип: Nullable(тип возвращаемого значения агрегатной функции).

Пример

Добавьте -orNull в конец агрегатной функции.

Запрос:

SELECT sumOrNull(number), toTypeName(sumOrNull(number)) FROM numbers(10) WHERE number > 10

Результат:

┌─sumOrNull(number)─┬─toTypeName(sumOrNull(number))─┐
│              ᴺᵁᴸᴸ │ Nullable(UInt64)              │
└───────────────────┴───────────────────────────────┘

Также -OrNull может быть использован с другими комбинаторами. Это полезно, когда агрегатная функция не принимает пустой вход.

Запрос:

SELECT avgOrNullIf(x, x > 10)
FROM
(
    SELECT toDecimal32(1.23, 2) AS x
)

Результат:

┌─avgOrNullIf(x, greater(x, 10))─┐
│                           ᴺᵁᴸᴸ │
└────────────────────────────────┘

-Resample

Позволяет вам делить данные на группы, а затем отдельно агрегировать данные в этих группах. Группы создаются путем разделения значений из одного столбца на интервалы.

<aggFunction>Resample(start, end, step)(<aggFunction_params>, resampling_key)

Аргументы

start — Начальное значение всего необходимого интервала для значений resampling_key.
stop — Конечное значение всего необходимого интервала для значений resampling_key. Весь интервал не включает значение stop [start, stop).
step — Шаг для разделения всего интервала на подпериоды. Функция aggFunction выполняется для каждого из этих подпериодов независимо.
resampling_key — Столбец, значения которого используются для разделения данных на интервалы.
aggFunction_params — Параметры aggFunction.

Возвращаемые значения

Массив результатов aggFunction для каждого подпериода.

Пример

Рассмотрим таблицу people с следующими данными:

┌─name───┬─age─┬─wage─┐
│ John   │  16 │   10 │
│ Alice  │  30 │   15 │
│ Mary   │  35 │    8 │
│ Evelyn │  48 │ 11.5 │
│ David  │  62 │  9.9 │
│ Brian  │  60 │   16 │
└────────┴─────┴──────┘

Давайте получим имена людей, чей возраст находится в интервалах [30,60) и [60,75). Поскольку мы используем целочисленное представление возраста, мы получаем возраст в интервалах [30, 59] и [60,74].

Чтобы агрегировать имена в массиве, мы используем агрегатную функцию groupArray. Она принимает один аргумент. В нашем случае это столбец name. Функция groupArrayResample должна использовать столбец age для агрегации имен по возрасту. Чтобы определить необходимые интервалы, мы передаем аргументы 30, 75, 30 в функцию groupArrayResample.

SELECT groupArrayResample(30, 75, 30)(name, age) FROM people

┌─groupArrayResample(30, 75, 30)(name, age)─────┐
│ [['Alice','Mary','Evelyn'],['David','Brian']] │
└───────────────────────────────────────────────┘

Рассмотрим результаты.

John не входит в выборку, потому что он слишком молод. Остальные люди распределены по указанным возрастным интервалам.

Теперь давайте посчитаем общее количество людей и их среднюю зарплату в указанных возрастных интервалах.

SELECT
    countResample(30, 75, 30)(name, age) AS amount,
    avgResample(30, 75, 30)(wage, age) AS avg_wage
FROM people

┌─amount─┬─avg_wage──────────────────┐
│ [3,2]  │ [11.5,12.949999809265137] │
└────────┴───────────────────────────┘

-ArgMin

Суффикс -ArgMin может быть добавлен к имени любой агрегатной функции. В этом случае агрегатная функция принимает дополнительный аргумент, который должен быть любым сравнимым выражением. Агрегатная функция обрабатывает только строки, которые имеют минимальное значение для указанного дополнительного выражения.

Примеры: sumArgMin(column, expr), countArgMin(expr), avgArgMin(x, expr) и так далее.

-ArgMax

Подобно суффиксу -ArgMin, но обрабатывает только строки, которые имеют максимальное значение для указанного дополнительного выражения.

Блог: Использование комбинаторов агрегатов в ClickHouse

-If​

-Array​

-Map​

-SimpleState​

-State​

-Merge​

-MergeState​

-ForEach​

-Distinct​

-OrDefault​

-OrNull​

-Resample​

-ArgMin​

-ArgMax​

Связанный контент​

-If

-Array

-Map

-SimpleState

-State

-Merge

-MergeState

-ForEach

-Distinct

-OrDefault

-OrNull

-Resample

-ArgMin

-ArgMax

Связанный контент