Оператор GROUP BY

Оператор GROUP BY переводит запрос SELECT в режим агрегации, который работает следующим образом:

• Оператор GROUP BY содержит список выражений (или одно выражение, которое рассматривается как список длины один). Этот список выступает в роли "ключа группировки", а каждое отдельное выражение далее называется "ключевым выражением".
• Все выражения в операторах SELECT, HAVING и ORDER BY должны вычисляться на основе ключевых выражений или на основе агрегатных функций над неклю́чевыми выражениями (включая обычные столбцы). Другими словами, каждый столбец, выбранный из таблицы, должен использоваться либо в ключевом выражении, либо внутри агрегатной функции, но не одновременно в обоих местах.
• Результат агрегирующего запроса SELECT будет содержать столько строк, сколько было уникальных значений "ключа группировки" в исходной таблице. Обычно это существенно уменьшает число строк, зачастую на порядки, но не обязательно: количество строк останется прежним, если все значения "ключа группировки" были различны.

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

Примечание

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

Обработка NULL

При группировке ClickHouse интерпретирует NULL как значение, и NULL == NULL. Это отличается от обработки NULL в большинстве других контекстов.

Ниже приведён пример, иллюстрирующий это.

Предположим, у вас есть следующая таблица:

┌─x─┬────y─┐
│ 1 │    2 │
│ 2 │ ᴺᵁᴸᴸ │
│ 3 │    2 │
│ 3 │    3 │
│ 3 │ ᴺᵁᴸᴸ │
└───┴──────┘

Запрос SELECT sum(x), y FROM t_null_big GROUP BY y приводит к следующему результату:

┌─sum(x)─┬────y─┐
│      4 │    2 │
│      3 │    3 │
│      5 │ ᴺᵁᴸᴸ │
└────────┴──────┘

Видно, что GROUP BY для y = NULL суммировал x так, как если бы NULL было этим значением.

Если указать в GROUP BY несколько ключей, в результате вы получите все комбинации выборки, как если бы NULL рассматривался как конкретное значение.

Модификатор ROLLUP

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

Промежуточные итоги вычисляются в обратном порядке: сначала — для последнего ключевого выражения в списке, затем для предыдущего и так далее до первого ключевого выражения.

В строках с промежуточными итогами значения уже «сгруппированных» ключевых выражений устанавливаются в значение 0 или пустую строку.

Примечание

Имейте в виду, что предложение HAVING может повлиять на результаты промежуточных итогов.

Пример

Рассмотрим таблицу t:

┌─год──┬─месяц─┬─день─┐
│ 2019 │     1 │   5 │
│ 2019 │     1 │  15 │
│ 2020 │     1 │   5 │
│ 2020 │     1 │  15 │
│ 2020 │    10 │   5 │
│ 2020 │    10 │  15 │
└──────┴───────┴─────┘

Запрос:

SELECT year, month, day, count(*) FROM t GROUP BY ROLLUP(year, month, day);

Поскольку секция GROUP BY содержит три ключевых выражения, результат включает четыре таблицы с промежуточными итогами, «свёрнутыми» справа налево:

• GROUP BY year, month, day;
• GROUP BY year, month (и столбец day заполняется нулями);
• GROUP BY year (теперь оба столбца month и day заполняются нулями);
• и общие итоги (и все три столбца с ключевыми выражениями заполнены нулями).

┌─year─┬─month─┬─day─┬─count()─┐
│ 2020 │    10 │  15 │       1 │
│ 2020 │     1 │   5 │       1 │
│ 2019 │     1 │   5 │       1 │
│ 2020 │     1 │  15 │       1 │
│ 2019 │     1 │  15 │       1 │
│ 2020 │    10 │   5 │       1 │
└──────┴───────┴─────┴─────────┘
┌─year─┬─month─┬─day─┬─count()─┐
│ 2019 │     1 │   0 │       2 │
│ 2020 │     1 │   0 │       2 │
│ 2020 │    10 │   0 │       2 │
└──────┴───────┴─────┴─────────┘
┌─year─┬─month─┬─day─┬─count()─┐
│ 2019 │     0 │   0 │       2 │
│ 2020 │     0 │   0 │       4 │
└──────┴───────┴─────┴─────────┘
┌─year─┬─month─┬─day─┬─count()─┐
│    0 │     0 │   0 │       6 │
└──────┴───────┴─────┴─────────┘

