Группировка

Оператор 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 и т.д.) это не требуется.

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

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

1. Фильтрация исходных кортежей (WHERE)
2. Группировка кортежей (GROUP BY)
3. Фильтрация получившихся групп (HAVING)
4. Сортировка итогового результата (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

См. также:

• EXPLAIN

• Фасет RAW

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

В распределённом запросе 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 -> 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

См. также:

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