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

Приведи пример CTE запроса

1.7 Middle🔥 181 комментариев
#Базы данных (SQL)

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

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

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

CTE (Common Table Expression) — Примеры и Применение

CTE — это временная именованная таблица, которая существует только в рамках одного запроса. Пишется с помощью оператора WITH.

Базовый пример: простой CTE

-- CTE содержит список пользователей с большим балансом
WITH high_balance_users AS (
    SELECT id, name, balance
    FROM users
    WHERE balance > 1000
)
SELECT * FROM high_balance_users;

Это эквивалентно:

SELECT id, name, balance
FROM users
WHERE balance > 1000;

Но CTE улучшает читаемость при сложных запросах.

Пример 1: Используем CTE несколько раз

-- CTE используется дважды
WITH rich_users AS (
    SELECT id, name, balance
    FROM users
    WHERE balance > 5000
),
rich_users_orders AS (
    SELECT o.id, o.user_id, o.amount, ru.name
    FROM orders o
    JOIN rich_users ru ON o.user_id = ru.id
)
SELECT 
    name,
    COUNT(*) as order_count,
    SUM(amount) as total_spent
FROM rich_users_orders
GROUP BY name
ORDER BY total_spent DESC;

Что происходит:

  1. rich_users — находит пользователей с балансом > 5000
  2. rich_users_orders — соединяет заказы этих пользователей
  3. Считаем статистику по каждому пользователю

Пример 2: Рекурсивный CTE (иерархия)

-- Структура: менеджеры и их подчинённые
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    manager_id INT REFERENCES employees(id)
);

INSERT INTO employees VALUES
(1, 'CEO Alice', NULL),
(2, 'Manager Bob', 1),
(3, 'Manager Carol', 1),
(4, 'Developer Dave', 2),
(5, 'Developer Eve', 2),
(6, 'Designer Frank', 3);

-- Рекурсивный CTE: показать всю иерархию
WITH RECURSIVE employee_hierarchy AS (
    -- Базовый случай: начиная с CEO
    SELECT id, name, manager_id, 0 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
)
SELECT 
    REPEAT('  ', level) || name as employee_tree,
    level
FROM employee_hierarchy
ORDER BY level, name;

-- Вывод:
-- CEO Alice
--   Manager Bob
--     Developer Dave
--     Developer Eve
--   Manager Carol
--     Designer Frank

Пример 3: CTE для ранжирования (ROW_NUMBER)

-- Найти топ-3 товаров по продажам в каждой категории
WITH product_sales AS (
    SELECT 
        p.name,
        c.category,
        SUM(o.quantity) as total_qty,
        ROW_NUMBER() OVER (PARTITION BY c.category ORDER BY SUM(o.quantity) DESC) as rank
    FROM products p
    JOIN categories c ON p.category_id = c.id
    LEFT JOIN order_items o ON p.id = o.product_id
    GROUP BY p.id, p.name, c.category
)
SELECT name, category, total_qty
FROM product_sales
WHERE rank <= 3
ORDER BY category, rank;

-- Вывод:
-- Electronics: iPhone (1000), Samsung TV (800), iPad (600)
-- Clothing: T-Shirt (500), Jeans (400), Shirt (350)

Пример 4: CTE для удаления дубликатов

-- Таблица с потенциальными дубликатами
CREATE TABLE user_emails (
    id INT,
    email VARCHAR(100)
);

INSERT INTO user_emails VALUES
(1, 'alice@example.com'),
(2, 'alice@example.com'),
(3, 'bob@example.com'),
(4, 'bob@example.com'),
(5, 'carol@example.com');

-- Найти первое вхождение каждого email
WITH ranked_emails AS (
    SELECT 
        id,
        email,
        ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) as rn
    FROM user_emails
)
SELECT id, email
FROM ranked_emails
WHERE rn = 1;

-- Вывод:
-- id=1, alice@example.com
-- id=3, bob@example.com  
-- id=5, carol@example.com

Пример 5: CTE с вычисляемыми значениями

