PrepBro
← Назад к вопросам

Что такое оператор WITH в БД?

1.3 Junior🔥 141 комментариев
#DevOps и инфраструктура#Django

Комментарии (1)

🐱
claude-haiku-4.5PrepBro AI23 мар. 2026 г.(ред.)

Ответ сгенерирован нейросетью и может содержать ошибки

WITH оператор в SQL (CTE — Common Table Expression)

WITH оператор (он же CTE, Common Table Expression) создаёт временную именованную выборку, которую можно использовать в основном запросе. Это делает SQL запросы более читаемыми и переиспользуемыми, особенно для сложной логики.

Базовый синтаксис

-- Базовый WITH
WITH cte_name AS (
    SELECT id, name FROM users WHERE is_active = true
)
SELECT * FROM cte_name WHERE id > 10;

-- Несколько CTE (через запятую)
WITH active_users AS (
    SELECT id, name FROM users WHERE is_active = true
),
premium_users AS (
    SELECT id, name FROM users WHERE subscription = 'premium'
)
SELECT * FROM active_users
INTERSECT
SELECT * FROM premium_users;

Простой пример: фильтрация через WITH

-- БЕЗ WITH: сложнее читается
SELECT 
    u.id, 
    u.name, 
    COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name
HAVING COUNT(o.id) > 5
ORDER BY order_count DESC;

-- С WITH: более читаемо
WITH user_orders AS (
    SELECT 
        u.id, 
        u.name, 
        COUNT(o.id) as order_count
    FROM users u
    LEFT JOIN orders o ON u.id = o.user_id
    GROUP BY u.id, u.name
)
SELECT * FROM user_orders
WHERE order_count > 5
ORDER BY order_count DESC;

Практический пример: продажи по менеджерам

-- Выборка 1: Продажи по менеджеру
WITH manager_sales AS (
    SELECT 
        m.id as manager_id,
        m.name as manager_name,
        SUM(s.amount) as total_sales,
        COUNT(s.id) as sale_count
    FROM managers m
    LEFT JOIN sales s ON m.id = s.manager_id
    GROUP BY m.id, m.name
)
SELECT * FROM manager_sales
WHERE total_sales > 10000
ORDER BY total_sales DESC;

Рекурсивный WITH (RECURSIVE)

-- Иерархические данные: структура организации
WITH RECURSIVE org_hierarchy AS (
    -- Базовый случай: начальник отдела
    SELECT 
        id, 
        name, 
        parent_id, 
        1 as level
    FROM employees
    WHERE parent_id IS NULL  -- Начальники
    
    UNION ALL
    
    -- Рекурсивный случай: подчинённые
    SELECT 
        e.id, 
        e.name, 
        e.parent_id, 
        oh.level + 1
    FROM employees e
    INNER JOIN org_hierarchy oh ON e.parent_id = oh.id
    WHERE oh.level < 5  -- Ограничение глубины
)
SELECT 
    REPEAT('  ', level - 1) || name as hierarchy,
    level
FROM org_hierarchy
ORDER BY level, id;

-- Результат:
-- hierarchy        | level
-- CEO              | 1
--   CTO            | 2
--     Backend Lead | 3
--       Developer  | 4
--     Frontend Lead| 3

Другой пример: генерация последовательности

-- Генерировать числа от 1 до 100
WITH RECURSIVE numbers AS (
    SELECT 1 as num
    
    UNION ALL
    
    SELECT num + 1
    FROM numbers
    WHERE num < 100
)
SELECT * FROM numbers;

-- Генерировать даты на месяц
WITH RECURSIVE date_series AS (
    SELECT DATE '2024-01-01' as date
    
    UNION ALL
    
    SELECT date + INTERVAL '1 day'
    FROM date_series
    WHERE date < DATE '2024-12-31'
)
SELECT * FROM date_series;

Пример с Python/SQLAlchemy

from sqlalchemy import create_engine, text
from sqlalchemy.orm import Session

engine = create_engine('postgresql://user:pass@localhost/db')

# Запрос с WITH
query = text("""
WITH monthly_revenue AS (
    SELECT 
        DATE_TRUNC('month', order_date) as month,
        SUM(amount) as revenue
    FROM orders
    GROUP BY DATE_TRUNC('month', order_date)
)
SELECT 
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) as prev_revenue,
    ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month)) / LAG(revenue) OVER (ORDER BY month), 2) as growth_percent
FROM monthly_revenue
ORDER BY month;
""")

with Session(engine) as session:
    results = session.execute(query).fetchall()
    for month, revenue, prev_revenue, growth in results:
        print(f"{month}: ${revenue:,.2f} (Growth: {growth}%)")

Window Functions с WITH

-- Ранжирование внутри отделов
WITH employee_salary_rank AS (
    SELECT 
        name,
        department,
        salary,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as rank_in_dept,
        AVG(salary) OVER (PARTITION BY department) as dept_avg_salary
    FROM employees
)
SELECT *
FROM employee_salary_rank
WHERE rank_in_dept <= 3  -- Топ 3 зарплаты в каждом отделе
ORDER BY department, rank_in_dept;

Несколько CTE с зависимостями

