SQL CTE (Common Table Expression) 高级用法与最佳实践

SQL CTE 高级用法与最佳实践

一、CTE 核心概念回顾

基础语法：

WITH cte_name (column1, column2, ...) AS (
    SELECT column1, column2, ...
    FROM table
    WHERE conditions
)
SELECT * FROM cte_name;

二、高级用法详解

-- 层次结构查询（员工上下级关系）
WITH RECURSIVE EmployeeHierarchy AS (
    -- 锚点查询
    SELECT 
        employee_id,
        employee_name,
        manager_id,
        1 AS level,
        CAST(employee_name AS VARCHAR(1000)) AS hierarchy_path
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- 递归查询
    SELECT 
        e.employee_id,
        e.employee_name,
        e.manager_id,
        eh.level + 1,
        CAST(eh.hierarchy_path || ' -> ' || e.employee_name AS VARCHAR(1000))
    FROM employees e
    INNER JOIN EmployeeHierarchy eh 
        ON e.manager_id = eh.employee_id
    WHERE eh.level < 10  -- 防止无限递归
)
SELECT * FROM EmployeeHierarchy;

-- 生成序列
WITH RECURSIVE NumberSeries AS (
    SELECT 1 AS num
    UNION ALL
    SELECT num + 1
    FROM NumberSeries
    WHERE num < 100
)
SELECT * FROM NumberSeries;

WITH 
-- 第一个CTE：数据清洗
CleanedData AS (
    SELECT 
        user_id,
        TRIM(email) AS email,
        COALESCE(age, 0) AS age,
        created_at::DATE AS signup_date
    FROM raw_users
    WHERE email IS NOT NULL
),

-- 第二个CTE：聚合计算
UserStats AS (
    SELECT 
        signup_date,
        COUNT(*) AS new_users,
        AVG(age) AS avg_age
    FROM CleanedData
    GROUP BY signup_date
),

-- 第三个CTE：窗口计算
RankedData AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (ORDER BY new_users DESC) AS rank
    FROM UserStats
)

-- 主查询
SELECT 
    signup_date,
    new_users,
    avg_age,
    rank
FROM RankedData
WHERE rank <= 10;

-- PostgreSQL示例
WITH updated_sales AS (
    UPDATE sales 
    SET amount = amount * 1.1
    WHERE sale_date >= '2024-01-01'
    RETURNING *
)
SELECT 
    product_id,
    SUM(amount) AS total_updated_sales
FROM updated_sales
GROUP BY product_id;

三、性能优化最佳实践

-- 在CTE查询条件字段上创建索引
CREATE INDEX idx_employees_manager ON employees(manager_id);
CREATE INDEX idx_sales_date ON sales(sale_date);

-- 不好：CTE被多次引用导致重复执行
WITH ExpensiveCTE AS (
    SELECT * FROM large_table 
    WHERE complex_calculation(column) > 100
)
SELECT * FROM ExpensiveCTE t1
JOIN ExpensiveCTE t2 ON t1.id = t2.parent_id;

-- 改进：物化CTE结果（数据库支持时）
WITH ExpensiveCTE AS (
    SELECT * FROM large_table 
    WHERE complex_calculation(column) > 100
)
/* 使用MATERIALIZED关键字（PostgreSQL 12+）*/
SELECT * FROM ExpensiveCTE t1
JOIN ExpensiveCTE t2 ON t1.id = t2.parent_id;

WITH OrderedResults AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (ORDER BY created_at DESC) AS rn,
        COUNT(*) OVER () AS total_count
    FROM products
    WHERE category_id = 5
    AND price BETWEEN 100 AND 1000
)
SELECT 
    product_id,
    product_name,
    price,
    total_count
FROM OrderedResults
WHERE rn BETWEEN 11 AND 20;  -- 第二页

四、实际应用场景

WITH 
ExtractedData AS (
    SELECT 
        customer_id,
        order_date::DATE AS order_date,
        product_sku,
        quantity,
        unit_price,
        quantity * unit_price AS line_total,
        CASE 
            WHEN quantity > 10 THEN 'BULK'
            WHEN quantity > 5 THEN 'MEDIUM'
            ELSE 'SMALL'
        END AS order_size
    FROM staging_orders
    WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
),

