Клаузула GROUP BY

Клаузула GROUP BY переводит запрос SELECT в режим агрегации, который работает следующим образом:

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

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

примечание

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

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

примечание

Имейте в виду, что клаузула HAVING может повлиять на результаты промежуточных сумм.

Пример

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

┌─year─┬─month─┬─day─┐
│ 2019 │     1 │   5 │
│ 2019 │     1 │  15 │
│ 2020 │     1 │   5 │
│ 2020 │     1 │  15 │
│ 2020 │    10 │   5 │
│ 2020 │    10 │  15 │
└──────┴───────┴─────┘

Запрос:

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 обе заполнены нулями);
• и итоги (все три ключевых колонки выражения нули).

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

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

Смотрите также

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

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

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

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

примечание

Имейте в виду, что клаузула HAVING может повлиять на результаты промежуточных сумм.

Пример

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

┌─year─┬─month─┬─day─┐
│ 2019 │     1 │   5 │
│ 2019 │     1 │  15 │
│ 2020 │     1 │   5 │
│ 2020 │     1 │  15 │
│ 2020 │    10 │   5 │
│ 2020 │    10 │  15 │
└──────┴───────┴─────┘

Запрос:

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, заполнены нулями.

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

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

примечание

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

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

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

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

Другие альтернативы включают только строки, которые проходят через HAVING в 'итогах', и ведут себя по-разному с настройками max_rows_to_group_by и group_by_overflow_mode = 'any'.

after_having_exclusive – Не включать строки, которые не прошли через max_rows_to_group_by. Иными словами, 'итоги' будут содержать меньшее или то же количество строк, как если бы max_rows_to_group_by был опущен.

after_having_inclusive – Включать все строки, которые не прошли через 'max_rows_to_group_by' в 'итоги'. Иными словами, 'итоги' будут содержать большее или то же количество строк, как если бы max_rows_to_group_by был опущен.

after_having_auto – Подсчитать количество строк, которые прошли через HAVING. Если это больше определенного количества (по умолчанию 50%), включить все строки, которые не прошли через '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 -- getting the first occurred page header for each domain.
FROM hits
GROUP BY domain

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

Модификатор 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 * количество потоков от общего объема ОЗУ.

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

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