🟢 Junior Level

GROUP BY — об’єднує рядки з однаковими значеннями у групи і дозволяє застосовувати до них агрегатні функції.

Проста аналогія: У вас є кошик з фруктами різних типів. GROUP BY — це коли ви розкладаєте їх по купках: яблука окремо, банани окремо, апельсини окремо. Потім можете порахувати, скільки фруктів у кожній купці.

Приклад:

-- Таблиця orders:
-- user_id | amount
-- 1       | 100
-- 1       | 200
-- 2       | 150
-- 2       | 50

-- Групуємо за user_id і рахуємо суму
SELECT user_id, SUM(amount) as total
FROM orders
GROUP BY user_id;

-- Результат:
-- user_id | total
-- 1       | 300
-- 2       | 200

Основні агрегатні функції:

• COUNT(*) — кількість рядків
• SUM(col) — сума
• AVG(col) — середнє
• MIN(col) / MAX(col) — мінімум/максимум

---

🟡 Middle Level

Як це працює всередині

PostgreSQL використовує дві стратегії для GROUP BY:

work_mem — параметр PostgreSQL, що обмежує об’єм пам’яті для однієї операції. За замовчуванням 4MB. Spill to disk — коли операція не поміщається у work_mem, PostgreSQL записує проміжні дані на диск у тимчасові файли. Це сповільнює виконання у 10-100 разів.

1. HashAggregate

1. Створюємо хеш-таблицю в пам'яті (work_mem)
2. Для кожного рядка:
   - Обчислюємо хеш від ключа групування
   - Якщо ключ є у хеші → оновлюємо агрегат
   - Якщо ні → створюємо новий запис

• Коли: Дані не відсортовані
• Вимагає: work_mem для зберігання всіх груп

2. GroupAggregate

1. Сортуємо дані за ключем групування
2. Йдемо за відсортованими даними і групуємо підряд однакові ключі

• Коли: Дані вже відсортовані (за індексом)
• Плюс: Майже не вимагає пам’яті

Порівняння стратегій

Стратегія	Коли	Пам’ять	Сортування
HashAggregate	Дані не відсортовані	work_mem	Не потрібне
GroupAggregate	Дані відсортовані	Мінімум	Потрібне (або індекс)

Обробка NULL

-- Всі NULL групуються РАЗОМ
SELECT status, COUNT(*)
FROM orders
GROUP BY status;

-- Результат:
-- status   | count
-- ACTIVE   | 100
-- PENDING  | 50
-- NULL     | 10   ← Всі NULL в одній групі!

Типові помилки

1. Забули колонку у GROUP BY

   -- ❌ ПОМИЛКА: name не у GROUP BY
   SELECT user_id, name, SUM(amount)
   FROM orders
   GROUP BY user_id;
   
   -- ✅ Або додайте name у GROUP BY
   SELECT user_id, name, SUM(amount)
   FROM orders
   GROUP BY user_id, name;
   
   -- ✅ Або використайте агрегат
   SELECT user_id, MAX(name), SUM(amount)
   FROM orders
   GROUP BY user_id;
   

2. Виняток: групування за PRIMARY KEY

   -- ✅ Можна! PK функціонально визначає всі колонки
   SELECT u.id, u.name, u.email, COUNT(o.id)
   FROM users u
   LEFT JOIN orders o ON u.id = o.user_id
   GROUP BY u.id;  -- name та email можна не вказувати
   

3. Фільтрація після GROUP BY

   -- ❌ WHERE не можна використовувати з агрегатами
   SELECT user_id, COUNT(*) FROM orders WHERE COUNT(*) > 5 GROUP BY user_id;
   
   -- ✅ Використовуйте HAVING
   SELECT user_id, COUNT(*) FROM orders GROUP BY user_id HAVING COUNT(*) > 5;
   

Практичні приклади

-- Пошук дублікатів
SELECT email, COUNT(*) as cnt
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

