> ## Documentation Index > Fetch the complete documentation index at: https://private-7c7dfe99-parallel-read-in-order-multi-part.mintlify.site/llms.txt > Use this file to discover all available pages before exploring further. > Документация по предложению GROUP BY # GROUP BY Предложение `GROUP BY` переводит запрос `SELECT` в режим агрегации и работает следующим образом: * Предложение `GROUP BY` содержит список выражений (или одно выражение, которое рассматривается как список длиной в один элемент). Этот список служит «ключом группировки», а каждое отдельное выражение называется «ключевым выражением». * Все выражения в предложениях [SELECT](/ru/reference/statements/select/index), [HAVING](/ru/reference/statements/select/having), и [ORDER BY](/ru/reference/statements/select/order-by) **должны** вычисляться на основе ключевых выражений **или** [агрегатных функций](/ru/reference/functions/aggregate-functions/index) над неключевыми выражениями (включая обычные столбцы). Иными словами, каждый столбец, выбранный из таблицы, должен использоваться либо в ключевом выражении, либо внутри агрегатной функции, но не одновременно в обоих вариантах. * Результат агрегации запроса `SELECT` будет содержать столько строк, сколько уникальных значений «ключа группировки» было в исходной таблице. Обычно это значительно уменьшает количество строк, нередко на несколько порядков, но не всегда: количество строк остается прежним, если все значения «ключа группировки» различны. Если вы хотите группировать данные в таблице по номерам столбцов, а не по их именам, включите настройку [enable\_positional\_arguments](/ru/reference/settings/session-settings/enable-positional-arguments#enable_positional_arguments). Существует еще один способ выполнить агрегацию по таблице. Если запрос содержит столбцы таблицы только внутри агрегатных функций, `предложение GROUP BY` можно опустить — в этом случае предполагается агрегация по пустому множеству ключей. Такие запросы всегда возвращают ровно одну строку. ## Обработка NULL При группировке ClickHouse рассматривает [NULL](/ru/reference/syntax#null) как значение, и `NULL==NULL`. В большинстве других случаев `NULL` обрабатывается иначе. Вот пример, который показывает, что это значит. Предположим, у вас есть такая таблица: ```text theme={null} ┌─x─┬────y─┐ │ 1 │ 2 │ │ 2 │ ᴺᵁᴸᴸ │ │ 3 │ 2 │ │ 3 │ 3 │ │ 3 │ ᴺᵁᴸᴸ │ └───┴──────┘ ``` Запрос `SELECT sum(x), y FROM t_null_big GROUP BY y` даёт следующий результат: ```text theme={null} ┌─sum(x)─┬────y─┐ │ 4 │ 2 │ │ 3 │ 3 │ │ 5 │ ᴺᵁᴸᴸ │ └────────┴──────┘ ``` Можно видеть, что `GROUP BY` для `y = NULL` просуммировал `x`, как будто `NULL` — это значение. Если передать в `GROUP BY` несколько ключей, в результате будут все комбинации из выборки, как будто `NULL` — это конкретное значение. ## Модификатор ROLLUP Модификатор `ROLLUP` используется для вычисления промежуточных итогов по ключевым выражениям на основе их порядка в списке `GROUP BY`. Строки промежуточных итогов добавляются после результирующей таблицы. Промежуточные итоги вычисляются в обратном порядке: сначала вычисляются промежуточные итоги для последнего ключевого выражения в списке, затем для предыдущего и так далее до первого ключевого выражения. В строках промежуточных итогов значения уже "сгруппированных" ключевых выражений устанавливаются в `0` или пустую строку. Обратите внимание, что предложение [HAVING](/ru/reference/statements/select/having) может влиять на результаты промежуточных итогов. **Пример** Рассмотрим таблицу t: ```text theme={null} ┌─year─┬─month─┬─day─┐ │ 2019 │ 1 │ 5 │ │ 2019 │ 1 │ 15 │ │ 2020 │ 1 │ 5 │ │ 2020 │ 1 │ 15 │ │ 2020 │ 10 │ 5 │ │ 2020 │ 10 │ 15 │ └──────┴───────┴─────┘ ``` ```sql title="Query" theme={null} SELECT year, month, day, count(*) FROM t GROUP BY ROLLUP(year, month, day); ``` Поскольку раздел `GROUP BY` содержит три ключевых выражения, результат включает четыре таблицы с промежуточными итогами, "свёрнутыми" справа налево: * `GROUP BY year, month, day`; * `GROUP BY year, month` (при этом столбец `day` заполняется нулями); * `GROUP BY year` (теперь столбцы `month` и `day` заполняются нулями); * и totals (при этом все три столбца ключевых выражений заполнены нулями). ```text title="Response" theme={null} ┌─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`. ```sql title="Query" theme={null} SELECT year, month, day, count(*) FROM t GROUP BY year, month, day WITH ROLLUP; ``` **См. также** * Настройка [group\_by\_use\_nulls](/ru/reference/settings/session-settings/group-by#group_by_use_nulls) для обеспечения совместимости со стандартом SQL. ## Модификатор CUBE Модификатор `CUBE` используется для вычисления промежуточных итогов для всех комбинаций ключевых выражений в списке `GROUP BY`. Строки с промежуточными итогами добавляются после результирующей таблицы. В строках промежуточных итогов значения всех "сгруппированных" ключевых выражений устанавливаются в `0` или пустую строку. Обратите внимание, что предложение [HAVING](/ru/reference/statements/select/having) может влиять на результаты промежуточных итогов. **Пример** Рассмотрим таблицу t: ```text theme={null} ┌─year─┬─month─┬─day─┐ │ 2019 │ 1 │ 5 │ │ 2019 │ 1 │ 15 │ │ 2020 │ 1 │ 5 │ │ 2020 │ 1 │ 15 │ │ 2020 │ 10 │ 5 │ │ 2020 │ 10 │ 15 │ └──────┴───────┴─────┘ ``` ```sql title="Query" theme={null} SELECT year, month, day, count(*) FROM t GROUP BY CUBE(year, month, day); ``` Поскольку раздел `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`, заполняются нулями. ```text title="Response" theme={null} ┌─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`. ```sql title="Query" theme={null} SELECT year, month, day, count(*) FROM t GROUP BY year, month, day WITH CUBE; ``` **См. также** * настройку [group\_by\_use\_nulls](/ru/reference/settings/session-settings/group-by#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`. При наличии [HAVING](/ru/reference/statements/select/having) модификатор `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](/ru/reference/statements/select/join) (в этом случае соответствующие итоговые значения объединяются). ## GROUP BY ALL `GROUP BY ALL` равносилен перечислению всех выражений в SELECT, которые не являются агрегатными функциями. Например: ```sql theme={null} SELECT a * 2, b, count(c), FROM t GROUP BY ALL ``` то же, что и ```sql theme={null} SELECT a * 2, b, count(c), FROM t GROUP BY a * 2, b ``` В особом случае, когда у функции среди аргументов есть и агрегатные функции, и другие поля, ключи `GROUP BY` будут содержать максимально возможное количество неагрегатных полей, которые можно из неё извлечь. Например: ```sql theme={null} SELECT substring(a, 4, 2), substring(substring(a, 1, 2), 1, count(b)) FROM t GROUP BY ALL ``` то же, что и ```sql theme={null} SELECT substring(a, 4, 2), substring(substring(a, 1, 2), 1, count(b)) FROM t GROUP BY substring(a, 4, 2), substring(a, 1, 2) ``` ## Примеры Пример: ```sql theme={null} SELECT count(), median(FetchTiming > 60 ? 60 : FetchTiming), count() - sum(Refresh) FROM hits ``` В отличие от MySQL (и в соответствии со стандартом SQL), вы не можете получить какое-либо значение столбца, который не входит в ключ и не используется в агрегатной функции (кроме константных выражений). Чтобы обойти это ограничение, можно использовать агрегатную функцию 'any' (получить первое встретившееся значение) или 'min/max'. Пример: ```sql theme={null} SELECT domainWithoutWWW(URL) AS domain, count(), any(Title) AS title -- получение первого встреченного заголовка страницы для каждого домена. FROM hits GROUP BY domain ``` Для каждого уникального значения ключа `GROUP BY` вычисляет набор значений агрегатных функций. ## Модификатор GROUPING SETS Это наиболее общий модификатор. Он позволяет вручную задать несколько наборов ключей агрегации (grouping sets). Агрегация выполняется отдельно для каждого набора группировки, после чего все результаты объединяются. Если столбец не входит в набор группировки, он заполняется значением по умолчанию. Иными словами, описанные выше модификаторы можно представить с помощью `GROUPING SETS`. Хотя запросы с модификаторами `ROLLUP`, `CUBE` и `GROUPING SETS` синтаксически эквивалентны, они могут выполняться по-разному. `GROUPING SETS` старается выполнять всё параллельно, тогда как `ROLLUP` и `CUBE` выполняют финальное слияние агрегатов в одном потоке. Когда исходные столбцы содержат значения по умолчанию, может быть трудно определить, является ли строка частью агрегации, использующей эти столбцы в качестве ключей, или нет. Для решения этой проблемы следует использовать функцию `GROUPING`. **Пример** Следующие два запроса эквивалентны. ```sql theme={null} -- Query 1 SELECT year, month, day, count(*) FROM t GROUP BY year, month, day WITH ROLLUP; -- Query 2 SELECT year, month, day, count(*) FROM t GROUP BY GROUPING SETS ( (year, month, day), (year, month), (year), () ); ``` **См. также** * настройку [group\_by\_use\_nulls](/ru/reference/settings/session-settings/group-by#group_by_use_nulls) для совместимости со стандартом SQL. ## Подробности реализации Агрегация — одна из важнейших возможностей колоночной СУБД, поэтому её реализация — одна из самых тщательно оптимизированных частей ClickHouse. По умолчанию агрегация выполняется в памяти с использованием хеш-таблицы. Для неё предусмотрено более 40 специализаций, которые автоматически выбираются в зависимости от типов данных ключа группировки. ### Оптимизация GROUP BY в зависимости от ключа сортировки таблицы Агрегацию можно выполнять эффективнее, если таблица отсортирована по некоторому ключу, а выражение `GROUP BY` содержит как минимум префикс ключа сортировки или инъективные функции. В этом случае, когда из таблицы считывается новый ключ, промежуточный результат агрегации можно завершить и отправить клиенту. Это поведение включается настройкой [optimize\_aggregation\_in\_order](/ru/reference/settings/session-settings/optimize-aggregation-in-order#optimize_aggregation_in_order). Такая оптимизация уменьшает использование памяти при агрегации, но в некоторых случаях может замедлить выполнение запроса. ### GROUP BY во внешней памяти Вы можете включить сброс временных данных на диск, чтобы ограничить использование памяти при `GROUP BY`. Параметр [max\_bytes\_before\_external\_group\_by](/ru/reference/settings/session-settings/max-bytes#max_bytes_before_external_group_by) задает порог потребления оперативной памяти, при достижении которого временные данные `GROUP BY` сбрасываются в файловую систему. Если установлено значение 0 (по умолчанию), эта возможность отключена. В качестве альтернативы можно задать [max\_bytes\_ratio\_before\_external\_group\_by](/ru/reference/settings/session-settings/max-bytes#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](/ru/reference/settings/session-settings/max-memory-usage#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](/ru/reference/statements/select/order-by) с [LIMIT](/ru/reference/statements/select/limit), то объем используемой оперативной памяти зависит от объема данных, попадающих в `LIMIT`, а не во всей таблице. Диапазон [`LIMIT ... AFTER ... UNTIL`](/ru/reference/statements/select/limit#limit-after-until) такой экономии не дает, поскольку сортировка не может знать, сколько строк потребуется этому диапазону. Но если в `ORDER BY` нет `LIMIT`, не забудьте включить внешнюю сортировку (`max_bytes_before_external_sort`).