Java Interview Questions

Главная / Все ответы / Базы данных SQL PostgreSQL

Вопрос 12 · Раздел 1

Что делает GROUP BY?

PostgreSQL использует две стратегии для GROUP BY:

Russian Базы данных SQL PostgreSQL Исходный Markdown
Версии по языкам: English Russian Ukrainian

🟢 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:

-- Если есть индекс по group_by колонке
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]] → фильтрация по агрегатам
  • [[Что такое оконные функции]] → альтернатива для агрегации без группировки