Что такое CTE (Common Table Expression) в SQL?

Что такое CTE (Common Table Expression)

CTE (Common Table Expression) — это временная именованная таблица, которая существует только в рамках одного запроса. Обычно пишется с помощью предложения WITH и позволяет разбивать сложные запросы на более читаемые и управляемые части. CTE полезны для рекурсивных запросов, повторного использования подзапросов и улучшения читаемости кода.

Синтаксис

WITH cte_name AS (
  SELECT column1, column2
  FROM table1
  WHERE condition
)
SELECT *
FROM cte_name
WHERE additional_condition;

Пример 1: Простой CTE

-- Без CTE (сложнее читать)
SELECT 
  department,
  COUNT(*) as employee_count,
  AVG(salary) as avg_salary
FROM (
  SELECT department, salary
  FROM employees
  WHERE hire_date > '2020-01-01'
) AS filtered_employees
GROUP BY department;

-- С CTE (понятнее)
WITH recent_employees AS (
  SELECT department, salary
  FROM employees
  WHERE hire_date > '2020-01-01'
)
SELECT 
  department,
  COUNT(*) as employee_count,
  AVG(salary) as avg_salary
FROM recent_employees
GROUP BY department;

Пример 2: Множественные CTE

WITH sales_2023 AS (
  SELECT 
    product_id,
    SUM(amount) as total_sales
  FROM sales
  WHERE YEAR(date) = 2023
  GROUP BY product_id
),
sales_2024 AS (
  SELECT 
    product_id,
    SUM(amount) as total_sales
  FROM sales
  WHERE YEAR(date) = 2024
  GROUP BY product_id
)
SELECT 
  s23.product_id,
  s23.total_sales as sales_2023,
  s24.total_sales as sales_2024,
  ROUND((s24.total_sales - s23.total_sales) / s23.total_sales * 100, 2) as growth_percent
FROM sales_2023 s23
JOIN sales_2024 s24 ON s23.product_id = s24.product_id
ORDER BY growth_percent DESC;

Пример 3: Рекурсивный CTE

-- Иерархия сотрудников (подчинённые и начальники)
WITH RECURSIVE employee_hierarchy AS (
  -- Базовый случай: сотрудники без начальника (CEO)
  SELECT 
    id,
    name,
    manager_id,
    1 as level
  FROM employees
  WHERE manager_id IS NULL
  
  UNION ALL
  
  -- Рекурсивный случай: подчинённые каждого сотрудника
  SELECT 
    e.id,
    e.name,
    e.manager_id,
    eh.level + 1
  FROM employees e
  INNER JOIN employee_hierarchy eh ON e.manager_id = eh.id
  WHERE eh.level < 10  -- ограничение глубины рекурсии
)
SELECT 
  CONCAT(REPEAT('  ', level - 1), name) as employee_tree,
  level
FROM employee_hierarchy
ORDER BY id;

Пример 4: Запрос с CTE и оконными функциями

WITH ranked_sales AS (
  SELECT 
    employee_id,
    name,
    month,
    sales_amount,
    ROW_NUMBER() OVER (PARTITION BY employee_id ORDER BY month) as month_rank,
    LAG(sales_amount) OVER (PARTITION BY employee_id ORDER BY month) as prev_month_sales
  FROM employee_sales
),
sales_growth AS (
  SELECT 
    employee_id,
    name,
    month,
    sales_amount,
    prev_month_sales,
    CASE 
      WHEN prev_month_sales IS NULL THEN 0
      ELSE ROUND((sales_amount - prev_month_sales) / prev_month_sales * 100, 2)
    END as growth_percent
  FROM ranked_sales
)
SELECT 
  employee_id,
  name,
  month,
  sales_amount,
  growth_percent
FROM sales_growth
WHERE growth_percent > 10
ORDER BY month, growth_percent DESC;

CTE vs Подзапросы

Подзапрос (Subquery)

SELECT department, AVG(salary)
FROM (
  SELECT department, salary
  FROM employees
  WHERE hire_date > '2020-01-01'
) AS subq
GROUP BY department;

Минусы:

• Если подзапрос используется несколько раз, код повторяется
• Сложнее читать при вложенных подзапросах
• Не может быть рекурсивным

CTE

WITH recent_emp AS (
  SELECT department, salary
  FROM employees
  WHERE hire_date > '2020-01-01'
)
SELECT department, AVG(salary)
FROM recent_emp
GROUP BY department;

Преимущества:

• Более читаемый код
• Можно повторно использовать в одном запросе
• Поддерживает рекурсию
• Легче отлаживать и оптимизировать

Практический пример для Data Science

WITH user_activity AS (
  SELECT 
    user_id,
    DATE(event_date) as event_day,
    COUNT(*) as events_count,
    SUM(spent) as daily_spent
  FROM events
  GROUP BY user_id, DATE(event_date)
),
user_stats AS (
  SELECT 
    user_id,
    COUNT(DISTINCT event_day) as active_days,
    AVG(events_count) as avg_events_per_day,
    AVG(daily_spent) as avg_daily_spent,
    SUM(daily_spent) as total_spent
  FROM user_activity
  GROUP BY user_id
)
SELECT 
  user_id,
  active_days,
  ROUND(avg_events_per_day, 2) as avg_events,
  ROUND(avg_daily_spent, 2) as avg_spent,
  ROUND(total_spent, 2) as total_spent,
  CASE 
    WHEN total_spent > 1000 THEN 'VIP'
    WHEN total_spent > 500 THEN 'Premium'
    ELSE 'Regular'
  END as user_segment
FROM user_stats
WHERE active_days >= 7
ORDER BY total_spent DESC;

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

• Упрощение читаемости — разбиение сложного запроса
• Повторное использование — один CTE в нескольких местах
• Иерархические данные — рекурсивные запросы
• Поэтапные расчёты — вычисления с промежуточными шагами
• Подготовка данных — фильтрация и трансформация перед основным запросом

CTE — один из самых полезных инструментов для написания понятного и maintainable SQL кода в аналитике данных.