TransformedData AS (
    SELECT 
        customer_id,
        order_date,
        product_sku,
        order_size,
        SUM(line_total) AS daily_total,
        COUNT(*) AS daily_orders,
        AVG(quantity) AS avg_quantity
    FROM ExtractedData
    GROUP BY 
        customer_id, 
        order_date, 
        product_sku, 
        order_size
),

LoadedData AS (
    INSERT INTO dw_sales_fact
    SELECT 
        d.customer_id,
        d.order_date,
        p.product_key,
        d.order_size,
        d.daily_total,
        d.daily_orders,
        d.avg_quantity,
        CURRENT_TIMESTAMP AS etl_timestamp
    FROM TransformedData d
    JOIN dw_product_dim p ON d.product_sku = p.sku
    RETURNING *
)

SELECT 
    'ETL Completed' AS status,
    COUNT(*) AS rows_inserted,
    MIN(order_date) AS min_date,
    MAX(order_date) AS max_date
FROM LoadedData;

WITH 
UserSessions AS (
    SELECT 
        user_id,
        session_id,
        MIN(event_time) AS session_start,
        MAX(event_time) AS session_end,
        COUNT(*) AS events_count
    FROM user_events
    WHERE event_time >= CURRENT_DATE - 7
    GROUP BY user_id, session_id
),

SessionMetrics AS (
    SELECT 
        user_id,
        COUNT(*) AS total_sessions,
        AVG(EXTRACT(EPOCH FROM (session_end - session_start))) AS avg_session_duration,
        SUM(events_count) AS total_events
    FROM UserSessions
    GROUP BY user_id
),

UserPurchases AS (
    SELECT 
        user_id,
        COUNT(DISTINCT order_id) AS purchase_count,
        SUM(order_amount) AS total_spent
    FROM orders
    WHERE order_date >= CURRENT_DATE - 30
    GROUP BY user_id
),

UserSegments AS (
    SELECT 
        sm.user_id,
        sm.total_sessions,
        sm.avg_session_duration,
        COALESCE(up.purchase_count, 0) AS purchase_count,
        COALESCE(up.total_spent, 0) AS total_spent,
        CASE 
            WHEN up.purchase_count >= 5 THEN 'VIP'
            WHEN up.purchase_count >= 2 THEN 'Regular'
            WHEN sm.total_sessions >= 3 THEN 'Engaged'
            ELSE 'New'
        END AS user_segment
    FROM SessionMetrics sm
    LEFT JOIN UserPurchases up ON sm.user_id = up.user_id
)

SELECT 
    user_segment,
    COUNT(*) AS user_count,
    AVG(total_sessions) AS avg_sessions,
    AVG(total_spent) AS avg_spent
FROM UserSegments
GROUP BY user_segment
ORDER BY avg_spent DESC;

五、各数据库差异

-- 支持 MATERIALIZED 提示
WITH MATERIALIZED HeavyCTE AS (
    SELECT * FROM very_large_table
)
SELECT * FROM HeavyCTE;

-- 支持数据修改语句
WITH deleted_rows AS (
    DELETE FROM temp_table 
    WHERE created_at < NOW() - INTERVAL '1 hour'
    RETURNING *
)
SELECT COUNT(*) FROM deleted_rows;

-- 支持 WITH RESULT SETS 定义输出格式
WITH SalesCTE AS (
    SELECT * FROM sales
)
SELECT * FROM SalesCTE
WITH RESULT SETS (
    (sale_id INT NOT NULL,
     sale_date DATE,
     amount DECIMAL(10,2))
);

-- 使用 WITH 子句优化器提示
WITH /*+ MATERIALIZE */ HeavyQuery AS (
    SELECT * FROM large_table
)
SELECT * FROM HeavyQuery;

六、调试与维护技巧

-- 分步调试：单独测试每个CTE
WITH 
Step1 AS (
    SELECT * FROM users WHERE active = 1  -- 单独运行
),
Step2 AS (
    SELECT * FROM Step1 WHERE age > 18  -- 单独运行
)

-- 添加调试字段
WITH DebugCTE AS (
    SELECT 
        *,
        CURRENT_TIMESTAMP AS debug_time,
        'Step1' AS debug_step,
        COUNT(*) OVER () AS total_rows
    FROM source_table
)
SELECT * FROM DebugCTE;

/*
目的：计算用户生命周期价值
作者：团队名
创建日期：2024-01-15
更新记录：
  2024-01-20 - 添加用户分段逻辑
  2024-02-01 - 优化性能，添加索引提示
参数：
  @start_date: 分析开始日期
  @min_purchases: 最小购买次数
*/

