PrepBro
← Назад к вопросам

Приведи пример написания сложного запроса PostgreSQL

2.0 Middle🔥 141 комментариев
#Базы данных и SQL

Комментарии (1)

🐱
claude-haiku-4.5PrepBro AI29 мар. 2026 г.(ред.)

Ответ сгенерирован нейросетью и может содержать ошибки

Сложный запрос PostgreSQL: практический пример

Сценарий: аналитика продаж e-commerce

Летим с реальным примером. У нас есть система интернет-магазина с таблицами:

users (id, email, name, created_at, is_premium)
orders (id, user_id, order_date, total_amount, status)
order_items (id, order_id, product_id, quantity, unit_price)
products (id, name, category, price, supplier_id)
reviews (id, product_id, user_id, rating, created_at)

Задача: найти топ-5 категорий продуктов по прибыли за последние 3 месяца, но только для тех категорий, где более 10 заказов, и рассчитать среднюю оценку каждой категории.

Решение: сложный запрос с CTE, Window Functions, Aggregation

-- Шаг 1: CTE для фильтрации по периоду и статусу
WITH recent_orders AS (
    SELECT 
        oi.product_id,
        oi.quantity,
        oi.unit_price,
        (oi.quantity * oi.unit_price) as item_total,
        o.order_date,
        o.user_id
    FROM order_items oi
    INNER JOIN orders o ON oi.order_id = o.id
    WHERE o.order_date >= NOW() - INTERVAL '3 months'
        AND o.status IN ('completed', 'delivered')
),

-- Шаг 2: Агрегация по продуктам
product_stats AS (
    SELECT 
        p.id,
        p.name,
        p.category,
        COUNT(DISTINCT ro.order_id) as order_count,
        SUM(ro.item_total) as total_revenue,
        SUM(ro.quantity) as total_quantity_sold,
        AVG(ro.unit_price) as avg_price
    FROM recent_orders ro
    INNER JOIN products p ON ro.product_id = p.id
    GROUP BY p.id, p.name, p.category
),

-- Шаг 3: Добавляем рейтинги продуктов
product_with_ratings AS (
    SELECT 
        ps.*,
        COALESCE(ROUND(AVG(r.rating)::numeric, 2), 0) as avg_rating,
        COUNT(DISTINCT r.id) as review_count
    FROM product_stats ps
    LEFT JOIN reviews r ON ps.id = r.product_id
    GROUP BY ps.id, ps.name, ps.category, ps.order_count, 
             ps.total_revenue, ps.total_quantity_sold, ps.avg_price
),

-- Шаг 4: Агрегация по категориям
category_stats AS (
    SELECT 
        category,
        COUNT(DISTINCT id) as product_count,
        SUM(order_count) as total_orders,
        SUM(total_revenue) as category_revenue,
        AVG(avg_rating) as avg_category_rating,
        SUM(total_quantity_sold) as total_items_sold,
        ROW_NUMBER() OVER (ORDER BY SUM(total_revenue) DESC) as revenue_rank
    FROM product_with_ratings
    GROUP BY category
    HAVING COUNT(DISTINCT id) > 0  -- минимум 1 продукт
        AND SUM(order_count) > 10  -- минимум 10 заказов
)

-- Шаг 5: Финальный результат
SELECT 
    revenue_rank,
    category,
    product_count,
    total_orders,
    ROUND(category_revenue::numeric, 2) as revenue,
    ROUND(category_revenue::numeric / total_orders, 2) as avg_order_value,
    ROUND(avg_category_rating::numeric, 2) as avg_rating,
    total_items_sold,
    ROUND((category_revenue::numeric / 
        SUM(category_revenue) OVER() * 100)::numeric, 2) as percent_of_total_revenue
FROM category_stats
WHERE revenue_rank <= 5
ORDER BY revenue_rank;

Разбор запроса по частям

Часть 1: recent_orders (CTE для свежих данных)

WITH recent_orders AS (
    SELECT 
        oi.product_id,
        oi.quantity,
        oi.unit_price,
        (oi.quantity * oi.unit_price) as item_total,
        o.order_date,
        o.user_id
    FROM order_items oi
    INNER JOIN orders o ON oi.order_id = o.id
    WHERE o.order_date >= NOW() - INTERVAL '3 months'
        AND o.status IN ('completed', 'delivered')
)

Что здесь:

  • CTE (Common Table Expression) для переиспользования логики
  • INNER JOIN связывает товары с заказами
  • WHERE фильтрует по дате (последние 3 месяца)
  • AND фильтрует по статусу (только завершённые заказы)
  • Рассчитываем item_total на лету

Часть 2: product_stats (агрегация по продуктам)

product_stats AS (
    SELECT 
        p.id,
        p.name,
        p.category,
        COUNT(DISTINCT ro.order_id) as order_count,
        SUM(ro.item_total) as total_revenue,
        SUM(ro.quantity) as total_quantity_sold,
        AVG(ro.unit_price) as avg_price
    FROM recent_orders ro
    INNER JOIN products p ON ro.product_id = p.id
    GROUP BY p.id, p.name, p.category
)

Что здесь:

  • COUNT(DISTINCT ...) считает уникальные заказы (важно, чтобы не дублировать)
  • SUM рассчитывает общий доход
  • GROUP BY группирует по продуктам

Часть 3: product_with_ratings (LEFT JOIN для рейтингов)

