GROUP BY переводит запрос SELECT в режим агрегации и работает следующим образом:
- Секция
GROUP BYсодержит список выражений (или одно выражение, которое рассматривается как список длиной в один элемент). Этот список служит «ключом группировки», а каждое отдельное выражение называется «ключевым выражением». - Все выражения в секциях SELECT, HAVING и ORDER BY должны вычисляться на основе ключевых выражений или агрегатных функций над неключевыми выражениями (включая обычные столбцы). Иными словами, каждый столбец, выбранный из таблицы, должен использоваться либо в ключевом выражении, либо внутри агрегатной функции, но не одновременно в обоих вариантах.
- Результат агрегации запроса
SELECTбудет содержать столько строк, сколько уникальных значений «ключа группировки» было в исходной таблице. Обычно это значительно уменьшает количество строк, нередко на несколько порядков, но не всегда: количество строк остается прежним, если все значения «ключа группировки» различны.
Существует еще один способ выполнить агрегацию по таблице. Если запрос содержит столбцы таблицы только внутри агрегатных функций,
секцию GROUP BY можно опустить — в этом случае предполагается агрегация по пустому множеству ключей. Такие запросы всегда возвращают ровно одну строку.Обработка 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 может влиять на результаты промежуточных итогов.
Query
GROUP BY содержит три ключевых выражения, результат включает четыре таблицы с промежуточными итогами, “свёрнутыми” справа налево:
GROUP BY year, month, day;GROUP BY year, month(при этом столбецdayзаполняется нулями);GROUP BY year(теперь столбцыmonthиdayзаполняются нулями);- и totals (при этом все три столбца ключевых выражений заполнены нулями).
Response
WITH.
Query
- Настройка group_by_use_nulls для обеспечения совместимости со стандартом SQL.
Модификатор CUBE
CUBE используется для вычисления промежуточных итогов для всех комбинаций ключевых выражений в списке GROUP BY. Строки с промежуточными итогами добавляются после результирующей таблицы.
В строках промежуточных итогов значения всех “сгруппированных” ключевых выражений устанавливаются в 0 или пустую строку.
Обратите внимание, что предложение HAVING может влиять на результаты промежуточных итогов.
Query
GROUP BY содержит три ключевых выражения, результат включает восемь таблиц с промежуточными итогами для всех комбинаций ключевых выражений:
GROUP BY year, month, dayGROUP BY year, monthGROUP BY year, dayGROUP BY yearGROUP BY month, dayGROUP BY monthGROUP BY day- и итоговые значения.
GROUP BY, заполняются нулями.
Response
WITH.
Query
- настройку 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 может работать по-разному. Поведение зависит от настройки totals_mode.
Настройка обработки totals
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%), включать в totals все строки, не прошедшие ограничение 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 равносилен перечислению всех выражений в SELECT, которые не являются агрегатными функциями.
Например:
GROUP BY будут содержать максимально возможное количество неагрегатных полей, которые можно из неё извлечь.
Например:
Примеры
GROUP BY вычисляет набор значений агрегатных функций.
Модификатор GROUPING SETS
GROUPING SETS.
Хотя запросы с модификаторами ROLLUP, CUBE и GROUPING SETS синтаксически эквивалентны, они могут выполняться по-разному.
GROUPING SETS старается выполнять всё параллельно, тогда как ROLLUP и CUBE выполняют финальное слияние агрегатов в одном потоке.
Когда исходные столбцы содержат значения по умолчанию, может быть трудно определить, является ли строка частью агрегации, использующей эти столбцы в качестве ключей, или нет.
Для решения этой проблемы следует использовать функцию GROUPING.
Пример
Следующие два запроса эквивалентны.
- настройку group_by_use_nulls для совместимости со стандартом SQL.
Подробности реализации
Оптимизация 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 * the_number_of_threads от общего объема оперативной памяти.
Когда внешняя агрегация включена, если объем данных был меньше max_bytes_before_external_group_by (то есть данные не были сброшены), запрос выполняется так же быстро, как и без внешней агрегации. Если какие-либо временные данные были сброшены, время выполнения увеличится в несколько раз (примерно втрое).
Если после GROUP BY у вас есть ORDER BY с LIMIT, то объем используемой оперативной памяти зависит от объема данных, попадающих в LIMIT, а не во всей таблице. Но если в ORDER BY нет LIMIT, не забудьте включить внешнюю сортировку (max_bytes_before_external_sort).