Skip to main content
Skip to main content
Edit this page

Секция 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.