WITH 
-- CTE 1: активные пользователи
active_users AS (
    SELECT id, name, email FROM users WHERE is_active = true
),
-- CTE 2: заказы активных пользователей
user_orders AS (
    SELECT 
        au.id as user_id,
        au.name,
        COUNT(o.id) as order_count,
        SUM(o.total) as total_spent
    FROM active_users au
    LEFT JOIN orders o ON au.id = o.user_id
    GROUP BY au.id, au.name
),
-- CTE 3: топ покупатели
top_customers AS (
    SELECT 
        user_id,
        name,
        order_count,
        total_spent,
        RANK() OVER (ORDER BY total_spent DESC) as rank
    FROM user_orders
    WHERE order_count > 0
)
SELECT * FROM top_customers
WHERE rank <= 10;

Производительность WITH

-- ❓ WITH может быть оптимизирован или нет
-- Зависит от БД (материализация)

-- PostgreSQL: часто материализирует CTE
WITH expensive_query AS (
    SELECT * FROM users
    WHERE expensive_computation() = true  -- Может выполниться один раз
)
SELECT * FROM expensive_query WHERE id > 100;
-- При использовании нескольких раз в основном запросе
-- CTE результат кэшируется

-- Но можно контролировать:
WITH expensive_query AS NOT MATERIALIZED (
    -- PostgreSQL 12+: инлайнить вместо материализации
    SELECT * FROM users WHERE id > 100
)
SELECT * FROM expensive_query;

Сравнение: WITH vs SUBQUERY

-- Подзапрос (Subquery)
SELECT *
FROM (
    SELECT id, name, COUNT(*) as orders
    FROM users
    GROUP BY id, name
) AS user_orders
WHERE orders > 5;

-- CTE (WITH) — более читаемо
WITH user_orders AS (
    SELECT id, name, COUNT(*) as orders
    FROM users
    GROUP BY id, name
)
SELECT *
FROM user_orders
WHERE orders > 5;

-- Оба работают одинаково по производительности
-- WITH более читаемо для сложных запросов

Реальный пример: Когорта анализ

WITH 
-- Когда впервые вошёл каждый пользователь
first_login AS (
    SELECT 
        user_id,
        DATE_TRUNC('month', MIN(login_time)) as cohort_month
    FROM logins
    GROUP BY user_id
),
-- Месяц каждого логина
login_by_month AS (
    SELECT 
        l.user_id,
        DATE_TRUNC('month', l.login_time) as login_month,
        fl.cohort_month
    FROM logins l
    JOIN first_login fl ON l.user_id = fl.user_id
),
-- Вычислить месяц от первого логина
cohort_analysis AS (
    SELECT 
        cohort_month,
        (EXTRACT(YEAR FROM login_month) - EXTRACT(YEAR FROM cohort_month)) * 12 +
        (EXTRACT(MONTH FROM login_month) - EXTRACT(MONTH FROM cohort_month)) as months_since_signup,
        COUNT(DISTINCT user_id) as user_count
    FROM login_by_month
    GROUP BY cohort_month, months_since_signup
)
SELECT 
    cohort_month,
    months_since_signup,
    user_count
FROM cohort_analysis
ORDER BY cohort_month, months_since_signup;

Практические советы

-- ✓ ХОРОШО: WITH для читаемости
WITH step1 AS (
    SELECT id, name FROM users WHERE status = 'active'
),
step2 AS (
    SELECT s1.id, s1.name, COUNT(o.id) as orders
    FROM step1 s1
    LEFT JOIN orders o ON s1.id = o.user_id
    GROUP BY s1.id, s1.name
)
SELECT * FROM step2 WHERE orders > 5;

-- ❌ ПЛОХО: слишком много вложенности без WITH
SELECT * FROM (
    SELECT s1.id, s1.name, COUNT(o.id) as orders FROM (
        SELECT id, name FROM users WHERE status = 'active'
    ) s1
    LEFT JOIN orders o ON s1.id = o.user_id
    GROUP BY s1.id, s1.name
) s2
WHERE orders > 5;

-- ✓ ХОРОШО: сгруппировать логику
WITH -- Данные
raw_data AS (...),
-- Трансформация
transformed_data AS (...),
-- Агрегация
aggregated_data AS (...)
SELECT * FROM aggregated_data;

RECURSIVE WITH — правила

-- Обязательная структура:
WITH RECURSIVE cte_name AS (
    -- ANCHOR (базовое условие)
    SELECT ... FROM table
    WHERE condition
    
    UNION ALL
    
    -- RECURSIVE (рекурсивное условие)
    SELECT ... FROM table
    JOIN cte_name ON ...
    WHERE stop_condition
)
SELECT * FROM cte_name;

-- Правила:
-- 1. Якорь (anchor) должен быть первым
-- 2. Должен быть UNION ALL (не UNION)
-- 3. Рекурсивная часть ссылается на CTE
-- 4. ДОЛЖНО быть стоп условие (WHERE), иначе бесконечный цикл

Ключевые моменты

  • WITH создаёт временную выборку (CTE)
  • Читаемость улучшается, особенно в сложных запросах
  • RECURSIVE для иерархических данных и последовательностей
  • Материализация — CTE может быть кэширована или инлайнена
  • Несколько CTE через запятую, второй может использовать первый
  • WITH vs Subquery — оба работают одинаково, WITH читаемее
  • Window Functions отлично работают с WITH
  • UNION ALL в RECURSIVE, не UNION