WITH 
-- 获取基础用户数据
UserBase AS (
    SELECT /*+ INDEX(users idx_users_active) */
        user_id,
        signup_date,
        country
    FROM users
    WHERE active = 1
    AND signup_date >= @start_date
),
...

七、常见陷阱与规避

-- 陷阱：CTE 多次引用导致重复执行
WITH HeavyCTE AS (
    SELECT complex_calculation(*) FROM large_table
)
SELECT * FROM HeavyCTE a
JOIN HeavyCTE b ON a.id = b.parent_id;

-- 解决方案：使用临时表或视图
CREATE TEMP TABLE temp_results AS
SELECT complex_calculation(*) FROM large_table;

SELECT * FROM temp_results a
JOIN temp_results b ON a.id = b.parent_id;

-- 避免过深的嵌套
WITH 
Level1 AS (SELECT ...),
Level2 AS (SELECT ... FROM Level1),
Level3 AS (SELECT ... FROM Level2),
Level4 AS (SELECT ... FROM Level3),
Level5 AS (SELECT ... FROM Level4)  -- 太深了！

-- 保持 CTE 数量合理（通常 3-5 个为佳）

八、最佳实践总结

示例：完整业务分析

-- 电商用户行为漏斗分析
WITH 
UserVisits AS (
    SELECT 
        user_id,
        DATE(visit_time) AS visit_date,
        COUNT(DISTINCT session_id) AS daily_sessions
    FROM web_events
    WHERE visit_time >= '2024-01-01'
    GROUP BY user_id, DATE(visit_time)
),

ProductViews AS (
    SELECT 
        user_id,
        DATE(view_time) AS view_date,
        COUNT(*) AS view_count,
        COUNT(DISTINCT product_id) AS unique_products
    FROM product_views
    WHERE view_time >= '2024-01-01'
    GROUP BY user_id, DATE(view_time)
),

AddToCart AS (
    SELECT 
        user_id,
        DATE(cart_time) AS cart_date,
        COUNT(*) AS cart_items
    FROM cart_events
    WHERE cart_time >= '2024-01-01'
    GROUP BY user_id, DATE(cart_time)
),

Purchases AS (
    SELECT 
        user_id,
        DATE(purchase_time) AS purchase_date,
        SUM(amount) AS purchase_amount,
        COUNT(*) AS purchase_count
    FROM purchases
    WHERE purchase_time >= '2024-01-01'
    GROUP BY user_id, DATE(purchase_time)
),

FunnelAnalysis AS (
    SELECT 
        COALESCE(v.visit_date, pv.view_date, ac.cart_date, pur.purchase_date) AS event_date,
        COUNT(DISTINCT v.user_id) AS visitors,
        COUNT(DISTINCT pv.user_id) AS viewers,
        COUNT(DISTINCT ac.user_id) AS cart_adders,
        COUNT(DISTINCT pur.user_id) AS buyers
    FROM UserVisits v
    FULL OUTER JOIN ProductViews pv 
        ON v.user_id = pv.user_id AND v.visit_date = pv.view_date
    FULL OUTER JOIN AddToCart ac 
        ON COALESCE(v.user_id, pv.user_id) = ac.user_id 
        AND COALESCE(v.visit_date, pv.view_date) = ac.cart_date
    FULL OUTER JOIN Purchases pur 
        ON COALESCE(v.user_id, pv.user_id, ac.user_id) = pur.user_id 
        AND COALESCE(v.visit_date, pv.view_date, ac.cart_date) = pur.purchase_date
    GROUP BY COALESCE(v.visit_date, pv.view_date, ac.cart_date, pur.purchase_date)
)

SELECT 
    event_date,
    visitors,
    viewers,
    cart_adders,
    buyers,
    ROUND(viewers * 100.0 / NULLIF(visitors, 0), 2) AS view_rate,
    ROUND(cart_adders * 100.0 / NULLIF(viewers, 0), 2) AS cart_rate,
    ROUND(buyers * 100.0 / NULLIF(cart_adders, 0), 2) AS conversion_rate
FROM FunnelAnalysis
ORDER BY event_date;

这样的CTE结构使复杂查询逻辑清晰、易于维护和调试，同时保持良好的性能。