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

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

-If

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

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

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

-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 отдельно, используя указанную агрегатную функцию. Результат также имеет тип 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(начало, конец, шаг)(<aggFunction_params>, ключ_ресемплирования)

Аргументы

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

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

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

Пример

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

┌─имя────┬─возраст─┬─зарплата─┐
│ 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

Связанные материалы