Как использовать несколько последовательных CTE, чтобы сначала очистить данные, а затем посчитать на них метрику?

Короткий ответ

Нужно объявить несколько CTE через запятую в одном блоке WITH, где каждый следующий CTE может ссылаться на предыдущие, формируя цепочку шагов «очистка → дедупликация → агрегация → финальный отбор».

Как это работает подробнее

в блоке WITH cte1 AS (...), cte2 AS (SELECT ... FROM cte1 ...), cte3 AS (...) каждый следующий CTE может использовать результат предыдущего по имени, как обычную таблицу; основной SELECT в конце обычно обращается к последнему CTE.

Пример кода

question-43.sqlКопировать

WITH cleaned_orders AS (
  SELECT * FROM orders WHERE amount > 0 AND customer_id IS NOT NULL
),
deduped_orders AS (
  SELECT DISTINCT * FROM cleaned_orders
),
monthly_revenue AS (
  SELECT DATE_TRUNC('month', order_date) AS month, SUM(amount) AS revenue
  FROM deduped_orders
  GROUP BY 1
)
SELECT * FROM monthly_revenue ORDER BY month;

Что использовать на практике

• такой подход делает сложную логику читаемой и тестируемой по шагам — можно временно заменить финальный SELECT на SELECT * FROM cte2 и проверить промежуточный результат, не переписывая весь запрос. Это особенно полезно, когда данные перед агрегацией нужно почистить (убрать дубликаты, отфильтровать тестовые записи, привести типы). Проверка: временно выполнить SELECT COUNT(*) FROM cleaned_orders и SELECT COUNT(*) FROM deduped_orders, чтобы увидеть, сколько строк отсеялось на каждом шаге.

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

• нагромождают слишком много логики в одном CTE вместо разделения на понятные шаги, из-за чего сложно отследить, на каком этапе появилась ошибка; забывают, что в большинстве диалектов (кроме рекурсивных CTE) CTE не может ссылаться на CTE, объявленный после него — порядок объявления важен.