-- Агрегація з умовами (FILTER)
SELECT
    department,
    COUNT(*) FILTER (WHERE status = 'ACTIVE') as active_count,
    COUNT(*) FILTER (WHERE status = 'INACTIVE') as inactive_count
FROM employees
GROUP BY department;

---

🔴 Senior Level

Коли НЕ використовувати GROUP BY

1. Коли потрібні деталі кожного рядка — використовуйте віконні функції замість GROUP BY (вони не згортають рядки)
2. Коли груп занадто багато (мільйони) — використовуйте пре-агрегацію через CTE або Materialized Views
3. Коли потрібно лише перевірити існування — використовуйте EXISTS (Semi-Join), а не GROUP BY + HAVING COUNT(*) > 0

Фізичні стратегії: Deep Dive

HashAggregate у деталях:

Phase 1: Build
  For each row:
    hash_key = hash(group_by_columns)
    if hash_key in hash_table:
      update_aggregate(hash_table[hash_key], row)
    else:
      create_new_group(hash_key, row)

Phase 2: Output
  For each group in hash_table:
    emit(group)

Spill to Disk (PG 13+):

⚠️ ВАЖЛИВО для міграції: До PG 13 запит з HashAggregate, що перевищує work_mem, завершувався помилкою out of memory. На PG 13+ той самий запит виконається, але мовчки піде на диск. При міграції з PG 12 на PG 13 запити, які раніше падали, почнуть працювати — але екстремально повільно. Моніторте Disk: X kB в EXPLAIN!

До PG 13: HashAggregate не влізає у work_mem → помилка
PG 13+:   HashAggregate скидає частини хешу на диск
          → Обробляє batch за batch
          → Повільніше, ніж у пам'яті, але працює!

GroupAggregate + Index:

-- Якщо є індекс за колонкою групування
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- Запит використовує Index Scan → дані вже відсортовані
SELECT user_id, SUM(amount) FROM orders GROUP BY user_id;
-- Plan: GroupAggregate → Index Scan (без Sort!)
-- → Мінімум пам'яті, максимум швидкості

Паралельна агрегація

-- PostgreSQL розбиває роботу на воркери
EXPLAIN SELECT department, COUNT(*) FROM employees GROUP BY department;

-- Plan:
-- Finalize GroupAggregate        ← Головний процес об'єднує результати від воркерів
--   Gather                        ← Вузол збору даних від паралельних воркерів
--     Partial GroupAggregate      ← Воркер 1: групує СВОЮ частину даних
--       Parallel Seq Scan         ← Воркер 1 паралельно читає таблицю
--     Partial GroupAggregate      ← Воркер 2: групує СВОЮ частину даних
--       Parallel Seq Scan         ← Воркер 2 паралельно читає таблицю

-- Кожен воркер читає свою частину таблиці, групує її (Partial),
-- потім головний процес об'єднує проміжні результати (Finalize).

Як працює:

1. Кожен воркер групує свою частину даних (Partial Aggregate)
2. Головний процес об’єднує проміжні результати (Combine)
3. Фіналізує агрегати (Finalize)

Обмеження:

• Не всі агрегатні функції підтримують паралелізм
• COUNT, SUM, AVG — підтримують
• Кастомні агрегати — потрібно перевірити proparallel у pg_proc

ROLLUP, CUBE, GROUPING SETS

ROLLUP — ієрархічні підсумки:

SELECT department, city, COUNT(*)
FROM employees
GROUP BY ROLLUP(department, city);

-- Результат:
-- dept    | city     | count
-- IT      | Moscow   | 10
-- IT      | SPb      | 5
-- IT      | NULL     | 15   ← Підсумок по IT
-- NULL    | NULL     | 50   ← Підсумок по всіх

CUBE — всі комбінації:

SELECT department, city, COUNT(*)
FROM employees
GROUP BY CUBE(department, city);

-- Результат включає:
-- (dept, city), (dept, NULL), (NULL, city), (NULL, NULL)
-- 4 рівні деталізації за ОДИН прохід!

GROUPING SETS — явний список:

SELECT department, city, COUNT(*)
FROM employees
GROUP BY GROUPING SETS (
    (department, city),  -- Деталізація
    (department),        -- Підсумок по відділу
    ()                   -- Підсумок по всіх
);

Чому це краще UNION ALL:

-- ❌ 3 проходи по таблиці
SELECT department, city, COUNT(*) FROM employees GROUP BY department, city
UNION ALL
SELECT department, NULL, COUNT(*) FROM employees GROUP BY department
UNION ALL
SELECT NULL, NULL, COUNT(*) FROM employees;

-- ✅ 1 прохід (Hash або Sort)
SELECT department, city, COUNT(*) FROM employees
GROUP BY ROLLUP(department, city);

FILTER (Clean SQL)

-- Елегантна агрегація без CASE WHEN
SELECT
    department,
    COUNT(*) as total,
    COUNT(*) FILTER (WHERE salary > 100000) as high_paid,
    COUNT(*) FILTER (WHERE hire_date > '2023-01-01') as new_hires,
    AVG(salary) FILTER (WHERE role = 'Senior') as senior_avg_salary
FROM employees
GROUP BY department;

Incremental Sort (PG 13+)

-- Індекс за (department, hire_date)
CREATE INDEX idx_emp_dept_date ON employees(department, hire_date);

-- Запит з GROUP BY та ORDER BY
SELECT department, hire_date, COUNT(*)
FROM employees
GROUP BY department, hire_date
ORDER BY department, hire_date;

-- PG 13+: Incremental Sort
-- Дані вже відсортовані за department (з індексу)
→ Сортуємо лише за hire_date всередині кожного department
→ У 5-10 разів швидше повного сортування!

На PG 12 і нижче — повне сортування всіх колонок ORDER BY, навіть якщо частина вже відсортована індексом.

Edge Cases

1. Порожній результат GROUP BY

   -- Якщо таблиця порожня → 0 рядків (не NULL!)
   SELECT status, COUNT(*) FROM empty_table GROUP BY status;
   -- Result: 0 rows
   

2. GROUP BY з виразами

   -- Групування за виразом
   SELECT
       EXTRACT(YEAR FROM created_at) as year,
       EXTRACT(MONTH FROM created_at) as month,
       COUNT(*)
   FROM orders
   GROUP BY year, month
   ORDER BY year, month;
   

3. GROUPING() функція

   -- Визначаємо, чи є рядок підсумковим
   SELECT
       department,
       city,
       COUNT(*),
       GROUPING(department) as is_dept_total,  -- 1 якщо NULL з ROLLUP
       GROUPING(city) as is_city_total
   FROM employees
   GROUP BY ROLLUP(department, city);
   

Performance Tuning

work_mem для HashAggregate:

-- Перевірка поточного значення
SHOW work_mem;  -- 4MB за замовчуванням

-- Якщо бачите spill на диск → збільште
SET work_mem = '256MB';
SELECT department, COUNT(*) FROM large_table GROUP BY department;

Індекс для GroupAggregate:

-- Створюємо індекс за колонками GROUP BY
CREATE INDEX idx_orders_status_date ON orders(status, created_at);

-- Запит без Sort
SELECT status, created_at, COUNT(*)
FROM orders
GROUP BY status, created_at;
-- Plan: GroupAggregate → Index Only Scan

Production Experience

Реальний сценарій #1: HashAggregate Spill

• Аналітика: 200 млн рядків, GROUP BY за 5 колонками
• work_mem = 4MB → spill 15 ГБ на диск
• Час: 8 хвилин
• Рішення: SET work_mem = '512MB' → все у пам’яті
• Результат: 45 секунд (прискорення у 10 разів)

Реальний сценарій #2: CUBE замість UNION ALL

• Звіт: 8 UNION ALL для різних розрізів
• Час: 2 хвилини (8 проходів по таблиці)
• Рішення: GROUP BY CUBE(region, product, month)
• Результат: 15 секунд (1 прохід)

Monitoring

