Оператор 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 или пустую строку.

примечание

Имейте в виду, что оператор 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 или пустую строку.

примечание

Имейте в виду, что оператор 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* эта строка выводится как отдельное поле 'totals'.
• В форматах TabSeparated*, CSV* и Vertical строка приходит после основного результата, предшествуя пустой строке (после других данных).
• В форматах Pretty* строка выводится как отдельная таблица после основного результата.
• В формате Template строка выводится в соответствии с указанным шаблоном.
• В других форматах она недоступна.

примечание

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

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

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

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

Другие альтернативы включают только строки, прошедшие через HAVING, в 'totals', и ведут себя по-разному с настройками 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 – Включить все строки, которые не прошли через 'max_rows_to_group_by' в 'totals'. Другими словами, '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 (в этом случае соответствующие итоговые значения объединяются).

GROUP BY ALL

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

Например:

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

В специальном случае, если существует функция, принимающая как агрегирующие функции, так и другие поля в качестве аргументов, ключи 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).