DataLearn SQL-101: Подзапросы vs CTE — когда что использовать

Условие

Найдите клиентов, чей средний чек выше среднего по компании, у которых за последние 30 дней был хотя бы один заказ, и покажите для каждого клиента его средний чек и общую сумму за весь период.

Решите задачу тремя способами: (а) только через подзапросы; (б) через CTE; (в) через оконные функции. Сравните читаемость и производительность.

Решение

Подход

В задаче два уровня агрегации:

• средний чек на клиента;
• средний чек по компании.

Вариант (а) — подзапросы

SELECT u.user_id,
       AVG(o.amount)  AS avg_check_user,
       SUM(o.amount)  AS total_user
FROM orders o
JOIN users u ON u.user_id = o.user_id
WHERE o.status = 'paid'
  AND EXISTS (SELECT 1 FROM orders o2
              WHERE o2.user_id = u.user_id
                AND o2.dt >= CURRENT_DATE - INTERVAL '30 day')
GROUP BY u.user_id
HAVING AVG(o.amount) > (SELECT AVG(amount)
                        FROM orders
                        WHERE status = 'paid');

Вариант (б) — CTE

WITH paid AS (
    SELECT * FROM orders WHERE status = 'paid'
),
overall AS (
    SELECT AVG(amount) AS avg_check_total FROM paid
),
active_30d AS (
    SELECT DISTINCT user_id FROM paid
    WHERE dt >= CURRENT_DATE - INTERVAL '30 day'
),
per_user AS (
    SELECT user_id,
           AVG(amount) AS avg_check_user,
           SUM(amount) AS total_user
    FROM paid
    GROUP BY user_id
)
SELECT pu.*
FROM per_user pu
JOIN active_30d a ON a.user_id = pu.user_id
CROSS JOIN overall ov
WHERE pu.avg_check_user > ov.avg_check_total;

Вариант (в) — оконные функции

WITH paid AS (
    SELECT * FROM orders WHERE status = 'paid'
),
labeled AS (
    SELECT
        user_id,
        AVG(amount)  OVER (PARTITION BY user_id) AS avg_check_user,
        SUM(amount)  OVER (PARTITION BY user_id) AS total_user,
        AVG(amount)  OVER ()                     AS avg_check_total,
        MAX(CASE WHEN dt >= CURRENT_DATE - INTERVAL '30 day' THEN 1 ELSE 0 END)
              OVER (PARTITION BY user_id)        AS active_30d_flag,
        ROW_NUMBER() OVER (PARTITION BY user_id) AS rn
    FROM paid
)
SELECT user_id, avg_check_user, total_user
FROM labeled
WHERE rn = 1
  AND active_30d_flag = 1
  AND avg_check_user > avg_check_total;

Сравнение

Когда что использовать

• Подзапросы — один-два уровня, короткие фильтры (WHERE col > (SELECT MAX...)), EXISTS / NOT EXISTS.
• CTE — пайплайны из 3+ шагов, нужна отладка и читаемость.
• Window — когда нужно сохранить детальные строки + добавить агрегаты (running total, ранги).
• Рекурсивный CTE (WITH RECURSIVE) — иерархии, графы.

Подводные камни

1. WITH name AS NOT MATERIALIZED — в Postgres 12+ CTE inline'ится; раньше материализовался (был optimization fence). Иногда нужно явно MATERIALIZED для повторного использования.
2. Коррелированные подзапросы в SELECT (SELECT (SELECT MAX(...) FROM ... WHERE ...) FROM ...) — O(n × m), часто медленнее, чем JOIN/window.
3. EXISTS vs JOIN DISTINCT: EXISTS обычно быстрее (semi-join без расширения).
4. Window vs GROUP BY: window сохраняет все строки → больше IO; GROUP BY свёртывает.
5. CTE в MySQL — только с 8.0; в более старых нужно либо derived tables, либо переменные.
6. Рекурсивный CTE легко зацикливается без UNION (есть UNION ALL — тогда нужен LIMIT или условие выхода).
7. WHERE в outer + JOIN с CTE — фильтр может не пробросить в CTE → лишнее сканирование. Иногда быстрее повторить фильтр в CTE.

Эталонный ответ

CTE-вариант — обычно лучший баланс читаемости и производительности. Шаги:

1. paid — все оплаченные заказы (отфильтровали);
2. overall — средний чек по компании;
3. active_30d — кто активен в последние 30 дней;
4. per_user — агрегат на клиента;
5. JOIN + сравнение со средним по компании.

Подзапросы — для коротких EXISTS/MAX-фильтров. Window — когда нужны детальные строки с агрегатами рядом.