Агрегатные функции

Елена Дворядкина

Обзор

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

Полный список предопределенных агрегатных функций с описаниями можно найти в разделе List of Aggregate Functions документации ClickHouse.

Создание таблицы для выполнения тестовых примеров

 
Чтобы воспроизвести приведенные в статье примеры и посмотреть, как работают различные агрегатные функции, создайте предварительно таблицу users:

CREATE TABLE users (user_id Int32, name String, age Int32, role String) ENGINE = MergeTree ORDER BY user_id;
INSERT INTO users VALUES
(1, 'john', 37, 'developer'),
(2, 'alex', 25, 'analyst'),
(3, 'mary', 30, 'manager'),
(4, 'david', 41, 'developer'),
(5, 'andrew', 41, 'analyst'),
(6, 'harry', 29, 'developer'),
(7, 'ann', 35, 'QA engineer'),
(8, 'jack', 27, 'designer');
┌─user_id─┬─name───┬─age─┬─role────────┐
│       1 │ john   │  37 │ developer   │
│       2 │ alex   │  25 │ analyst     │
│       3 │ mary   │  30 │ manager     │
│       4 │ david  │  41 │ developer   │
│       5 │ andrew │  41 │ analyst     │
│       6 │ harry  │  29 │ developer   │
│       7 │ ann    │  35 │ QA engineer │
│       8 │ jack   │  27 │ designer    │
└─────────┴────────┴─────┴─────────────┘

Например, в следующем запросе используется агрегатная функция, которая возвращает возраст самого младшего пользователя из таблицы users:

SELECT min(age) FROM users;

Результат:

┌─min(age)─┐
│       25 │
└──────────┘

Чтобы использовать агрегатную функцию в выражении WHERE, используйте подзапрос. Следующий пример возвращает имя самого младшего пользователя:

SELECT name FROM users WHERE age = (SELECT min(age) FROM users);

Результат:

┌─name─┐
│ alex │
└──────┘

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

SELECT role, count() AS number_of_users FROM users GROUP BY role;

Результат:

┌─role────────┬─number_of_users─┐
│ manager     │               1 │
│ designer    │               1 │
│ analyst     │               2 │
│ developer   │               3 │
│ QA engineer │               1 │
└─────────────┴─────────────────┘

Чтобы фильтровать полученные значения, используйте выражение HAVING. Например, следующий запрос возвращает роли, которые назначены более чем одному пользователю:

SELECT role, count() AS number_of_users FROM users GROUP BY role HAVING count()>1;

Результат:

┌─role──────┬─number_of_users─┐
│ analyst   │               2 │
│ developer │               3 │
└───────────┴─────────────────┘
ПРИМЕЧАНИЕ

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

Комбинаторы

Чтобы расширить возможности агрегатных функций и адаптировать запросы под различные специализированные задачи без необходимости изменять структуру данных, ADQM поддерживает различные комбинаторы. Комбинатор — специальный суффикс, который добавляется к названию агрегатной функции и модифицирует логику работы этой функции. Для одной функции можно использовать несколько комбинаторов одновременно.

Дополнительное условие для агрегирования

Aгрегатная функция с комбинатором If принимает дополнительный аргумент — условие типа UInt8, по которому определяется, какие строки агрегатная функция будет обрабатывать.

Синтаксис агрегатного выражения: <aggr_func_name>If(<expr>, <if_condition>).

Пример

Следующий запрос возвращает количество пользователей старше 30 лет:

SELECT countIf(age>30) AS over_30 FROM users;

Результат:

┌─over_30─┐
│       4 │
└─────────┘

Агрегирование по уникальным значениям

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

Синтаксис агрегатного выражения: <aggr_func_name>Distinct(<expr>) или <aggr_func_name>(DISTINCT <expr>).

Пример

Следующий запрос возвращает количество уникальных ролей в таблице:

SELECT count(DISTINCT role) AS unique_roles FROM users;

Результат:

┌─unique_roles─┐
│            5 │
└──────────────┘

