Промпт для оптимизация SQL

Мне нужен ваш SQL-запрос, чтобы я мог его оптимизировать.

Как только вы предоставите его, я проанализирую запрос, предложу оптимальную версию для PostgreSQL, объясню свой выбор и приведу альтернативные варианты с их недостатками.

Жду ваш запрос!

Запрос пользователя

SELECT
    c.category_name,
    sub.total_sales,
    (
        SELECT u.username
        FROM users u
        WHERE u.user_id = (
            SELECT o.user_id
            FROM orders o
            JOIN order_items oi ON o.order_id = oi.order_id
            JOIN products p ON oi.product_id = p.product_id
            WHERE p.category_id = c.category_id
            GROUP BY o.user_id
            ORDER BY COUNT(DISTINCT o.order_id) DESC
            LIMIT 1
        )
    ) AS most_active_user_in_category
FROM
    (
        SELECT
            p.category_id,
            SUM(o.total_amount) AS total_sales
        FROM
            orders o
        JOIN
            order_items oi ON o.order_id = oi.order_id
        JOIN
            products p ON oi.product_id = p.product_id
        WHERE
            o.order_date >= DATE('now', '-1 year')
        GROUP BY
            p.category_id
        ORDER BY
            total_sales DESC
        LIMIT 3
    ) AS sub
JOIN
    categories c ON sub.category_id = c.category_id;

Я проанализировал ваш запрос. Он выглядит громоздко из-за вложенных подзапросов, которые могут привести к низкой производительности, особенно на больших объемах данных.

Оптимальный SQL-запрос для PostgreSQL

Вот оптимальная версия запроса, которая использует оконные функции и CTE (Common Table Expressions). Этот подход позволяет избежать множественных сканирований таблицы и делает запрос более читаемым и эффективным.

WITH CategorySales AS (
    -- CTE для расчета общих продаж по категориям и фильтрации топ-3
    SELECT
        p.category_id,
        c.category_name,
        SUM(o.total_amount) AS total_sales
    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 c ON p.category_id = c.category_id
    WHERE o.order_date >= NOW() - INTERVAL '1 year'
    GROUP BY p.category_id, c.category_name
    ORDER BY total_sales DESC
    LIMIT 3
),
UserActivity AS (
    -- CTE для определения самого активного пользователя в каждой категории
    SELECT
        p.category_id,
        o.user_id,
        COUNT(DISTINCT o.order_id) AS order_count,
        ROW_NUMBER() OVER (PARTITION BY p.category_id ORDER BY COUNT(DISTINCT o.order_id) DESC) AS rn
    FROM orders o
    JOIN order_items oi ON o.order_id = oi.order_id
    JOIN products p ON oi.product_id = p.product_id
    WHERE p.category_id IN (SELECT category_id FROM CategorySales)
    GROUP BY p.category_id, o.user_id
)
SELECT
    cs.category_name,
    cs.total_sales,
    u.username AS most_active_user_in_category
FROM CategorySales cs
LEFT JOIN UserActivity ua ON cs.category_id = ua.category_id AND ua.rn = 1
LEFT JOIN users u ON ua.user_id = u.user_id;

Обоснование выбора

• Использование CTE (Common Table Expressions): Запрос разбит на логические блоки (CategorySales и UserActivity), что значительно повышает его читаемость и поддерживаемость. CTE выполняются один раз, и их результат может быть использован в последующих частях запроса, что исключает дублирование кода и повторные вычисления.
• Оконные функции (ROW_NUMBER()): Это ключевой элемент оптимизации. Вместо того чтобы выполнять отдельный подзапрос для каждой категории, мы используем ROW_NUMBER() с PARTITION BY p.category_id. Это позволяет определить самого активного пользователя для каждой категории за один проход по данным, что гораздо эффективнее, чем вложенный подзапрос, который будет выполняться для каждой строки внешнего запроса.
• Уменьшение вложенности: Запрос стал более плоским. Вместо нескольких уровней вложенных подзапросов, он использует JOIN между CTE и таблицами, что позволяет оптимизатору запросов PostgreSQL лучше планировать выполнение.
• Предикативная фильтрация: Фильтрация по WHERE p.category_id IN (SELECT category_id FROM CategorySales) во втором CTE UserActivity ограничивает обработку данных только теми категориями, которые попали в топ-3 по продажам. Это сокращает объем обрабатываемых строк.

Альтернативные варианты и их недостатки

1. Ваш исходный запрос с вложенными подзапросами

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

• Недостатки:
  • Низкая производительность: Для каждой строки, возвращенной основным запросом (в нашем случае, 3 строки), будет выполняться отдельный, независимый подзапрос. Это приводит к избыточным вычислениям и сканированиям таблиц, что сильно замедляет выполнение на больших объемах данных.
  • Сложность чтения: Множественные уровни вложенности затрудняют понимание логики запроса.
  • Проблемы с масштабированием: По мере роста объема данных, производительность будет падать нелинейно.

2. Запрос с использованием DISTINCT ON

Принцип: Вместо оконной функции ROW_NUMBER() можно использовать DISTINCT ON.

SELECT
    cs.category_name,
    cs.total_sales,
    u.username AS most_active_user_in_category
FROM (
    -- Ваш исходный CTE для CategorySales
    SELECT
        p.category_id,
        c.category_name,
        SUM(o.total_amount) AS total_sales
    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 c ON p.category_id = c.category_id
    WHERE o.order_date >= NOW() - INTERVAL '1 year'
    GROUP BY p.category_id, c.category_name
    ORDER BY total_sales DESC
    LIMIT 3
) cs
LEFT JOIN LATERAL (
    -- LATERAL JOIN для поиска самого активного пользователя
    SELECT
        u.username
    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 users u ON o.user_id = u.user_id
    WHERE p.category_id = cs.category_id
    GROUP BY u.username
    ORDER BY COUNT(DISTINCT o.order_id) DESC
    LIMIT 1
) AS active_user ON true;

• Недостатки:
  • Сложность понимания: Конструкция LATERAL JOIN может быть менее интуитивно понятной для начинающих разработчиков.
  • Производительность: Хотя LATERAL JOIN часто оптимизируется лучше, чем простой подзапрос в SELECT, он все равно по сути выполняет вложенный запрос для каждой строки внешнего запроса. В некоторых случаях, особенно на очень больших таблицах, это может быть менее эффективно, чем единичный проход с оконной функцией.

Таким образом, решение с CTE и оконными функциями является наиболее производительным, читаемым и масштабируемым вариантом для PostgreSQL.