-- Анализ продаж по месяцам
WITH monthly_sales AS (
    SELECT 
        DATE_TRUNC('month', created_at)::DATE as month,
        SUM(amount) as total_sales,
        COUNT(*) as order_count
    FROM orders
    WHERE created_at >= NOW() - INTERVAL '1 year'
    GROUP BY DATE_TRUNC('month', created_at)
),
sales_with_stats AS (
    SELECT 
        month,
        total_sales,
        order_count,
        AVG(total_sales) OVER () as avg_monthly_sales,
        LAG(total_sales) OVER (ORDER BY month) as prev_month_sales
    FROM monthly_sales
)
SELECT 
    month,
    total_sales,
    order_count,
    ROUND((total_sales / avg_monthly_sales - 1) * 100, 2) as vs_avg_percent,
    ROUND((total_sales / prev_month_sales - 1) * 100, 2) as vs_prev_month_percent
FROM sales_with_stats
ORDER BY month DESC;

Пример 6: Практический CTE на Python с SQLAlchemy

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

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

def get_top_customers():
    """CTE запрос через SQLAlchemy"""
    query = text("""
    WITH customer_totals AS (
        SELECT 
            c.id,
            c.name,
            COUNT(o.id) as order_count,
            SUM(o.amount) as total_spent
        FROM customers c
        LEFT JOIN orders o ON c.id = o.customer_id
        GROUP BY c.id, c.name
    )
    SELECT 
        id,
        name,
        order_count,
        total_spent,
        CASE 
            WHEN total_spent > 10000 THEN 'VIP'
            WHEN total_spent > 5000 THEN 'Premium'
            ELSE 'Regular'
        END as customer_tier
    FROM customer_totals
    WHERE order_count > 0
    ORDER BY total_spent DESC
    LIMIT 10
    """)
    
    with Session(engine) as session:
        results = session.execute(query).fetchall()
        return results

# Использование
for row in get_top_customers():
    print(f"{row.name}: {row.total_spent} ({row.customer_tier})")

Пример 7: Множественные CTE (цепочка)

-- Сложный анализ с несколькими CTE
WITH user_purchases AS (
    SELECT user_id, COUNT(*) as purchase_count, SUM(amount) as total_amount
    FROM purchases
    GROUP BY user_id
),
user_scores AS (
    SELECT 
        user_id,
        purchase_count,
        total_amount,
        purchase_count * 10 + (total_amount / 100) as loyalty_score
    FROM user_purchases
    WHERE total_amount > 0
),
top_loyal_users AS (
    SELECT 
        u.id,
        u.name,
        us.loyalty_score,
        RANK() OVER (ORDER BY us.loyalty_score DESC) as loyalty_rank
    FROM users u
    JOIN user_scores us ON u.id = us.user_id
)
SELECT *
FROM top_loyal_users
WHERE loyalty_rank <= 20
ORDER BY loyalty_rank;

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

  1. Читаемость — большой запрос разбивается на логические части

    WITH step1 AS (...),
     step2 AS (...),
     step3 AS (...)
SELECT * FROM step3;  -- Вся логика видна сверху

  2. Повторное использование — одну CTE можно использовать несколько раз

    WITH active_users AS (...)
SELECT COUNT(*) FROM active_users  -- использование 1
UNION ALL
SELECT SUM(balance) FROM active_users;  -- использование 2

  3. Рекурсивные запросы — возможны только через CTE

    WITH RECURSIVE tree AS (...) SELECT * FROM tree;

  4. Улучшенная отладка — можно проверить каждый CTE отдельно

    -- Проверяем промежуточный результат
WITH intermediate AS (...)
SELECT * FROM intermediate;  -- Видим что там

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

  • Сложные многошаговые запросы — разбей на CTE
  • Рекурсивные данные (иерархии, графы) — нужен рекурсивный CTE
  • Повторяющиеся подзапросы — одна CTE вместо дублирования
  • Нужна читаемость — CTE лучше вложенных подзапросов
  • Window functions — часто комбинируют с CTE для ранжирования/статистики