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

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

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

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

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

Суффикс -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 и отдельно агрегирует values каждого key в map с помощью указанной агрегатной функции. Результат также имеет тип Map.

Пример

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

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;

┌

При применении этого комбинатора агрегатная функция возвращает то же значение, но другого типа. Это 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 │
└──────────────────────────────────────┴───┘

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

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

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

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

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

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

Суффикс -Tuple можно добавить к любой агрегатной функции. Получившаяся функция принимает по одному аргументу типа Tuple для каждого аргумента базовой агрегатной функции; все Tuple должны содержать одинаковое число элементов. Агрегация применяется независимо к каждой позиции элемента: функция получает соответствующий элемент из каждого Tuple и возвращает Tuple с результатами.

Если первый входной Tuple имеет явные имена элементов, они сохраняются в результате.

Агрегатные функции, которые самостоятельно обрабатывают значения NULL (anyRespectNulls, anyLastRespectNulls, модификатор RESPECT NULLS), не поддерживают тип Nullable(Tuple(...)) в качестве аргумента; вместо этого используйте элементы типа Nullable.

Синтаксис

<aggFunction>Tuple(tuple1[, tuple2, ...])

Аргументы

• tuple1[, tuple2, ...] — Столбцы типа Tuple, по одному на каждый аргумент базовой агрегатной функции, все с одинаковым количеством элементов. Каждый элемент должен иметь тип, поддерживаемый базовой агрегатной функцией для соответствующей позиции аргумента.

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

• Tuple, содержащий результат применения агрегатной функции к каждому элементу по отдельности.

Тип: Tuple(aggFunction(element1), aggFunction(element2), ...).

Пример

Запрос:

SELECT sumTuple(t) FROM
(
    SELECT tuple(toInt64(1), toFloat64(2.5)) AS t
    UNION ALL
    SELECT tuple(toInt64(3), toFloat64(4.5))
    UNION ALL
    SELECT tuple(toInt64(5), toFloat64(6.5))
);

Результат:

┌─sumTuple(t)─┐
│ (9,13.5)    │
└─────────────┘

При использовании GROUP BY:

SELECT
    k,
    avgTuple(t)
FROM
(
    SELECT
        number % 2 AS k,
        tuple(toInt64(number), toFloat64(number) * 1.5) AS t
    FROM numbers(6)
)
GROUP BY k
ORDER BY k;

┌─k─┬─avgTuple(t)─┐
│ 0 │ (2,3)       │
│ 1 │ (3,4.5)     │
└───┴─────────────┘

При использовании с агрегатной функцией, принимающей несколько аргументов: каждый аргумент Tuple передаёт один аргумент внутренней функции, а элементы сопоставляются по порядку:

corrTuple((a1, a2), (b1, b2)) = (corr(a1, b1), corr(a2, b2))

SELECT corrTuple((a1, a2), (b1, b2))
FROM
(
    SELECT
        toFloat64(number) AS a1,
        toFloat64(number * 2) AS a2,
        toFloat64(100 - number) AS b1,
        toFloat64(number * 3) AS b2
    FROM numbers(10)
);

┌─corrTuple((a1, a2), (b1, b2))─┐
│ (-1,1)                        │
└───────────────────────────────┘

a1 и b1 антикоррелированы, тогда как a2 и b2 пропорциональны, поэтому результат — (-1, 1).

-Tuple можно комбинировать с другими комбинаторами, например с -If. Пример: sumTupleIf(tuple_column, cond).

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

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

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

-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 │
└───────────────────────────────────┘

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

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

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

Синтаксис

<aggFunction>OrNull(x)

Аргументы

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

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

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

Тип: Nullable(aggregate function return type).

Пример

Добавьте -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))─┐
│                           ᴺᵁᴸᴸ │
└────────────────────────────────┘

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

<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, чтобы агрегировать имена по возрасту. Чтобы задать нужные интервалы, передаём в функцию groupArrayResample аргументы 30, 75, 30.

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 можно добавить к имени любой агрегатной функции. В этом случае агрегатная функция принимает дополнительный аргумент, которым может быть любое выражение, поддерживающее сравнение. Агрегатная функция обрабатывает только те строки, для которых указанное дополнительное выражение имеет минимальное значение.

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

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

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