Этот же запрос можно записать и с использованием ключевого слова WITH.

SELECT year, month, day, count(*) FROM t GROUP BY year, month, day WITH ROLLUP;

См. также

• Параметр group_by_use_nulls для обеспечения совместимости со стандартом SQL.

Модификатор CUBE

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

В строках с промежуточными итогами значения всех «сгруппированных» ключевых выражений устанавливаются в 0 или пустую строку.

Примечание

Имейте в виду, что предложение HAVING может влиять на результаты промежуточных итогов.

Пример

Рассмотрим таблицу t:

┌─год──┬─месяц─┬─день─┐
│ 2019 │     1 │   5 │
│ 2019 │     1 │  15 │
│ 2020 │     1 │   5 │
│ 2020 │     1 │  15 │
│ 2020 │    10 │   5 │
│ 2020 │    10 │  15 │
└──────┴───────┴─────┘

Запрос:

SELECT year, month, day, count(*) FROM t GROUP BY CUBE(year, month, day);

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

• GROUP BY year, month, day
• GROUP BY year, month
• GROUP BY year, day
• GROUP BY year
• GROUP BY month, day
• GROUP BY month
• GROUP BY day
• и общие итоги.

Столбцы, не включённые в GROUP BY, заполняются нулями.

┌─year─┬─month─┬─day─┬─count()─┐
│ 2020 │    10 │  15 │       1 │
│ 2020 │     1 │   5 │       1 │
│ 2019 │     1 │   5 │       1 │
│ 2020 │     1 │  15 │       1 │
│ 2019 │     1 │  15 │       1 │
│ 2020 │    10 │   5 │       1 │
└──────┴───────┴─────┴─────────┘
┌─year─┬─month─┬─day─┬─count()─┐
│ 2019 │     1 │   0 │       2 │
│ 2020 │     1 │   0 │       2 │
│ 2020 │    10 │   0 │       2 │
└──────┴───────┴─────┴─────────┘
┌─year─┬─month─┬─day─┬─count()─┐
│ 2020 │     0 │   5 │       2 │
│ 2019 │     0 │   5 │       1 │
│ 2020 │     0 │  15 │       2 │
│ 2019 │     0 │  15 │       1 │
└──────┴───────┴─────┴─────────┘
┌─year─┬─month─┬─day─┬─count()─┐
│ 2019 │     0 │   0 │       2 │
│ 2020 │     0 │   0 │       4 │
└──────┴───────┴─────┴─────────┘
┌─year─┬─month─┬─day─┬─count()─┐
│    0 │     1 │   5 │       2 │
│    0 │    10 │  15 │       1 │
│    0 │    10 │   5 │       1 │
│    0 │     1 │  15 │       2 │
└──────┴───────┴─────┴─────────┘
┌─year─┬─month─┬─day─┬─count()─┐
│    0 │     1 │   0 │       4 │
│    0 │    10 │   0 │       2 │
└──────┴───────┴─────┴─────────┘
┌─year─┬─month─┬─day─┬─count()─┐
│    0 │     0 │   5 │       3 │
│    0 │     0 │  15 │       3 │
└──────┴───────┴─────┴─────────┘
┌─year─┬─month─┬─day─┬─count()─┐
│    0 │     0 │   0 │       6 │
└──────┴───────┴─────┴─────────┘

Тот же запрос можно записать и с использованием ключевого слова WITH.

SELECT year, month, day, count(*) FROM t GROUP BY year, month, day WITH CUBE;

См. также

• Настройка group_by_use_nulls для совместимости со стандартом SQL.

Модификатор WITH TOTALS

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

