SQL CTE і підзапити: багатокроковий аналіз

Data Analyst Professional · Модуль 4 · Урок 15 із 51

CTE, підзапити й багатокроковий аналіз

Складний аналіз краще будувати як перевірюваний pipeline: scope → нормалізація → агрегація → enrich → final. WITH надає крокам імена, а підзапити вирішують локальні задачі — за умови, що grain кожного етапу явний.

90–120 хвQuery pipelineМінітест: 3 питання

CTE як іменований етап

WITH completed_orders AS (
SELECT order_id, customer_id, order_ts, net_amount
FROM analytics.orders
WHERE status = 'completed'
),
customer_revenue AS (
SELECT customer_id, SUM(net_amount) AS revenue
FROM completed_orders
GROUP BY customer_id
)
SELECT customer_id, revenue
FROM customer_revenue
WHERE revenue >= 10000
ORDER BY revenue DESC, customer_id;

Назви мають описувати сутність і grain: customer_revenue краще за step2. CTE покращує структуру, але не гарантує швидкість; execution plan і версія PostgreSQL визначають оптимізацію.

Типи підзапитів

Форма	Очікуваний результат	Типове застосування	Ризик
scalar subquery	одне значення	control threshold	помилка, якщо повертає більше 1 row
EXISTS	TRUE/FALSE	чи існує пов’язаний факт	не плутати з counting all matches
IN (subquery)	membership	набір ключів	NULL semantics, особливо NOT IN
derived table	табличний результат	pre-aggregation перед JOIN	неявний grain і aliases

EXISTS для перевірки наявності

SELECT c.customer_id
FROM analytics.customers AS c
WHERE EXISTS (
SELECT 1
FROM analytics.orders AS o
WHERE o.customer_id = c.customer_id
AND o.status = 'completed'
);

EXISTS не множить customer rows: він перевіряє, чи є хоча б один match. Для anti-join часто безпечніше NOT EXISTS, ніж NOT IN із nullable subquery result.

Багатокроковий analysis contract

scope

Період, status, population, timezone та privacy limits.

dedup/normalize

Явне правило ключа, version row і invalid states.

aggregate

Задокументований grouping grain і metric formulas.

enrich

JOIN лише після uniqueness/cardinality checks.

final

Тільки потрібні fields, deterministic order і limitations.

checks

Row/key counts, unmatched, control totals і boundary cases на кожному critical stage.

Не перетворюйте CTE на чорну скриньку

Довгий WITH із десятьма етапами може бути нечитабельним. Кожен CTE повинен мати одну роль, зрозумілий grain і мінімальні поля. Для повторюваної бізнес-логіки потрібен governed model/view або transformation layer, а не копії query в різних звітах.

Performance — після коректності

Не прибирайте CTE чи не дублюйте logic лише через припущення про швидкість. Використовуйте EXPLAIN у безпечному середовищі, вимірюйте фактичний plan і зберігайте semantic checks до та після оптимізації.

Практика: pipeline review

Розбийте задачу на 3–5 іменованих stages.
Для кожного stage запишіть grain, key, row count і purpose.
Використайте EXISTS для boolean membership і pre-aggregate до risky JOIN.
Збережіть checks поруч із final query.
Порівняйте фінальний total із незалежним control query.

Офіційні довідки

Модуль 4 завершено

Ви вмієте контролювати cardinality JOIN, агрегувати на явний grain, оформляти business rules через CASE та будувати багатокроковий SQL із checks.