Оператор 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 строка выводится в соответствии с указанным шаблоном.
• В других форматах она недоступна.

примечание

totals выводится в результатах запросов 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).