Группировка¶

Оператор GROUP BY объединяет кортежи с одинаковыми значениями в группы. Затем к каждой группе можно применять агрегатные функции, например COUNT() или SUM().

Сценарии использования¶

Используйте GROUP BY, когда нужно:

посчитать количество кортежей по категориям
найти сумму/среднее/максимум по группам
получить статистику по определенным полям

См. также:

SELECT

Примеры запросов¶

Создадим таблицу и добавим в нее данные, которые подходят для группировки:

CREATE TABLE customers (
    id INTEGER NOT NULL,
    name TEXT NOT NULL,
    amount INTEGER,
    PRIMARY KEY (id))
USING memtx DISTRIBUTED BY (id)
OPTION (TIMEOUT = 3.0);

INSERT INTO customers VALUES
    (1, 'alice', 778),
    (2, 'bob', 22),
    (3, 'john', 62),
    (4, 'bob', 990),
    (5, 'alice', 24),
    (6, 'bob', 22);

Использование без функций¶

Без агрегатных функций GROUP BY возвращает уникальные сочетания значений. ORDER BY в примере ниже нужен только для фиксированного порядка кортежей:

SELECT name, amount FROM customers
GROUP BY amount, name
ORDER BY amount, name;

Результат:

+-------+--------+
| name  | amount |
+================+
| bob   | 22     |
|-------+--------|
| alice | 24     |
|-------+--------|
| john  | 62     |
|-------+--------|
| alice | 778    |
|-------+--------|
| bob   | 990    |
+-------+--------+
(5 rows)

GROUP BY не заменяет ORDER BY, но меняет состав результата: два кортежа ('bob', 22) превращаются в одну группу. Если нужен предсказуемый порядок вывода, используйте ORDER BY.

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

Без ORDER BY порядок кортежей в результате запроса в Picodata не гарантируется.

Группировка и фильтрация исходных строк¶

Сначала WHERE отбирает исходные кортежи, затем GROUP BY объединяет оставшиеся кортежи в группы:

SELECT name, COUNT(*) AS count FROM customers
WHERE id != 3
GROUP BY name
ORDER BY name;

Запрос вернет число кортежей для каждого имени:

+-------+-------+
| name  | count |
+===============+
| alice | 2     |
|-------+-------|
| bob   | 3     |
+-------+-------+
(2 rows)

Группировка и фильтрация итоговых групп¶

Для фильтрации уже сформированных групп используйте HAVING. В отличие от WHERE, он применяется не к исходным кортежам, а к группам:

SELECT name, SUM(amount) AS total FROM customers
GROUP BY name
HAVING SUM(amount) > 100
ORDER BY name;

Результат:

+-------+-------+
| name  | total |
+===============+
| alice | 802   |
|-------+-------|
| bob   | 1034  |
+-------+-------+
(2 rows)

Правило простое: каждое неагрегированное выражение из SELECT должно присутствовать в GROUP BY. Для агрегатов (COUNT, SUM и т.д.) это не требуется.

Агрегатные функции¶

Агрегатная функция вычисляет одно значение для набора строк. С GROUP BY результат вычисляется для каждой группы. Без GROUP BY функция обрабатывает весь набор строк.

Поддерживаются AVG, COUNT, GROUP_CONCAT, MAX, MIN, STRING_AGG, SUM и TOTAL. Тип результата и поведение при отсутствии значений описаны на страницах соответствующих функций.

Синтаксис¶

Выражение¶

Диаграмма

Литерал¶

Диаграмма

DISTINCT¶

DISTINCT позволяет учитывать только уникальные значения выражения:

SELECT COUNT(DISTINCT amount) FROM customers;

У GROUP_CONCAT и STRING_AGG с DISTINCT разрешен только один аргумент: свой разделитель задать нельзя.

Все агрегатные функции можно использовать как оконные с OVER. В оконных вызовах DISTINCT не поддерживается.

Порядок обработки¶

Логический порядок обработки запроса с WHERE, GROUP BY, HAVING и ORDER BY такой:

Фильтрация исходных кортежей (WHERE)
Группировка кортежей (GROUP BY)
Фильтрация получившихся групп (HAVING)
Сортировка итогового результата (ORDER BY)

План исполнения запроса

EXPLAIN (RAW, FMT) SELECT name, amount FROM customers WHERE id !=3 GROUP BY amount,name HAVING sum(amount) > 100 ORDER BY name;

╭──────────────────────────╮
│ 1. Query (WHOLE STORAGE) │
╰──────────────────────────╯

SELECT
  "customers"."amount" as "gr_expr_1",
  "customers"."name" as "gr_expr_2",
  sum (CAST ("customers"."amount" as int)) as "sum_1"
FROM
  "customers"
WHERE
  "customers"."id" <> CAST(3 AS int)
GROUP BY
  "customers"."amount",
  "customers"."name"

plan:
    [0] SCAN TABLE customers (~983040 rows)
    [0] USE TEMP B-TREE FOR GROUP BY

╭───────────────────╮
│ 2. Query (ROUTER) │
╰───────────────────╯

SELECT
  "name",
  "amount"
FROM
  (
    SELECT
      "COL_1" as "name",
      "COL_0" as "amount"
    FROM
      (
        SELECT
          "COL_0",
          "COL_1",
          "COL_2"
        FROM
          "_tmp_3176007081912592988_0136"
      )
    GROUP BY
      "COL_0",
      "COL_1"
    HAVING
      sum ("COL_2") > CAST(100 AS int)
  )
ORDER BY
  "name"

plan:
    [0] SCAN TABLE _tmp_3176007081912592988_0136 (~1048576 rows)
    [0] USE TEMP B-TREE FOR GROUP BY
    [0] USE TEMP B-TREE FOR ORDER BY

См. также:

Перемещение данных¶

В распределенном запросе GROUP BY может потребоваться перемещение данных: все зависит от того, находятся ли нужные кортежи в одном или нескольких бакетах.

Если планировщик доказывает, что запрос выполняется в пределах одного бакета, перемещения данных не будет. Так бывает, когда WHERE фиксирует ключ шардирования:

EXPLAIN (LOGICAL) SELECT id, SUM(amount) FROM customers WHERE id = 1 GROUP BY id;

Существенная часть плана

projection (customers.id::int -> id, sum(customers.amount::int::int)::decimal -> col_1)
  group by (customers.id::int)
    selection (customers.id::int = 1::int)
      scan customers

См. также:

Отсутствие перемещения.

Важно не то, какая колонка указана в GROUP BY, а сколько бакетов затрагивает запрос после фильтрации.

Если после фильтрации нужны данные из нескольких бакетов, используется один из вариантов перемещения данных:

локальная вставка/материализация (данные в пределах одного узла)
частичное/полное перемещение (данные расположены на разных узлах)

Один из таких вариантов — полное перемещение данных. Оно часто появляется, когда группировка идет по колонке, не входящей в ключ распределения, а запрос не ограничен одним бакетом. В примере ниже фильтр id <> 3 оставляет диапазон buckets = [1-16384], поэтому в плане появляется motion [policy: full]:

EXPLAIN (LOGICAL) SELECT name, COUNT(*) AS count FROM customers
WHERE id <> 3
GROUP BY name;

Существенная часть плана

projection (gr_expr_1::string -> name, sum(count_1::int::int)::int::int -> count)
  group by (gr_expr_1::string)
    motion [policy: full, program: ReshardIfNeeded]
      projection (customers.name::string -> gr_expr_1, count(*)::int -> count_1)
        group by (customers.name::string)
          selection (customers.id::int <> 3::int)
            scan customers

См. также:

Перемещение данных