COUNT, SUM, AVG, MIN, MAX, GROUP BY, HAVING,ROLLUP
Агрегация — это процесс вычисления одного результирующего значения из набора строк. Это фундаментальная возможность SQL, которая превращает PostgreSQL из простого хранилища данных в мощный аналитический инструмент.
Зачем нужна агрегация:
PostgreSQL поддерживает богатый набор встроенных агрегатных функций.
| Функция | Описание | Пример | Возвращает | Игнорирует NULL |
|---|---|---|---|---|
| COUNT | Количество строк | COUNT(*) | bigint | Нет (для *) |
| SUM | Сумма значений | SUM(amount) | Тот же тип | Да |
| AVG | Среднее значение | AVG(age) | numeric | Да |
| MIN | Минимальное значение | MIN(price) | Тот же тип | Да |
| MAX | Максимальное значение | MAX(created_at) | Тот же тип | Да |
| Функция | Описание | Пример |
|---|---|---|
| STRING_AGG | Конкатенация строк | STRING_AGG(name, ', ') |
| ARRAY_AGG | Агрегация в массив | ARRAY_AGG(id) |
| JSONB_AGG | Агрегация в JSONB-массив | JSONB_AGG(row_to_json(t)) |
| JSONB_OBJECT_AGG | Агрегация в JSONB-объект | JSONB_OBJECT_AGG(key, value) |
| MODE | Наиболее частое значение | MODE() WITHIN GROUP (ORDER BY value) |
| MEDIAN | Медианное значение | PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY value) |
| STDDEV | Стандартное отклонение | STDDEV(price) |
| VARIANCE | Дисперсия | VARIANCE(price) |
-- COUNT(*) — считает ВСЕ строки, включая те, где все столбцы NULL
SELECT COUNT(*) FROM users;
-- Результат: 100 (все строки в таблице)
-- COUNT(column) — считает только строки, где column НЕ NULL
SELECT COUNT(email) FROM users;
-- Результат: 95 (если у 5 пользователей email = NULL)
-- COUNT(DISTINCT column) — считает уникальные не-NULL значения
SELECT COUNT(DISTINCT city) FROM users;
-- Результат: 10 (если есть 10 уникальных городов)Таблица users:
+----+-------+----------+
| id | name | city |
+----+-------+----------+
| 1 | Alice | Moscow |
| 2 | Bob | Moscow |
| 3 | Carol | London |
| 4 | Dave | NULL | ← city = NULL
| 5 | Eve | Paris |
+----+-------+----------+
COUNT(*) = 5 (все строки)
COUNT(city) = 4 (исключая NULL)
COUNT(DISTINCT city) = 3 (Moscow, London, Paris)
-- ❌ Медленно на больших таблицах: полный скан
SELECT COUNT(*) FROM large_table;
-- ✅ Быстро: использование приблизительного подсчёта
SELECT reltuples::bigint AS estimated_count
FROM pg_class
WHERE relname = 'large_table';
-- ✅ Быстро: использование покрытиявающего индекса
CREATE INDEX idx_users_id ON users(id); -- id NOT NULL
SELECT COUNT(id) FROM users; -- Индекс-скан вместо табличного-- Количество активных пользователей
SELECT COUNT(*) FILTER (WHERE is_active = true) AS active_users
FROM users;
-- Количество пользователей с email
SELECT COUNT(email) AS users_with_email
FROM users;
-- Количество уникальных городов
SELECT COUNT(DISTINCT city) AS unique_cities
FROM users;
-- Группировка с подсчётом
SELECT
city,
COUNT(*) AS total_users,
COUNT(email) AS users_with_email
FROM users
GROUP BY city;-- Статистика по зарплатам
SELECT
COUNT(*) AS employee_count,
SUM(salary) AS total_payroll, -- Общий фонд оплаты труда
AVG(salary) AS avg_salary, -- Средняя зарплата
MIN(salary) AS min_salary, -- Минимальная зарплата
MAX(salary) AS max_salary -- Максимальная зарплата
FROM employees;-- Важно: агрегатные функции игнорируют NULL
-- Если все значения NULL — результат NULL
SELECT
AVG(salary) AS avg_salary, -- NULL, если все зарплаты NULL
COALESCE(AVG(salary), 0) AS avg_salary_safe -- 0 вместо NULL
FROM employees;-- Исключение выбросов из расчёта
SELECT AVG(salary)
FROM employees
WHERE salary IS NOT NULL AND salary BETWEEN 0 AND 1000000;
-- Замена NULL на значение по умолчанию
SELECT AVG(COALESCE(salary, 0)) FROM employees;PostgreSQL поддерживает фильтрацию внутри агрегатных функций:
-- Количество активных и неактивных пользователей в одном запросе
SELECT
COUNT(*) FILTER (WHERE is_active = true) AS active_count,
COUNT(*) FILTER (WHERE is_active = false) AS inactive_count,
COUNT(*) AS total_count
FROM users;
-- Средняя зарплата по отделам с фильтрацией
SELECT
department,
AVG(salary) FILTER (WHERE is_full_time = true) AS avg_full_time,
AVG(salary) FILTER (WHERE is_full_time = false) AS avg_part_time,
AVG(salary) AS avg_all
FROM employees
GROUP BY department;Преимущество FILTER: Один проход по таблице вместо нескольких подзапросов.
-- Список всех городов через запятую
SELECT STRING_AGG(city, ', ') AS all_cities
FROM users;
-- Результат: "Moscow, London, Paris, Berlin"
-- С сортировкой
SELECT STRING_AGG(city, ', ' ORDER BY city) AS all_cities_sorted
FROM users;
-- С уникальными значениями
SELECT STRING_AGG(DISTINCT city, ', ') AS unique_cities
FROM users;-- Все ID пользователей в массив
SELECT ARRAY_AGG(id) AS user_ids
FROM users;
-- Результат: {1, 2, 3, 4, 5}
-- Массив имён по городам
SELECT
city,
ARRAY_AGG(name) AS names
FROM users
GROUP BY city;
-- Результат:
-- city | names
-- -------+------------------
-- Moscow | {Alice, Bob}
-- London | {Carol}
-- Массив с сортировкой
SELECT
city,
ARRAY_AGG(name ORDER BY name) AS names_sorted
FROM users
GROUP BY city;GROUP BY объединяет строки с одинаковыми значениями в группы, применяя агрегатные функции к каждой группе.
-- Простая группировка
SELECT city, COUNT(*) AS user_count
FROM users
GROUP BY city;Что происходит:
userscityCOUNT(*)Исходные данные:
+----+-------+----------+-----+
| id | name | city | age |
+----+-------+----------+-----+
| 1 | Alice | Moscow | 25 |
| 2 | Bob | Moscow | 30 |
| 3 | Carol | London | 28 |
| 4 | Dave | London | 35 |
| 5 | Eve | Paris | 22 |
+----+-------+----------+-----+
После GROUP BY city:
+--------+-------+-------------+-------------+
| city | COUNT | SUM(age) | AVG(age) |
+--------+-------+-------------+-------------+
| Moscow | 2 | 55 | 27.5 |
| London | 2 | 63 | 31.5 |
| Paris | 1 | 22 | 22.0 |
+--------+-------+-------------+-------------+
-- Группировка по городу и возрасту
SELECT city, age, COUNT(*) AS count
FROM users
GROUP BY city, age
ORDER BY city, count DESC;
-- Результат:
-- city | age | count
-- -------+-----+------
-- Moscow | 25 | 1
-- Moscow | 30 | 1
-- London | 28 | 1
-- London | 35 | 1
-- Paris | 22 | 1-- Группировка по году рождения
SELECT
EXTRACT(YEAR FROM birth_date) AS birth_year,
COUNT(*) AS people_count
FROM users
GROUP BY EXTRACT(YEAR FROM birth_date)
ORDER BY birth_year;
-- Группировка по диапазону возраста
SELECT
CASE
WHEN age < 25 THEN '18-24'
WHEN age < 35 THEN '25-34'
WHEN age < 45 THEN '35-44'
ELSE '45+'
END AS age_group,
COUNT(*) AS count
FROM users
GROUP BY
CASE
WHEN age < 25 THEN '18-24'
WHEN age < 35 THEN '25-34'
WHEN age < 45 THEN '35-44'
ELSE '45+'
END
ORDER BY age_group;-- Новые пользователи по месяцам
SELECT
DATE_TRUNC('month', created_at) AS month,
COUNT(*) AS new_users
FROM users
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month;
-- Результат:
-- month | new_users
-- ------------+-----------
-- 2024-01-01 | 150
-- 2024-02-01 | 180
-- 2024-03-01 | 200
-- Продажи по дням недели
SELECT
EXTRACT(DOW FROM order_date) AS day_of_week,
SUM(total_amount) AS revenue
FROM orders
GROUP BY EXTRACT(DOW FROM order_date)
ORDER BY day_of_week;-- WHERE фильтрует строки ДО группировки
-- HAVING фильтрует группы ПОСЛЕ группировки
SELECT
city,
COUNT(*) AS user_count
FROM users
WHERE age > 25 -- Фильтруем строки: только пользователи старше 25
GROUP BY city
HAVING COUNT(*) > 10; -- Фильтруем группы: только города с >10 пользователями┌─────────────────────────────────────────┐
│ 1. FROM / JOIN │
│ └─ Загрузка и объединение таблиц │
│ │
│ 2. WHERE │
│ └─ Фильтрация строк │
│ │
│ 3. GROUP BY │
│ └─ Группировка строк │
│ │
│ 4. HAVING │
│ └─ Фильтрация групп │
│ │
│ 5. SELECT │
│ └─ Выбор столбцов, вычисления │
│ │
│ 6. ORDER BY │
│ └─ Сортировка результатов │
│ │
│ 7. LIMIT / OFFSET │
│ └─ Ограничение вывода │
└─────────────────────────────────────────┘
-- Города с более чем 100 пользователями
SELECT city, COUNT(*) AS user_count
FROM users
GROUP BY city
HAVING COUNT(*) > 100;
-- Пользователи с общей суммой заказов > 10000
SELECT
user_id,
SUM(total_amount) AS total_spent
FROM orders
GROUP BY user_id
HAVING SUM(total_amount) > 10000;
-- Товары, которые заказывали более 5 раз
SELECT
product_id,
COUNT(*) AS order_count
FROM order_items
GROUP BY product_id
HAVING COUNT(*) > 5;
-- Комбинация условий
SELECT
department,
AVG(salary) AS avg_salary,
COUNT(*) AS emp_count
FROM employees
WHERE is_active = true
GROUP BY department
HAVING AVG(salary) > 50000 AND COUNT(*) > 10;ROLLUP создаёт иерархические итоги — от деталей к общему, двигаясь слева направо по списку столбцов.
Простыми словами: ROLLUP автоматически добавляет строки с промежуточными суммами, как подытоги в финансовом отчёте.
GROUP BY ROLLUP (column1, column2, ...)ROLLUP(a, b) создаёт три уровня группировки:
(a, b) — детальные группы (как обычный GROUP BY a, b)(a) — промежуточный итог по первому столбцу() — общий итог по всем строкам-- Исходные данные
+----+--------+------------+--------+
| id | city | department | salary |
+----+--------+------------+--------+
| 1 | Moscow | Sales | 50000 |
| 2 | Moscow | Sales | 60000 |
| 3 | Moscow | IT | 80000 |
| 4 | London | Sales | 55000 |
| 5 | London | IT | 75000 |
| 6 | London | IT | 85000 |
+----+--------+------------+--------+
-- Иерархические итоги
SELECT
city,
department,
SUM(salary) AS total_salary,
COUNT(*) AS emp_count
FROM employees
GROUP BY ROLLUP (city, department);Результат:
+--------+------------+---------------+-----------+---------------------------+
| city | department | total_salary | emp_count | Пояснение |
+--------+------------+---------------+-----------+---------------------------+
| Moscow | Sales | 110000 | 2 | Детали: Moscow + Sales |
| Moscow | IT | 80000 | 1 | Детали: Moscow + IT |
| Moscow | NULL | 190000 | 3 | Итог по Moscow |
| London | Sales | 55000 | 1 | Детали: London + Sales |
| London | IT | 160000 | 2 | Детали: London + IT |
| London | NULL | 215000 | 3 | Итог по London |
| NULL | NULL | 405000 | 6 | Общий итог |
+--------+------------+---------------+-----------+---------------------------+
NULL в результате означает «итого по этому столбцу»:
city = Moscow, department = NULL → «все отделы в Moscow»city = NULL, department = NULL → «все города, все отделы»-- Иерархия: год → месяц → день
SELECT
EXTRACT(YEAR FROM order_date) AS year,
EXTRACT(MONTH FROM order_date) AS month,
EXTRACT(DAY FROM order_date) AS day,
SUM(total_amount) AS revenue,
COUNT(*) AS order_count
FROM orders
GROUP BY ROLLUP (
EXTRACT(YEAR FROM order_date),
EXTRACT(MONTH FROM order_date),
EXTRACT(DAY FROM order_date)
)
ORDER BY year, month, day;Создаёт 4 уровня:
(year, month, day) — выручка по дням(year, month) — выручка по месяцам (итог по дням)(year) — выручка по годам (итог по месяцам)() — общая выручка-- Иерархия: регион → страна → город
SELECT
region,
country,
city,
SUM(revenue) AS total_revenue
FROM sales
GROUP BY ROLLUP (region, country, city);CUBE создаёт все возможные комбинации группировки, включая перекрёстные итоги.
Простыми словами: CUBE отвечает на вопрос «покажи статистику по всем возможным сочетаниям столбцов».
GROUP BY CUBE (column1, column2, ...)CUBE(a, b) создаёт 4 комбинации:
(a, b) — детали(a) — итог по первому столбцу(b) — итог по второму столбцу() — общий итогДля n столбцов: 2^n комбинаций.
SELECT
city,
department,
COUNT(*) AS emp_count
FROM employees
GROUP BY CUBE (city, department);Результат CUBE(city, department):
+--------+------------+-------+---------------------------+
| city | department | count | Пояснение |
+--------+------------+-------+---------------------------+
| Moscow | Sales | 10 | Детали |
| Moscow | IT | 15 | Детали |
| London | Sales | 8 | Детали |
| London | IT | 12 | Детали |
| Moscow | NULL | 25 | Итог по Moscow |
| London | NULL | 20 | Итог по London |
| NULL | Sales | 18 | Итог по Sales (все города)|
| NULL | IT | 27 | Итог по IT (все города) |
| NULL | NULL | 45 | Общий итог |
+--------+------------+-------+---------------------------+
ROLLUP(city, department):
├── (city, department) — детали
├── (city) — итог по городу
└── () — общий итог
Итого: 3 набора
CUBE(city, department):
├── (city, department) — детали
├── (city) — итог по городу
├── (department) — итог по отделу ← ДОПОЛНИТЕЛЬНО
└── () — общий итог
Итого: 4 набора
CUBE добавляет перекрёстные итоги, которых нет в ROLLUP.
-- Сводная таблица как в Excel
SELECT
category,
region,
SUM(amount) AS total_sales
FROM sales
GROUP BY CUBE (category, region);
-- Результат покажет:
-- 1. Продажи по каждой категории и региону (детали)
-- 2. Продажи по каждой категории (все регионы)
-- 3. Продажи по каждому региону (все категории)
-- 4. Общие продажи (все категории, все регионы)GROUPING SETS позволяет точно указать, какие наборы группировки нужны.
Простыми словами: Вы сами говорите PostgreSQL: «мне нужны вот эти конкретные итоги, и только они».
GROUP BY GROUPING SETS (
(column1, column2), -- Набор 1
(column1), -- Набор 2
(column2) -- Набор 3
)SELECT
city,
department,
COUNT(*) AS emp_count
FROM employees
GROUP BY GROUPING SETS (
(city, department), -- Детали по каждому городу и отделу
(city) -- Итог по каждому городу
);Результат:
+--------+------------+-------+---------------------------+
| city | department | count | Пояснение |
+--------+------------+-------+---------------------------+
| Moscow | Sales | 10 | Детали |
| Moscow | IT | 15 | Детали |
| London | Sales | 8 | Детали |
| London | IT | 12 | Детали |
| Moscow | NULL | 25 | Итог по Moscow |
| London | NULL | 20 | Итог по London |
+--------+------------+-------+---------------------------+
-- ROLLUP: 3 набора (детали + по городу + общий итог)
GROUP BY ROLLUP (city, department)
-- CUBE: 4 набора (детали + по городу + по отделу + общий итог)
GROUP BY CUBE (city, department)
-- GROUPING SETS: только 2 набора (детали + по городу)
GROUP BY GROUPING SETS ((city, department), (city))
-- GROUPING SETS: несколько конкретных наборов
GROUP BY GROUPING SETS (
(city, department), -- Детали
(city), -- Итог по городу
(department), -- Итог по отделу
() -- Общий итог
)
-- Эквивалентно CUBE, но явно указано| Критерий | ROLLUP/CUBE | GROUPING SETS |
|---|---|---|
| Гибкость | Фиксированные наборы | Любые комбинации |
| Производительность | Может создавать лишние данные | Только нужные группы |
| Читаемость | Неявно | Явно указано, что нужно |
| Контроль | Ограниченный | Полный |
-- Отчёт для руководства:
-- 1. Детали по категориям и регионам
-- 2. Итоги по категориям
-- 3. Итоги по регионам
-- (без общего итога — он не нужен)
SELECT
category,
region,
SUM(amount) AS total_sales,
COUNT(*) AS order_count
FROM sales
GROUP BY GROUPING SETS (
(category, region),
(category),
(region)
)
ORDER BY category, region;Как понять, NULL в результате — это реальное значение из таблицы или маркер «итого»?
SELECT city, COUNT(*)
FROM employees
GROUP BY ROLLUP(city);
-- Результат:
-- city | count
-- -------+------
-- Moscow | 10
-- London | 15
-- NULL | 25 ← Это «итого» или город NULL?GROUPING(column) возвращает:
1 — если это строка «итого» (NULL сгенерирован ROLLUP/CUBE/GROUPING SETS)0 — если это обычное значение (NULL из данных или не-NULL)SELECT
city,
GROUPING(city) AS is_total, -- 1 = итог, 0 = деталь
COUNT(*) AS emp_count
FROM employees
GROUP BY ROLLUP(city);
-- Результат:
-- city | is_total | count
-- -------+----------+------
-- Moscow | 0 | 10
-- London | 0 | 15
-- NULL | 1 | 25 ← Это итогSELECT
CASE
WHEN GROUPING(city) = 1 THEN 'ВСЕГО'
WHEN city IS NULL THEN 'Не указан'
ELSE city
END AS city,
COUNT(*) AS emp_count
FROM employees
GROUP BY ROLLUP(city)
ORDER BY is_total, city;
-- Результат:
-- city | count
-- ----------+------
-- London | 15
-- Moscow | 10
-- ВСЕГО | 25SELECT
city,
department,
GROUPING(city) AS is_city_total,
GROUPING(department) AS is_dept_total,
COUNT(*) AS emp_count
FROM employees
GROUP BY CUBE(city, department);
-- Результат:
-- city | department | is_city | is_dept | count
-- -------+------------+---------+---------+------
-- Moscow | Sales | 0 | 0 | 10 ← Детали
-- Moscow | IT | 0 | 0 | 15 ← Детали
-- Moscow | NULL | 0 | 1 | 25 ← Итог по городу
-- NULL | Sales | 1 | 0 | 18 ← Итог по отделу
-- NULL | NULL | 1 | 1 | 45 ← Общий итогВажно понимать разницу между GROUP BY и оконными функциями:
-- GROUP BY: агрегация с уменьшением количества строк
SELECT city, COUNT(*) AS cnt
FROM users
GROUP BY city;
-- Результат: 1 строка на город
-- Оконная функция: агрегация без уменьшения строк
SELECT
name,
city,
COUNT(*) OVER (PARTITION BY city) AS city_count
FROM users;
-- Результат: все строки, + столбец с количеством в городе| Характеристика | GROUP BY | Оконные функции |
|---|---|---|
| Количество строк | Уменьшается | Остаётся прежним |
| Детализация | Только групповые столбцы | Все столбцы доступны |
| Использование | Отчёты, сводки | Анализ с деталями |
-- Индекс на столбце группировки ускоряет GROUP BY
CREATE INDEX idx_users_city ON users(city);
-- Запрос будет использовать индекс для группировки
SELECT city, COUNT(*)
FROM users
GROUP BY city;
-- Составной индекс для группировки по нескольким столбцам
CREATE INDEX idx_orders_user_date ON orders(user_id, order_date);
SELECT
user_id,
DATE_TRUNC('month', order_date) AS month,
SUM(total_amount) AS total
FROM orders
GROUP BY user_id, DATE_TRUNC('month', order_date);Для часто используемых агрегаций создавайте материализованные представления:
-- Создание материализованного представления
CREATE MATERIALIZED VIEW mv_monthly_sales AS
SELECT
DATE_TRUNC('month', order_date) AS month,
COUNT(*) AS order_count,
SUM(total_amount) AS revenue
FROM orders
GROUP BY DATE_TRUNC('month', order_date);
-- Быстрый запрос к агрегированным данным
SELECT * FROM mv_monthly_sales ORDER BY month;
-- Обновление данных
REFRESH MATERIALIZED VIEW mv_monthly_sales;
-- Конкурентное обновление (без блокировки чтения)
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_monthly_sales;-- ❌ Медленно на больших данных: требует сортировки/хэширования
SELECT COUNT(DISTINCT user_id) FROM orders;
-- ✅ Быстрее: использование approximate_count_distinct (расширение)
-- Требует установки расширения pg_distinct_count или аналогичного
-- ✅ Альтернатива: гиперлоглог (расширение hyperloglog)
-- SELECT hll_cardinality(hll_add_agg(hll_hash_integer(user_id))) FROM orders;SELECT
u.id,
u.name,
u.email,
COUNT(o.id) AS order_count,
SUM(o.total_amount) AS total_spent,
AVG(o.total_amount) AS avg_order_value,
MIN(o.order_date) AS first_order,
MAX(o.order_date) AS last_order
FROM users u
JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name, u.email
ORDER BY total_spent DESC
LIMIT 10;-- Удержание пользователей по месяцам
WITH user_cohorts AS (
SELECT
user_id,
DATE_TRUNC('month', created_at) AS cohort_month
FROM users
),
user_activity AS (
SELECT
o.user_id,
DATE_TRUNC('month', o.order_date) AS activity_month
FROM orders o
)
SELECT
uc.cohort_month,
COUNT(DISTINCT uc.user_id) AS cohort_size,
COUNT(DISTINCT ua.user_id) AS active_users,
ROUND(
COUNT(DISTINCT ua.user_id)::numeric / COUNT(DISTINCT uc.user_id) * 100,
2
) AS retention_rate
FROM user_cohorts uc
LEFT JOIN user_activity ua
ON uc.user_id = ua.user_id
AND uc.cohort_month = ua.activity_month
GROUP BY uc.cohort_month
ORDER BY uc.cohort_month;SELECT
stage,
COUNT(*) AS users_count,
ROUND(
COUNT(*)::numeric / SUM(COUNT(*)) OVER() * 100,
2
) AS percentage
FROM (
SELECT
user_id,
CASE
WHEN viewed_product THEN 1 ELSE 0
END AS viewed,
CASE
WHEN added_to_cart THEN 1 ELSE 0
END AS cart,
CASE
WHEN purchased THEN 1 ELSE 0
END AS purchased,
CASE
WHEN purchased THEN 'Purchased'
WHEN added_to_cart THEN 'Cart'
WHEN viewed_product THEN 'Viewed'
ELSE 'Landed'
END AS stage
FROM user_events
) subq
GROUP BY stage
ORDER BY
CASE stage
WHEN 'Landed' THEN 1
WHEN 'Viewed' THEN 2
WHEN 'Cart' THEN 3
WHEN 'Purchased' THEN 4
END;-- Дубликаты email
SELECT
email,
COUNT(*) AS cnt,
STRING_AGG(id::text, ', ') AS duplicate_ids
FROM users
GROUP BY email
HAVING COUNT(*) > 1;
-- Дубликаты составных ключей
SELECT
first_name,
last_name,
birth_date,
COUNT(*) AS cnt
FROM users
GROUP BY first_name, last_name, birth_date
HAVING COUNT(*) > 1;
-- Пользователи с несколькими заказами в один день
SELECT
user_id,
DATE(order_date) AS order_day,
COUNT(*) AS orders_count
FROM orders
GROUP BY user_id, DATE(order_date)
HAVING COUNT(*) > 1;SELECT
p.category,
p.subcategory,
COUNT(*) AS product_count,
AVG(p.price) AS avg_price,
MIN(p.price) AS min_price,
MAX(p.price) AS max_price,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY p.price) AS median_price,
STDDEV(p.price) AS price_stddev
FROM products p
WHERE p.is_active = true
GROUP BY p.category, p.subcategory
HAVING COUNT(*) > 5
ORDER BY product_count DESC;-- Иерархический отчёт по продажам
SELECT
CASE WHEN GROUPING(region) = 1 THEN 'ВСЕГО' ELSE region END AS region,
CASE WHEN GROUPING(city) = 1 THEN 'ВСЕГО' ELSE city END AS city,
SUM(amount) AS total_sales,
COUNT(*) AS order_count,
AVG(amount) AS avg_order_value
FROM sales
GROUP BY ROLLUP(region, city)
ORDER BY region, city;-- ❌ Ошибка: name не в GROUP BY и не агрегирован
SELECT name, city, COUNT(*)
FROM users
GROUP BY city;
-- ✅ Правильно: все не-агрегированные столбцы в GROUP BY
SELECT city, COUNT(*)
FROM users
GROUP BY city;
-- ✅ Или: name в GROUP BY
SELECT name, city, COUNT(*)
FROM users
GROUP BY name, city;-- ❌ Ошибка: агрегат в WHERE
SELECT city, COUNT(*)
FROM users
WHERE COUNT(*) > 10 -- Нельзя использовать агрегат в WHERE
GROUP BY city;
-- ✅ Правильно: HAVING для агрегатов
SELECT city, COUNT(*)
FROM users
GROUP BY city
HAVING COUNT(*) > 10;-- ❌ Ожидание: 0, Реальность: NULL
SELECT AVG(salary) FROM employees WHERE salary IS NULL; -- NULL
-- ✅ Правильно: обработка NULL
SELECT COALESCE(AVG(salary), 0) FROM employees WHERE salary IS NULL; -- 0
-- ❌ COUNT(*) включает все строки
SELECT department, COUNT(*) FROM employees WHERE salary > 100000 GROUP BY department;
-- Может вернуть 0 строк, если нет сотрудников с такой зарплатой
-- ✅ Проверка на пустые группы
SELECT department, COUNT(*) FILTER (WHERE salary > 100000) FROM employees GROUP BY department;-- ❌ CUBE создаёт 8 наборов для 3 столбцов — может быть слишком много данных
SELECT a, b, c, SUM(x)
FROM table
GROUP BY CUBE(a, b, c);
-- ✅ GROUPING SETS только с нужными наборами
SELECT a, b, c, SUM(x)
FROM table
GROUP BY GROUPING SETS (
(a, b, c),
(a),
(b),
(c)
);Перед использованием агрегации проверьте:
Готовы проверить знания? Переходите к вопросам!
Вопросы ещё не добавлены
Вопросы для этой подтемы ещё не добавлены.