Агрегированные значения при пустых входных данных

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

  • OrDefault — возвращается значение по умолчанию для соответствующего типа агрегатной функции;

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

Пример:

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

Результат:

┌─avg(number)─┬─avgOrDefault(number)─┬─avgOrNull(number)─┐
│         nan │                    0 │              ᴺᵁᴸᴸ │
└─────────────┴──────────────────────┴───────────────────┘

Агрегирование массивов

Array

Агрегатные функции с комбинатором Array принимают аргументы типа Array(T) (массивы) вместо аргументов типа T. При обработке массивов агрегатная функция работает как исходная агрегатная функция по всем элементам массивов.

Синтаксис агрегатного выражения: <aggr_func_name>Array(<arr>), где <arr> — массивы элементов (например, столбец с массивами). Если функция принимает несколько аргументов, массивы должны быть одинаковых размеров.

Пример

  1. Создайте таблицу, один из столбцов которой содержит массивы:

    CREATE TABLE test_arrays (id Int32, array Array(UInt32)) ENGINE = MergeTree ORDER BY id;
    INSERT INTO test_arrays VALUES (1, [1, 2, 3]), (2, [2,3,7]), (3, [1,1,1]);
    ┌─id─┬─array───┐
│  1 │ [1,2,3] │
│  2 │ [2,3,7] │
│  3 │ [1,1,1] │
└────┴─────────┘

  2. Используйте функцию sumArray, чтобы вычислить сумму элементов всех массивов в столбце типа Array(UInt32):

    SELECT sumArray(array) AS sum FROM test_arrays;
    ┌─sum─┐
│  21 │
└─────┘

ForEach

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

Синтаксис агрегатного выражения: <aggr_func_name>ForEach(<arr>), где <arr> — набор массивов.

Пример

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

SELECT maxForEach(array) FROM test_arrays;

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

┌─maxForEach(array)─┐
│ [2,3,7]           │
└───────────────────┘

Агрегирование значений типа Map

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

Пример

  1. Создайте таблицу, один из столбцов которой содержит значения типа Map:

    CREATE TABLE test_map_table (a Int32, b Map(String, UInt64)) ENGINE = MergeTree ORDER BY a;
    INSERT INTO test_map_table VALUES (1, {'key1':1, 'key2':10}), (2, {'key1':2,'key2':20}), (3, {'key1':3,'key2':30});
    ┌─a─┬─b────────────────────┐
│ 1 │ {'key1':1,'key2':10} │
│ 2 │ {'key1':2,'key2':20} │
│ 3 │ {'key1':3,'key2':30} │
└───┴──────────────────────┘

  2. Используйте комбинатор Map для агрегатной функции sum, чтобы получить сумму значений по ключу key2:

    select sumMap(b)['key2'] from test_map_table;

    Результат:

    ┌─arrayElement(sumMap(b), 'key2')─┐
│                              60 │
└─────────────────────────────────┘

Работа с агрегатными состояниями

ADQM позволяет работать с промежуточными агрегатными состояниями вместо результирующих агрегатных значений, которые занимают больше места. Используйте комбинатор State для агрегатных функций, чтобы сохранять промежуточные состояния агрегаций типа AggregateFunction. Чтобы в дальнейшем получить агрегированные данные, необходимо передать промежуточные состояния в качестве аргументов в те же агрегатные функции с комбинаторами:

  • Merge — доагрегирует данные и возвращает готовое значение;

  • MergeState — выполняет слияние промежуточных состояний агрегации аналогично комбинатору Merge, но возвращает не готовое значение, а промежуточное состояние агрегации аналогично комбинатору State.

Для некоторых агрегатных функций (например, sum, max, min, any), можно использовать комбинатор SimpleState. Агрегатная функция с этим комбинатором возвращает то же значение, что и исходная функция, но типа SimpleAggregateFunction. SimpleAggregateFunction имеет лучшую производительность, чем AggregateFunction для той же агрегатной функции.

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

Разделение данных на группы перед агрегированием

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