product_with_ratings AS (
    SELECT 
        ps.*,
        COALESCE(ROUND(AVG(r.rating)::numeric, 2), 0) as avg_rating,
        COUNT(DISTINCT r.id) as review_count
    FROM product_stats ps
    LEFT JOIN reviews r ON ps.id = r.product_id
    GROUP BY ps.id, ps.name, ps.category, ps.order_count, 
             ps.total_revenue, ps.total_quantity_sold, ps.avg_price
)

Ключевые моменты:

  • LEFT JOIN (не INNER) чтобы продукты без отзывов остались
  • COALESCE для обработки NULL значений (0 если нет отзывов)
  • ::numeric для точного округления
  • GROUP BY требует всех неагрегирующихся колонок

Часть 4: category_stats (ROW_NUMBER для ранжирования)

category_stats AS (
    SELECT 
        category,
        COUNT(DISTINCT id) as product_count,
        SUM(order_count) as total_orders,
        SUM(total_revenue) as category_revenue,
        AVG(avg_rating) as avg_category_rating,
        SUM(total_quantity_sold) as total_items_sold,
        ROW_NUMBER() OVER (ORDER BY SUM(total_revenue) DESC) as revenue_rank
    FROM product_with_ratings
    GROUP BY category
    HAVING COUNT(DISTINCT id) > 0  -- минимум 1 продукт
        AND SUM(order_count) > 10  -- минимум 10 заказов
)

Ключевые моменты:

  • ROW_NUMBER() это Window Function для ранжирования
  • OVER (ORDER BY ...) определяет порядок ранжирования
  • HAVING (не WHERE!) фильтрует агрегирующиеся данные
  • WHERE работает с исходными данными, HAVING с результатами GROUP BY

Часть 5: финальный SELECT с аналитикой

SELECT 
    revenue_rank,
    category,
    product_count,
    total_orders,
    ROUND(category_revenue::numeric, 2) as revenue,
    ROUND(category_revenue::numeric / total_orders, 2) as avg_order_value,
    ROUND(avg_category_rating::numeric, 2) as avg_rating,
    total_items_sold,
    ROUND((category_revenue::numeric / 
        SUM(category_revenue) OVER() * 100)::numeric, 2) as percent_of_total_revenue
FROM category_stats
WHERE revenue_rank <= 5
ORDER BY revenue_rank;

Что здесь:

  • avg_order_value = общий доход / количество заказов
  • percent_of_total_revenue использует SUM() OVER() (window function)
  • SUM(category_revenue) OVER() — сумма всех категорий без GROUP BY
  • WHERE на результат HAVING (фильтруем только топ-5)

Результат запроса

 revenue_rank | category        | product_count | total_orders | revenue | avg_order_value | avg_rating | percent_of_total
-----------+-----------------+---------------+--------------+---------+-----------------+------------+------------------
          1 | Electronics     |            25 |          145 | 450000  |     3103.45     |       4.50 |       35.12
          2 | Home & Garden   |            18 |           98 | 290000  |     2959.18     |       4.25 |       22.63
          3 | Fashion         |            32 |          125 | 275000  |     2200.00     |       4.10 |       21.45
          4 | Books           |            15 |           65 | 130000  |     2000.00     |       4.75 |       10.14
          5 | Sports          |            20 |           55 | 110000  |     2000.00     |       4.00 |        8.58

Оптимизация запроса

Добавьте индексы для ускорения:

-- Индекс для фильтрации по дате
CREATE INDEX idx_orders_date_status 
    ON orders(order_date, status);

-- Индекс для связи товаров с категориями
CREATE INDEX idx_products_category 
    ON products(category);

-- Индекс для рейтингов
CREATE INDEX idx_reviews_product_id 
    ON reviews(product_id);

-- Составной индекс для order_items
CREATE INDEX idx_order_items_order_product 
    ON order_items(order_id, product_id);

Объяснение сложных концепций

CTE (WITH clauses):

  • Улучшает читаемость
  • Позволяет переиспользовать результаты
  • PostgreSQL оптимизирует CTE лучше в новых версиях

Window Functions (OVER):

  • ROW_NUMBER() — ранжирование без пропусков
  • RANK() — ранжирование с пропусками при равных значениях
  • SUM() OVER() — получить сумму всех строк в окне

Type Casting (::numeric):

  • ::numeric для точных денежных вычислений
  • ::integer для целых чисел
  • Избегает потери точности при делении

Различия WHERE и HAVING:

  • WHERE фильтрует ДО группировки
  • HAVING фильтрует ПОСЛЕ группировки
  • Используйте HAVING для фильтрации агрегирующихся функций

Алтернативный синтаксис: Subqueries

Если CTE кажется сложным, можно использовать подзапросы:

SELECT * FROM (
    SELECT * FROM (
        SELECT * FROM order_items
        WHERE order_id IN (SELECT id FROM orders WHERE status = 'completed')
    ) as filtered_items
    -- остальная логика
) as result
WHERE ...;

Но CTE читается лучше.

Советы для системного аналитика

  1. Всегда проверяйте производительность — используйте EXPLAIN ANALYZE
  2. Начните просто — напишите базовый запрос, потом усложняйте
  3. Используйте CTE для логики — намного понятнее чем вложенные подзапросы
  4. Window Functions vs GROUP BY — когда нужны детали + агрегаты, используйте window functions
  5. Документируйте сложные запросы — комментарии помогут вам и команде
  6. Тестируйте на большом объёме данных — запрос на 1000 строк может упасть на 1 млн