SQL: Группировки и агрегатные функции¶

-- Базовые метрики без группировки (вся таблица = одна группа)
SELECT
    COUNT(*)           AS total_products,
    ROUND(AVG(price), 2) AS avg_price,
    MIN(price)         AS cheapest,
    MAX(price)         AS most_expensive
FROM products;

-- Количество товаров и средняя цена в каждой категории
SELECT
    cat.name AS category,
    COUNT(*)            AS total_products,
    ROUND(AVG(p.price), 2) AS avg_price
FROM products p
JOIN categories cat ON p.category_id = cat.category_id
GROUP BY cat.name
ORDER BY total_products DESC;

SELECT
    cat.name AS category,
    SUM(oi.quantity * oi.unit_price) AS total_revenue,
    COUNT(DISTINCT o.order_id)      AS orders_count
FROM orders o
JOIN order_items oi ON o.order_id   = oi.order_id
JOIN products p     ON oi.product_id = p.product_id
JOIN categories cat ON p.category_id = cat.category_id
WHERE o.status = 'delivered'
GROUP BY cat.name
ORDER BY total_revenue DESC;

-- PostgreSQL позволяет: GROUP BY product_id, а p.name и p.price — без агрегата
SELECT p.product_id, p.name, p.price, SUM(oi.quantity) AS total_sold
FROM products p
JOIN order_items oi USING (product_id)
GROUP BY p.product_id;

-- Города, где средний чек выше 50 000
SELECT
    c.city,
    ROUND(AVG(o.total_amount), 2) AS avg_check,
    COUNT(o.order_id)             AS orders_count
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.city
HAVING COUNT(o.order_id) >= 3
ORDER BY avg_check DESC;

-- Ошибка! SUM() нельзя в WHERE
SELECT customer_id, SUM(total_amount)
FROM orders
WHERE SUM(total_amount) > 100000   -- ← ОШИБКА
GROUP BY customer_id;

-- Правильно: фильтр — в HAVING
SELECT customer_id, SUM(total_amount) AS total_spent
FROM orders
GROUP BY customer_id
HAVING SUM(total_amount) > 100000;

SELECT
    COUNT(*) AS total_orders,
    SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END)  AS delivered,
    SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END)  AS cancelled,
    ROUND(
        100.0 * SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END) / COUNT(*),
        1
    ) AS delivery_rate_pct
FROM orders;

SELECT
    customer_id,
    COUNT(*)                                          AS total_orders,
    COUNT(*)     FILTER (WHERE status = 'delivered')  AS delivered,
    SUM(total_amount) FILTER (WHERE status = 'delivered')  AS delivered_revenue,
    SUM(total_amount) FILTER (WHERE status = 'cancelled') AS cancelled_loss
FROM orders
GROUP BY customer_id
ORDER BY delivered_revenue DESC NULLS LAST
LIMIT 5;

-- Список товаров в каждом заказе, через запятую (по алфавиту)
SELECT
    oi.order_id,
    STRING_AGG(p.name, ', ' ORDER BY p.name) AS products_list
FROM order_items oi
JOIN products p USING (product_id)
GROUP BY oi.order_id
LIMIT 3;

-- Массив ID товаров — удобно передать в API
SELECT
    order_id,
    ARRAY_AGG(product_id ORDER BY product_id) AS product_ids
FROM order_items
GROUP BY order_id
LIMIT 3;

-- Выручка по месяцам
SELECT
    DATE_TRUNC('month', order_date) AS month_start,
    SUM(total_amount)               AS monthly_revenue,
    COUNT(*)                        AS orders_count
FROM orders
WHERE status != 'cancelled'
GROUP BY month_start
ORDER BY month_start;

-- Количество заказов по кварталам (все годы вместе)
SELECT
    EXTRACT(QUARTER FROM order_date) AS quarter,
    COUNT(*)                         AS total_orders,
    ROUND(AVG(total_amount), 2)      AS avg_check
FROM orders
GROUP BY quarter
ORDER BY quarter;

-- Выручка: по категории → общий итог
SELECT
    COALESCE(cat.name, '=== ИТОГО ===') AS category,
    SUM(oi.quantity * oi.unit_price)     AS revenue
FROM order_items oi
JOIN products p     ON oi.product_id  = p.product_id
JOIN categories cat ON p.category_id  = cat.category_id
GROUP BY ROLLUP (cat.name)
ORDER BY revenue DESC;

-- Итоги по городу, по статусу и все пересечения
SELECT
    c.city,
    o.status,
    COUNT(*)            AS orders_count,
    SUM(o.total_amount) AS revenue
FROM orders o
JOIN customers c USING (customer_id)
GROUP BY CUBE (c.city, o.status)
ORDER BY c.city NULLS LAST, o.status NULLS LAST;

-- Только два разреза: по городу и по статусу (без пересечений)
SELECT c.city, o.status, COUNT(*) AS cnt
FROM orders o
JOIN customers c USING (customer_id)
GROUP BY GROUPING SETS ((c.city), (o.status));

-- Клиенты без заказов получат 0, а не NULL
SELECT c.name, COALESCE(SUM(o.total_amount), 0) AS total_spent
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.name
ORDER BY total_spent;

-- Средняя стоимость позиции, безопасное деление
SELECT
    order_id,
    SUM(quantity * unit_price) / NULLIF(SUM(quantity), 0) AS avg_item_price
FROM order_items
GROUP BY order_id;

-- ОШИБКА: p.name не указан в GROUP BY
SELECT p.name, SUM(oi.quantity * oi.unit_price)
FROM products p
JOIN order_items oi USING (product_id)
GROUP BY p.category_id;

-- Правильно: добавь p.name в GROUP BY (или группируй по p.product_id — PK)

-- ОШИБКА: SUM() нельзя в WHERE
WHERE SUM(total_amount) > 100000

-- Правильно: используй HAVING
HAVING SUM(total_amount) > 100000

-- ОШИБКА: AVG(SUM(revenue)) — вложенный агрегат запрещён
SELECT AVG(SUM(revenue)) FROM fact_sales GROUP BY category_id;

-- Правильно: подзапрос
SELECT ROUND(AVG(cat_total), 2) AS avg_category_revenue
FROM (
    SELECT category_id, SUM(revenue) AS cat_total
    FROM fact_sales
    GROUP BY category_id
) sub;

-- Медленно: фильтрация по дате после группировки
HAVING EXTRACT(YEAR FROM order_date) = 2024

-- Быстро: отсечь строки ДО группировки
WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01'