Секция 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 для У = NULL просуммировал x, как будто NULL — это значение.

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

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

Модификатор WITH ROLLUP применяется для подсчета подытогов для ключевых выражений. При этом учитывается порядок следования ключевых выражений в списке GROUP BY. Подытоги подсчитываются в обратном порядке: сначала для последнего ключевого выражения в списке, потом для предпоследнего и так далее вплоть до самого первого ключевого выражения.

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

Примечание

Если в запросе есть секция HAVING, она может повлиять на результаты расчета подытогов.

Пример

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

┌─year─┬─month─┬─day─┐
│ 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 year, month, day WITH ROLLUP;

Поскольку секция 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 CUBE

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

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

Примечание

Если в запросе есть секция HAVING, она может повлиять на результаты расчета подытогов.

Пример

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

┌─year─┬─month─┬─day─┐
│ 2019 │     1 │   5 │
│ 2019 │     1 │  15 │
│ 2020 │     1 │   5 │
│ 2020 │     1 │  15 │
│ 2020 │    10 │   5 │
│ 2020 │    10 │  15 │
└──────┴───────┴─────┘

Query:

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

Поскольку секция 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 TOTALS

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

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

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

Примечание

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

При использовании секции HAVING поведение WITH TOTALS контролируется настройкой totals_mode.

Настройка обработки итогов

По умолчанию 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%), то включить все строчки, не прошедшие max_rows_to_group_by в totals, иначе - не включить.

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 (в этом случае соответствующие тотальные значения будут соединены).

Примеры

Пример:

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 -- getting the first occurred page header for each domain.
FROM hits
GROUP BY domain

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

Детали реализации

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

Оптимизация GROUP BY для отсортированных таблиц

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

Группировка во внешней памяти

Можно включить сброс временных данных на диск, чтобы ограничить потребление оперативной памяти при выполнении GROUP BY. Настройка max_bytes_before_external_group_by определяет пороговое значение потребления RAM, по достижении которого временные данные GROUP BY сбрасываются в файловую систему. Если равно 0 (по умолчанию) - значит выключено.

При использовании max_bytes_before_external_group_by, рекомендуем выставить max_memory_usage приблизительно в два раза больше. Это следует сделать, потому что агрегация выполняется в две стадии: чтение и формирование промежуточных данных (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.