-- 1. Перевірка стратегії агрегації
EXPLAIN (ANALYZE, BUFFERS)
SELECT department, COUNT(*) FROM employees GROUP BY department;
-- HashAggregate vs GroupAggregate

-- 2. Перевірка spill
EXPLAIN (ANALYZE)
SELECT ... GROUP BY ...;
-- Шукайте: "Disk: X kB" → погано!

-- 3. Перевірка паралелізму
EXPLAIN SELECT ... GROUP BY ...;
-- Workers Planned: 4 → чудово
-- Workers Planned: 0 → перевірте max_parallel_workers_per_gather

-- 4. Пошук повільних GROUP BY
SELECT query, mean_exec_time
FROM pg_stat_statements
WHERE query LIKE '%GROUP BY%'
ORDER BY mean_exec_time DESC
LIMIT 10;

Best Practices

1. Індекс за колонками GROUP BY → GroupAggregate без Sort
2. work_mem контролюйте при HashAggregate
3. FILTER замість CASE WHEN в агрегатах
4. ROLLUP/CUBE/GROUPING SETS замість UNION ALL
5. PK у GROUP BY дозволяє не вказувати інші колонки таблиці
6. Паралелізм перевірте через EXPLAIN
7. Incremental Sort (PG 13+) використовуйте з індексами

Резюме для Senior

• 2 стратегії: HashAggregate (вимагає work_mem) vs GroupAggregate (вимагає Sort/індекс)
• PG 13+: HashAggregate вміє spill → не падає, але повільніше
• Паралельна агрегація: Partial → Gather → Finalize
• ROLLUP/CUBE/GROUPING SETS: один прохід замість UNION ALL
• FILTER: чистий SQL для умовної агрегації
• Incremental Sort (PG 13+): економія при partial сортуванні
• Індекс за GROUP BY → GroupAggregate без Sort → мінімум RAM
• Завжди перевіряйте план: Hash vs Group, Workers, Spill

---

🎯 Шпаргалка для інтерв’ю

Обов’язково знати:

• 2 стратегії: HashAggregate (хеш-таблиця у work_mem) vs GroupAggregate (відсортовані дані)
• HashAggregate: O(N), вимагає work_mem, PG 13+ вміє spill на диск
• GroupAggregate: O(N log N) для сортування, але мінімум RAM; з індексом → без Sort
• Паралельна агрегація: Partial (воркери) → Gather → Finalize (головний процес)
• ROLLUP: ієрархічні підсумки, CUBE: всі комбінації, GROUPING SETS: явний список
• ROLLUP/CUBE/GROUPING SETS = 1 прохід vs UNION ALL = N проходів
• FILTER: COUNT(*) FILTER (WHERE status = 'ACTIVE') — краще CASE WHEN
• Incremental Sort (PG 13+): дані частково відсортовані → сортуємо решту
• Індекс за колонками GROUP BY → GroupAggregate без Sort
• NULL групуються разом

Часті уточнюючі запитання:

• «Коли HashAggregate кращий за GroupAggregate?» → Дані не відсортовані + поміщаються у work_mem
• «Що буде, якщо HashAggregate не влізе у work_mem?» → PG 13+ spill на диск, до PG 13 — помилка
• «Чому ROLLUP кращий за UNION ALL?» → 1 прохід замість N
• «Як прискорити GROUP BY на 200 млн рядків?» → work_mem ↑, індекс за GROUP BY, паралелізм

Червоні прапорці (НЕ говорити):

• ❌ «GROUP BY завжди сортує» (HashAggregate не сортує)
• ❌ «spill на диск — це нормально» (у 10-100 разів повільніше!)
• ❌ «UNION ALL для різних розрізів — норма» (CUBE/GROUPING SETS швидше)

Пов’язані теми:

• [[В чому різниця між WHERE та HAVING]] → фільтрація до/після GROUP BY
• [[Коли потрібно використовувати HAVING]] → фільтрація за агрегатами
• [[Що таке віконні функції]] → альтернатива для агрегації без групування