Skip to content
ClickHouse Docs
ClickHouse DocsClickHouse Docs
49.6kGet Started

GROUP BY

View as MarkdownEdit this pageReport an issueSet up docs MCP

Предложение GROUP BY переводит запрос SELECT в режим агрегации и работает следующим образом:

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

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

Обработка NULL

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

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

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

┌─x─┬────y─┐
│ 1 │    2 │
│ 2 │ ᴺᵁᴸᴸ │
│ 3 │    2 │
│ 3 │    3 │
│ 3 │ ᴺᵁᴸᴸ │
└───┴──────┘

Запрос SELECT sum(x), y FROM t_null_big GROUP BY y даёт следующий результат:

┌─sum(x)─┬────y─┐
│      4 │    2 │
│      3 │    3 │
│      5 │ ᴺᵁᴸᴸ │
└────────┴──────┘

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

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

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

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

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

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

Пример

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

┌─year─┬─month─┬─day─┐
│ 2019 │     1 │   5 │
│ 2019 │     1 │  15 │
│ 2020 │     1 │   5 │
│ 2020 │     1 │  15 │
│ 2020 │    10 │   5 │
│ 2020 │    10 │  15 │
└──────┴───────┴─────┘
Querysql
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 (при этом все три столбца ключевых выражений заполнены нулями).
Responsetext
┌─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.

Querysql
SELECT year, month, day, count(*) FROM t GROUP BY year, month, day WITH ROLLUP;

См. также

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

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

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

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

Пример

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

┌─year─┬─month─┬─day─┐
│ 2019 │     1 │   5 │
│ 2019 │     1 │  15 │
│ 2020 │     1 │   5 │
│ 2020 │     1 │  15 │
│ 2020 │    10 │   5 │
│ 2020 │    10 │  15 │
└──────┴───────┴─────┘
Querysql
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, заполняются нулями.

Responsetext
┌─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.

Querysql
SELECT year, month, day, count(*) FROM t GROUP BY year, month, day WITH CUBE;

См. также

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

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

Если указан модификатор WITH TOTALS, вычисляется ещё одна строка. В этой строке столбцы ключа содержат значения по умолчанию (нули или пустые строки), а столбцы агрегатных функций — значения, вычисленные по всем строкам (значения «итого»).

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

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

При наличии 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 (в этом случае соответствующие итоговые значения объединяются).

GROUP BY ALL

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

Например:

SELECT
    a * 2,
    b,
    count(c),
FROM t
GROUP BY ALL

то же, что и

SELECT
    a * 2,
    b,
    count(c),
FROM t
GROUP BY a * 2, b

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

Например:

SELECT
    substring(a, 4, 2),
    substring(substring(a, 1, 2), 1, count(b))
FROM t
GROUP BY ALL

то же, что и

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)

Примеры

Пример:

SELECT
    count(),
    median(FetchTiming > 60 ? 60 : FetchTiming),
    count() - sum(Refresh)
FROM hits

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

Пример:

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.

Пример

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

-- 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 для совместимости со стандартом 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 * the_number_of_threads от общего объема оперативной памяти.

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

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

Navigation