Перейти к основному содержимому
Перейти к основному содержимому

Клауза GROUP BY

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

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

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

примечание

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

Обработка NULL​

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

Вот пример, чтобы показать, что это означает.

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

Запрос SELECT sum(x), y FROM t_null_big GROUP BY y приводит к:

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

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

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

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

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

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

примечание

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

Пример

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

Запрос:

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

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

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

Смотрите также

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

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

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

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

примечание

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

Пример

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

Запрос:

Так как секция 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, заполняются нулями.

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

Смотрите также

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

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

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

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

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

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

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

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

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

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

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

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

after_having_auto – Подсчитать количество строк, которые прошли через HAVING. Если их больше определенного количества (по умолчанию 50%), включить все строки, которые не прошли через 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 эквивалентно перечислению всех выбранных выражений, которые не являются агрегатными функциями.

Например:

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

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

Например:

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

Примеры​

Пример:

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

Пример:

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

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

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

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

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

Пример

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

Смотрите также

  • 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 (т.е. данные не были сброшены), запрос выполняется так же быстро, как и без внешней агрегации. Если какие-либо временные данные были сброшены, время выполнения будет в несколько раз длиннее (примерно в три раза).

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