Эта дополнительная строка создаётся только в форматах JSON*, TabSeparated* и Pretty*, отдельно от остальных строк:

• В форматах XML и JSON* эта строка выводится как отдельное поле totals.
• В форматах TabSeparated*, CSV* и Vertical строка следует после основного результата, предваряемая пустой строкой (после остальных данных).
• В форматах Pretty* строка выводится как отдельная таблица после основного результата.
• В формате Template строка выводится в соответствии с указанным шаблоном.
• В остальных форматах она недоступна.

Примечание

totals выводится в результатах запросов SELECT и не выводится в INSERT INTO ... SELECT.

WITH TOTALS может работать по-разному, когда присутствует HAVING. Поведение зависит от настройки totals_mode.

Настройка обработки totals

По умолчанию totals_mode = 'before_having'. В этом случае totals вычисляется по всем строкам, включая те, которые не проходят по условиям HAVING и max_rows_to_group_by.

Другие варианты включают в totals только строки, прошедшие через HAVING, и по-разному ведут себя при настройках max_rows_to_group_by и group_by_overflow_mode = 'any'.

after_having_exclusive – не включать строки, которые не прошли через max_rows_to_group_by. Другими словами, в totals будет меньше или столько же строк, сколько было бы, если бы max_rows_to_group_by был опущен.

after_having_inclusive – включать в totals все строки, которые не прошли через max_rows_to_group_by. Другими словами, в totals будет больше или столько же строк, сколько было бы, если бы max_rows_to_group_by был опущен.

after_having_auto – подсчитать количество строк, прошедших через HAVING. Если оно больше определённого порога (по умолчанию 50%), включить в totals все строки, которые не прошли через max_rows_to_group_by. В противном случае не включать их.

totals_auto_threshold – по умолчанию 0.5. Коэффициент для after_having_auto.

Если max_rows_to_group_by и group_by_overflow_mode = 'any' не используются, все варианты after_having эквивалентны, и можно использовать любой из них (например, after_having_auto).

Вы можете использовать WITH TOTALS во вложенных подзапросах, включая подзапросы в предложении JOIN (в этом случае соответствующие итоговые значения объединяются).

GROUP BY ALL

GROUP BY ALL эквивалентен перечислению в предложении SELECT всех выражений, которые не являются агрегатными функциями.

Например:

SELECT
    a * 2,
    b,
    count(c),
FROM t
GROUP BY ALL

то же самое, что

SELECT
    a * 2,
    b,
    count(c),
FROM t
GROUP BY a * 2, b

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

Например:

SELECT
    substring(a, 4, 2),
    substring(substring(a, 1, 2), 1, count(b))
FROM t
GROUP BY ALL

то же самое, что

SELECT
    substring(a, 4, 2),
    substring(substring(a, 1, 2), 1, count(b))
FROM t
GROUP BY substring(a, 4, 2), substring(a, 1, 2)

Примеры

Пример:

SELECT
    count(),
    median(FetchTiming > 60 ? 60 : FetchTiming),
    count() - sum(Refresh)
FROM hits

В отличие от MySQL (и в соответствии со стандартом SQL), вы не можете получить значение столбца, не участвующего ни в ключе, ни в агрегатной функции (за исключением константных выражений). В качестве обходного решения можно использовать агрегатную функцию any (получить первое встретившееся значение) или min/max.

Пример:

SELECT
    domainWithoutWWW(URL) AS domain,
    count(),
    any(Title) AS title -- получение первого встреченного заголовка страницы для каждого домена.
FROM hits
GROUP BY domain

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

Модификатор GROUPING SETS

Это самый общий модификатор. Он позволяет вручную задавать несколько наборов ключей агрегации (grouping sets). Агрегация выполняется отдельно для каждого grouping set, после чего все результаты объединяются. Если столбец не входит в grouping set, он заполняется значением по умолчанию.

Другими словами, модификаторы, описанные выше, могут быть выражены с помощью GROUPING SETS. Несмотря на то, что запросы с модификаторами ROLLUP, CUBE и GROUPING SETS синтаксически эквивалентны, их производительность может отличаться. Когда GROUPING SETS старается выполнять все варианты агрегации параллельно, ROLLUP и CUBE выполняют финальное слияние агрегатов в одном потоке.

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