Синтаксис агрегатного выражения: <aggr_func_name>Resample(<start>, <end>, <step>)(<aggr_func_params>, <resample_key>), где:

  • <resample_key> — столбец, значения которого используются для разделения данных на интервалы;

  • <start> — начальное значение полного интервала значений <resample_key>;

  • <stop> — конечное значение полного интервала значений <resample_key> (интервал не включает конечное значение — [<start>, <stop>));

  • <step> — шаг деления полного интервала на подинтервалы. Функция aggr_func_name выполняется для каждого из подинтервалов независимо;

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

Агрегатная функция с комбинатором Resample возвращает массив результатов функции aggr_func_name для каждой группы данных.

Пример

 
Исходная таблица users:

┌─name───┬─age─┐
│ john   │  37 │
│ alex   │  25 │
│ mary   │  30 │
│ david  │  41 │
│ andrew │  41 │
│ harry  │  29 │
│ ann    │  35 │
│ jack   │  27 │
└────────┴─────┘

Следующий запрос возвращает имена пользователей, чей возраст находится в интервалах [20,35) и [35,50):

SELECT groupArrayResample(20, 50, 15)(name, age) from users;

где:

  • name — столбец с именами пользователей (аргумент агрегатной функции groupArray);

  • age — столбец для агрегирования имен пользователей по возрасту.

Результат:

┌─groupArrayResample(20, 50, 15)(name, age)────────────────────────┐
│ [['alex','mary','harry','jack'],['john','david','andrew','ann']] │
└──────────────────────────────────────────────────────────────────┘

Обработка NULL

Значения NULL в агрегатных функциях игнорируются. Исключением является функция count(), которая возвращает общее количество строк в выборке.

Пример

Создайте таблицу со столбцом типа Nullable:

CREATE TABLE table_nulls (x Int8, y Nullable(Int8)) ENGINE = MergeTree ORDER BY x;
INSERT INTO table_nulls VALUES (1, NULL), (2, 1), (3, 2), (4, 2), (5, 3);
┌─x─┬────y─┐
│ 1 │ ᴺᵁᴸᴸ │
│ 2 │    1 │
│ 3 │    2 │
│ 4 │    2 │
│ 5 │    3 │
└───┴──────┘

Функция groupArray(y) формирует массив из значений столбца y, не учитывая значения NULL:

SELECT groupArray(y) FROM table_nulls;
┌─groupArray(y)─┐
│ [1,2,2,3]     │
└───────────────┘

Функция count() возвращает общее количество строк в таблице:

SELECT count() FROM table_nulls;
┌─count()─┐
│       5 │
└─────────┘

Функция count(y) возвращает количество строк, где значение y не NULL:

SELECT count(y) FROM table_nulls;
┌─count(y)─┐
│        4 │
└──────────┘

Функция count(DISTINCT y) возвращает количество уникальных строк со значениями y, не равными NULL:

SELECT count(DISTINCT y) FROM table_nulls;
┌─uniqExact(y)─┐
│            3 │
└──────────────┘

Чтобы заменить значения NULL на какое-либо подходящее значение, которое будет учитываться при вычислении агрегатной функции, можно использовать функцию coalesce. Например, функция avg(coalesce(y, 0)) заменяет все значения NULL в столбце y на 0 при вычислении среднего значения по столбцу:

SELECT avg(y), avg(coalesce(y, 0)) FROM table_nulls;
┌─avg(y)─┬─avg(coalesce(y, 0))─┐
│      2 │                 1.6 │
└────────┴─────────────────────┘

Чтобы не пропускать NULL в агрегатных функциях, можно также использовать функцию tuple, например:

SELECT groupArray(y), groupArray(tuple(y)).1 FROM table_nulls;
┌─groupArray(y)─┬─tupleElement(groupArray(tuple(y)), 1)─┐
│ [1,2,2,3]     │ [NULL,1,2,2,3]                        │
└───────────────┴───────────────────────────────────────┘
Нашли ошибку? Выделите текст и нажмите Ctrl+Enter чтобы сообщить о ней