Пример

Следующие два запроса эквивалентны.

-- Запрос 1
SELECT year, month, day, count(*) FROM t GROUP BY year, month, day WITH ROLLUP;

-- Запрос 2
SELECT year, month, day, count(*) FROM t GROUP BY
GROUPING SETS
(
    (year, month, day),
    (year, month),
    (year),
    ()
);

См. также

• настройку group_by_use_nulls для обеспечения совместимости со стандартом SQL.

Подробности реализации

Агрегация — одна из важнейших функций колоночной СУБД, и, следовательно, её реализация является одной из наиболее оптимизированных частей ClickHouse. По умолчанию агрегация выполняется в памяти с использованием хеш-таблицы. Для неё существует более 40 специализаций, которые выбираются автоматически в зависимости от типов данных «ключа группировки».

Оптимизация GROUP BY в зависимости от сортировочного ключа таблицы

Агрегацию можно выполнять более эффективно, если таблица отсортирована по некоторому ключу, а выражение GROUP BY содержит как минимум префикс сортировочного ключа или инъективные функции. В этом случае, когда из таблицы читается новый ключ, промежуточный результат агрегации может быть финализирован и отправлен клиенту. Такое поведение включается настройкой optimize_aggregation_in_order. Подобная оптимизация снижает потребление памяти во время агрегации, но в некоторых случаях может замедлить выполнение запроса.

GROUP BY во внешней памяти

Вы можете включить сброс временных данных на диск, чтобы ограничить использование памяти во время GROUP BY. Настройка max_bytes_before_external_group_by определяет порог потребления ОЗУ, при достижении которого временные данные GROUP BY начинают сбрасываться в файловую систему. Если установлено значение 0 (по умолчанию), механизм отключён. В качестве альтернативы вы можете задать max_bytes_ratio_before_external_group_by, что позволяет задействовать внешнюю память для GROUP BY только после того, как запрос достигнет определённого порога использованной памяти.

При использовании max_bytes_before_external_group_by мы рекомендуем установить max_memory_usage примерно вдвое выше (или max_bytes_ratio_before_external_group_by=0.5). Это необходимо, потому что агрегация состоит из двух этапов: чтение данных и формирование промежуточных данных (1) и слияние промежуточных данных (2). Сброс данных в файловую систему может происходить только на этапе 1. Если временные данные не были сброшены, то на этапе 2 может потребоваться до такого же объёма памяти, как и на этапе 1.

Например, если max_memory_usage было установлено в 10000000000 и вы хотите использовать внешнюю агрегацию, имеет смысл задать max_bytes_before_external_group_by равным 10000000000, а max_memory_usage — 20000000000. Когда внешняя агрегация срабатывает (если был хотя бы один сброс временных данных), максимальное потребление ОЗУ лишь немного превышает max_bytes_before_external_group_by.

При распределённой обработке запросов внешняя агрегация выполняется на удалённых серверах. Чтобы сервер, инициирующий запрос, использовал лишь небольшое количество ОЗУ, установите distributed_aggregation_memory_efficient в 1.

При слиянии данных, сброшенных на диск, а также при слиянии результатов с удалённых серверов при включённой настройке distributed_aggregation_memory_efficient, используется до 1/256 * число_потоков от общего объёма ОЗУ.

Когда внешняя агрегация включена, если объём данных меньше max_bytes_before_external_group_by (то есть данные не были сброшены), запрос выполняется так же быстро, как и без внешней агрегации. Если какие-либо временные данные были сброшены, время выполнения будет в несколько раз больше (примерно в три раза).

Если после GROUP BY используется ORDER BY с LIMIT, то объём используемой ОЗУ зависит от количества данных в LIMIT, а не во всей таблице. Но если у ORDER BY нет LIMIT, не забудьте включить внешнюю сортировку (max_bytes_before_external_sort).