WITH cte_name [(column_list)] AS (
    -- CTE定義查詢
    SELECT ...
)
-- 主查詢
SELECT ... FROM cte_name ...;

-- 示例1：計算訂單統(tǒng)計信息
WITH order_stats AS (
    SELECT 
        AVG(amount) as avg_amount,
        MAX(amount) as max_amount,
        COUNT(*) as total_orders
    FROM orders
    WHERE order_date >= '2024-01-01'
)
SELECT 
    o.order_id,
    o.amount,
    os.avg_amount,
    CASE 
        WHEN o.amount > os.avg_amount THEN '高于平均'
        ELSE '低于平均'
    END as amount_category
FROM orders o
CROSS JOIN order_stats os
WHERE o.order_date >= '2024-01-01';

-- 示例2：多個CTE協(xié)同工作
WITH 
high_value_customers AS (
    SELECT customer_id, SUM(amount) as total_spent
    FROM orders
    GROUP BY customer_id
    HAVING SUM(amount) > 10000
),
recent_orders AS (
    SELECT customer_id, COUNT(*) as recent_order_count
    FROM orders
    WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
    GROUP BY customer_id
)
SELECT 
    c.customer_name,
    hvc.total_spent,
    COALESCE(ro.recent_order_count, 0) as recent_orders
FROM customers c
JOIN high_value_customers hvc ON c.customer_id = hvc.customer_id
LEFT JOIN recent_orders ro ON c.customer_id = ro.customer_id
ORDER BY hvc.total_spent DESC;

-- 示例1：生成數(shù)字序列
WITH RECURSIVE number_series AS (
    -- 錨點成員：起始值
    SELECT 1 as n
    UNION ALL
    -- 遞歸成員：遞增邏輯
    SELECT n + 1
    FROM number_series
    WHERE n < 10  -- 終止條件
)
SELECT * FROM number_series;

-- 示例2：組織架構(gòu)查詢（查找某員工及其所有下屬）
WITH RECURSIVE employee_hierarchy AS (
    -- 錨點成員：指定的管理者
    SELECT 
        employee_id,
        employee_name,
        manager_id,
        0 as level,
        CAST(employee_name AS VARCHAR(1000)) as path
    FROM employees
    WHERE employee_id = 1001  -- 起始員工ID
    UNION ALL
    -- 遞歸成員：查找下屬
    SELECT 
        e.employee_id,
        e.employee_name,
        e.manager_id,
        eh.level + 1,
        CAST(eh.path || ' -> ' || e.employee_name AS VARCHAR(1000))
    FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
    WHERE eh.level < 5  -- 防止無限遞歸
)
SELECT 
    employee_id,
    employee_name,
    level,
    path as hierarchy_path
FROM employee_hierarchy
ORDER BY level, employee_name;

-- 示例3：向上追溯管理鏈
WITH RECURSIVE management_chain AS (
    -- 錨點成員：指定員工
    SELECT 
        employee_id,
        employee_name,
        manager_id,
        0 as level_up
    FROM employees
    WHERE employee_id = 2001  -- 起始員工
    UNION ALL
    -- 遞歸成員：查找上級管理者
    SELECT 
        e.employee_id,
        e.employee_name,
        e.manager_id,
        mc.level_up + 1
    FROM employees e
    JOIN management_chain mc ON e.employee_id = mc.manager_id
)
SELECT * FROM management_chain ORDER BY level_up;

-- 強制物化（默認(rèn)行為）
WITH cte_name AS MATERIALIZED (
    SELECT expensive_calculation() FROM large_table
)
SELECT * FROM cte_name 
UNION ALL 
SELECT * FROM cte_name;  -- 復(fù)用已計算的結(jié)果
-- 禁止物化（內(nèi)聯(lián)優(yōu)化）
WITH cte_name AS NOT MATERIALIZED (
    SELECT * FROM small_table WHERE condition
)
SELECT * FROM cte_name WHERE additional_condition;

-- 查看CTE執(zhí)行計劃
EXPLAIN (ANALYZE, BUFFERS) 
WITH sales_summary AS (
    SELECT 
        product_id,
        SUM(quantity) as total_quantity,
        SUM(amount) as total_amount
    FROM sales
    WHERE sale_date >= '2024-01-01'
    GROUP BY product_id
)
SELECT 
    p.product_name,
    ss.total_quantity,
    ss.total_amount
FROM products p
JOIN sales_summary ss ON p.product_id = ss.product_id;

-- 策略1：使用計數(shù)器限制遞歸深度
WITH RECURSIVE limited_recursion AS (
    SELECT id, parent_id, name, 0 as depth
    FROM categories
    WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.parent_id, c.name, lr.depth + 1
    FROM categories c
    JOIN limited_recursion lr ON c.parent_id = lr.id
    WHERE lr.depth < 10  -- 限制最大深度
)
SELECT * FROM limited_recursion;
-- 策略2：使用路徑檢測避免循環(huán)
WITH RECURSIVE path_tracking AS (
    SELECT 
        id, 
        parent_id, 
        name,
        ARRAY[id] as path
    FROM categories
    WHERE parent_id IS NULL
    UNION ALL
    SELECT 
        c.id, 
        c.parent_id, 
        c.name,
        pt.path || c.id
    FROM categories c
    JOIN path_tracking pt ON c.parent_id = pt.id
    WHERE NOT (c.id = ANY(pt.path))  -- 避免循環(huán)
)
SELECT * FROM path_tracking;

-- ? 推薦：需要多次引用相同結(jié)果時
WITH expensive_calc AS (
    SELECT 
        customer_id,
        complex_calculation(data) as result
    FROM large_table
    WHERE complex_condition
)
SELECT c1.customer_id, c1.result, c2.result
FROM expensive_calc c1
JOIN expensive_calc c2 ON c1.customer_id = c2.customer_id + 1;
-- ? 不推薦：簡單的一次性查詢
SELECT * FROM (
    SELECT * FROM small_table WHERE simple_condition
) subquery;

-- 確保遞歸CTE中的連接字段有索引
CREATE INDEX idx_categories_parent_id ON categories(parent_id);
CREATE INDEX idx_employees_manager_id ON employees(manager_id);
-- 在遞歸查詢中使用索引友好的條件
WITH RECURSIVE category_tree AS (
    SELECT id, parent_id, name, 0 as level
    FROM categories
    WHERE id = 1  -- 使用主鍵，利用主鍵索引
    UNION ALL
    SELECT c.id, c.parent_id, c.name, ct.level + 1
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id  -- 利用外鍵索引
    WHERE ct.level < 5
)
SELECT * FROM category_tree;

-- 設(shè)置合理的遞歸深度限制
SET max_stack_depth = '2MB';  -- PostgreSQL
-- 或在查詢中使用WHERE條件限制深度

-- ? 只選擇必要的字段
WITH RECURSIVE slim_hierarchy AS (
    SELECT id, parent_id, level  -- 只選擇必要字段
    FROM categories
    WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.parent_id, sh.level + 1
    FROM categories c
    JOIN slim_hierarchy sh ON c.parent_id = sh.id
    WHERE sh.level < 10
)
SELECT sh.id, sh.level, c.name  -- 在最后再JOIN獲取詳細(xì)信息
FROM slim_hierarchy sh
JOIN categories c ON sh.id = c.id;

-- 大數(shù)據(jù)量遞歸查詢的分批處理
WITH RECURSIVE batch_process AS (
    SELECT id, parent_id, name, 0 as level, 0 as batch_num
    FROM categories
    WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.parent_id, c.name, bp.level + 1, 
           CASE WHEN bp.level % 1000 = 0 THEN bp.batch_num + 1 
                ELSE bp.batch_num END
    FROM categories c
    JOIN batch_process bp ON c.parent_id = bp.id
    WHERE bp.level < 10000 AND bp.batch_num < 10
)
SELECT * FROM batch_process;

-- SQL Server遞歸CTE（無需RECURSIVE關(guān)鍵字）
WITH employee_cte AS (
    -- 錨點成員
    SELECT employee_id, manager_id, employee_name, 0 as level
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    -- 遞歸成員
    SELECT e.employee_id, e.manager_id, e.employee_name, ec.level + 1
    FROM employees e
    INNER JOIN employee_cte ec ON e.manager_id = ec.employee_id
)
SELECT * FROM employee_cte
OPTION (MAXRECURSION 100);  -- SQL Server特有的遞歸限制語法

-- Oracle的CTE（子查詢因子化）
WITH 
sales_data AS (
    SELECT product_id, SUM(amount) as total_sales
    FROM sales
    WHERE sale_date >= DATE '2024-01-01'
    GROUP BY product_id
),
product_info AS (
    SELECT product_id, product_name, category_id
    FROM products
)
SELECT pi.product_name, sd.total_sales
FROM product_info pi
JOIN sales_data sd ON pi.product_id = sd.product_id
ORDER BY sd.total_sales DESC;

-- MySQL遞歸CTE
WITH RECURSIVE fibonacci AS (
    SELECT 0 as n, 0 as fib_n, 1 as fib_n_plus_1
    UNION ALL
    SELECT n + 1, fib_n_plus_1, fib_n + fib_n_plus_1
    FROM fibonacci
    WHERE n < 20
)
SELECT n, fib_n FROM fibonacci;

-- MySQL 5.x 使用臨時表替代CTE
-- 替代普通CTE
CREATE TEMPORARY TABLE temp_order_stats AS
SELECT AVG(amount) as avg_amount FROM orders;
SELECT o.*, t.avg_amount
FROM orders o
CROSS JOIN temp_order_stats t
WHERE o.amount > t.avg_amount;
DROP TEMPORARY TABLE temp_order_stats;
-- 替代遞歸CTE（使用存儲過程）
DELIMITER //
CREATE PROCEDURE GetEmployeeHierarchy(IN root_id INT)
BEGIN
    CREATE TEMPORARY TABLE temp_hierarchy (
        employee_id INT,
        level INT
    );
    INSERT INTO temp_hierarchy VALUES (root_id, 0);
    SET @level = 0;
    WHILE ROW_COUNT() > 0 AND @level < 10 DO
        INSERT INTO temp_hierarchy
        SELECT e.employee_id, @level + 1
        FROM employees e
        JOIN temp_hierarchy th ON e.manager_id = th.employee_id
        WHERE th.level = @level;
        SET @level = @level + 1;
    END WHILE;
    SELECT * FROM temp_hierarchy;
    DROP TEMPORARY TABLE temp_hierarchy;
END //
DELIMITER ;

WITH sales_funnel AS (
    SELECT 
        'Leads' as stage,
        COUNT(*) as count,
        1 as stage_order
    FROM leads
    WHERE created_date >= '2024-01-01'
    UNION ALL
    SELECT 
        'Qualified Leads' as stage,
        COUNT(*) as count,
        2 as stage_order
    FROM leads
    WHERE status = 'qualified' AND created_date >= '2024-01-01'
    UNION ALL
    SELECT 
        'Opportunities' as stage,
        COUNT(*) as count,
        3 as stage_order
    FROM opportunities
    WHERE created_date >= '2024-01-01'
    UNION ALL
    SELECT 
        'Closed Won' as stage,
        COUNT(*) as count,
        4 as stage_order
    FROM opportunities
    WHERE status = 'won' AND created_date >= '2024-01-01'
),
funnel_with_conversion AS (
    SELECT 
        stage,
        count,
        stage_order,
        LAG(count) OVER (ORDER BY stage_order) as previous_count,
        CASE 
            WHEN LAG(count) OVER (ORDER BY stage_order) > 0 
            THEN ROUND(count::DECIMAL / LAG(count) OVER (ORDER BY stage_order) * 100, 2)
            ELSE 100.0
        END as conversion_rate
    FROM sales_funnel
)
SELECT 
    stage,
    count,
    conversion_rate || '%' as conversion_rate
FROM funnel_with_conversion
ORDER BY stage_order;

WITH customer_cohorts AS (
    SELECT 
        customer_id,
        DATE_TRUNC('month', MIN(order_date)) as cohort_month
    FROM orders
    GROUP BY customer_id
),
customer_activities AS (
    SELECT 
        cc.cohort_month,
        DATE_TRUNC('month', o.order_date) as activity_month,
        COUNT(DISTINCT o.customer_id) as active_customers
    FROM customer_cohorts cc
    JOIN orders o ON cc.customer_id = o.customer_id
    GROUP BY cc.cohort_month, DATE_TRUNC('month', o.order_date)
),
cohort_table AS (
    SELECT 
        cohort_month,
        activity_month,
        active_customers,
        EXTRACT(EPOCH FROM (activity_month - cohort_month)) / (30 * 24 * 60 * 60) as month_number
    FROM customer_activities
)
SELECT 
    cohort_month,
    month_number,
    active_customers,
    FIRST_VALUE(active_customers) OVER (
        PARTITION BY cohort_month 
        ORDER BY month_number
    ) as cohort_size,
    ROUND(
        active_customers::DECIMAL / 
        FIRST_VALUE(active_customers) OVER (
            PARTITION BY cohort_month 
            ORDER BY month_number
        ) * 100, 2
    ) as retention_rate
FROM cohort_table
ORDER BY cohort_month, month_number;

-- 查詢用戶的所有有效權(quán)限（包括繼承的權(quán)限）
WITH RECURSIVE user_permissions AS (
    -- 直接權(quán)限
    SELECT 
        up.user_id,
        up.permission_id,
        p.permission_name,
        'direct' as permission_source,
        0 as inheritance_level
    FROM user_permissions up
    JOIN permissions p ON up.permission_id = p.permission_id
    WHERE up.user_id = :user_id
    UNION ALL
    -- 角色繼承的權(quán)限
    SELECT 
        ur.user_id,
        rp.permission_id,
        p.permission_name,
        'role:' || r.role_name as permission_source,
        1 as inheritance_level
    FROM user_roles ur
    JOIN roles r ON ur.role_id = r.role_id
    JOIN role_permissions rp ON r.role_id = rp.role_id
    JOIN permissions p ON rp.permission_id = p.permission_id
    WHERE ur.user_id = :user_id
    UNION ALL
    -- 角色層級繼承的權(quán)限
    SELECT 
        up.user_id,
        rp.permission_id,
        p.permission_name,
        'inherited_role:' || pr.role_name as permission_source,
        up.inheritance_level + 1
    FROM user_permissions up
    JOIN user_roles ur ON up.user_id = ur.user_id
    JOIN role_hierarchy rh ON ur.role_id = rh.child_role_id
    JOIN roles pr ON rh.parent_role_id = pr.role_id
    JOIN role_permissions rp ON pr.role_id = rp.role_id
    JOIN permissions p ON rp.permission_id = p.permission_id
    WHERE up.inheritance_level < 3  -- 限制繼承深度
)
SELECT DISTINCT 
    permission_id,
    permission_name,
    MIN(inheritance_level) as min_inheritance_level,
    STRING_AGG(DISTINCT permission_source, ', ') as sources
FROM user_permissions
GROUP BY permission_id, permission_name
ORDER BY min_inheritance_level, permission_name;

-- 移動分類及其所有子分類到新的父分類下
WITH RECURSIVE category_subtree AS (
    -- 要移動的分類及其子分類
    SELECT id, parent_id, name, 0 as level
    FROM categories
    WHERE id = :category_to_move
    UNION ALL
    SELECT c.id, c.parent_id, c.name, cs.level + 1
    FROM categories c
    JOIN category_subtree cs ON c.parent_id = cs.id
),
update_plan AS (
    SELECT 
        cs.id,
        CASE 
            WHEN cs.level = 0 THEN :new_parent_id
            ELSE cs.parent_id
        END as new_parent_id
    FROM category_subtree cs
)
UPDATE categories 
SET parent_id = up.new_parent_id,
    updated_at = CURRENT_TIMESTAMP
FROM update_plan up
WHERE categories.id = up.id;

WITH RECURSIVE date_series AS (
    SELECT DATE '2024-01-01' as date_val
    UNION ALL
    SELECT date_val + INTERVAL '1 day'
    FROM date_series
    WHERE date_val < DATE '2024-12-31'
),
daily_sales AS (
    SELECT 
        DATE(order_date) as sale_date,
        SUM(amount) as daily_amount,
        COUNT(*) as daily_orders
    FROM orders
    WHERE order_date >= '2024-01-01' 
      AND order_date < '2025-01-01'
    GROUP BY DATE(order_date)
)
SELECT 
    ds.date_val,
    COALESCE(dsales.daily_amount, 0) as amount,
    COALESCE(dsales.daily_orders, 0) as orders,
    -- 計算7天移動平均
    AVG(COALESCE(dsales.daily_amount, 0)) OVER (
        ORDER BY ds.date_val 
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) as moving_avg_7_days
FROM date_series ds
LEFT JOIN daily_sales dsales ON ds.date_val = dsales.sale_date
ORDER BY ds.date_val;

-- 分析用戶會話，定義30分鐘無活動為會話結(jié)束
WITH RECURSIVE user_sessions AS (
    SELECT 
        user_id,
        event_time,
        event_type,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_time) as rn,
        event_time as session_start,
        1 as session_id
    FROM user_events
    WHERE user_id = :user_id
      AND event_time >= :start_date
    UNION ALL
    SELECT 
        ue.user_id,
        ue.event_time,
        ue.event_type,
        us.rn + 1,
        CASE 
            WHEN ue.event_time - us.event_time > INTERVAL '30 minutes'
            THEN ue.event_time
            ELSE us.session_start
        END,
        CASE 
            WHEN ue.event_time - us.event_time > INTERVAL '30 minutes'
            THEN us.session_id + 1
            ELSE us.session_id
        END
    FROM user_events ue
    JOIN user_sessions us ON ue.user_id = us.user_id 
                          AND ue.event_time > us.event_time
    WHERE ue.user_id = :user_id
      AND ue.event_time >= :start_date
      AND us.rn = (SELECT MAX(rn) FROM user_sessions WHERE user_id = us.user_id)
)
SELECT 
    session_id,
    session_start,
    MAX(event_time) as session_end,
    COUNT(*) as event_count,
    MAX(event_time) - session_start as session_duration
FROM user_sessions
GROUP BY session_id, session_start
ORDER BY session_start;

-- ? 推薦：使用描述性的CTE名稱
WITH 
high_value_customers AS (...),
recent_orders AS (...),
product_performance AS (...)
-- ? 避免：使用模糊的名稱
WITH 
cte1 AS (...),
temp AS (...),
data AS (...)

-- ? 推薦：按邏輯順序組織多個CTE
WITH 
-- 基礎(chǔ)數(shù)據(jù)提取
raw_sales_data AS (
    SELECT customer_id, product_id, amount, sale_date
    FROM sales
    WHERE sale_date >= '2024-01-01'
),
-- 數(shù)據(jù)聚合
customer_totals AS (
    SELECT customer_id, SUM(amount) as total_spent
    FROM raw_sales_data
    GROUP BY customer_id
),
-- 分類標(biāo)記
customer_segments AS (
    SELECT 
        customer_id,
        total_spent,
        CASE 
            WHEN total_spent > 10000 THEN 'VIP'
            WHEN total_spent > 5000 THEN 'Premium'
            ELSE 'Standard'
        END as segment
    FROM customer_totals
)
-- 最終查詢
SELECT 
    c.customer_name,
    cs.total_spent,
    cs.segment
FROM customers c
JOIN customer_segments cs ON c.customer_id = cs.customer_id
ORDER BY cs.total_spent DESC;

-- ? 推薦：明確的終止條件
WITH RECURSIVE hierarchy AS (
    SELECT id, parent_id, name, 0 as level
    FROM categories
    WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.parent_id, c.name, h.level + 1
    FROM categories c
    JOIN hierarchy h ON c.parent_id = h.id
    WHERE h.level < 10  -- 明確的深度限制
      AND c.parent_id IS NOT NULL  -- 防止NULL值問題
)
SELECT * FROM hierarchy;

-- ? 推薦：檢測和防止循環(huán)引用
WITH RECURSIVE safe_hierarchy AS (
    SELECT 
        id, 
        parent_id, 
        name, 
        0 as level,
        ARRAY[id] as path
    FROM categories
    WHERE parent_id IS NULL
    UNION ALL
    SELECT 
        c.id, 
        c.parent_id, 
        c.name, 
        sh.level + 1,
        sh.path || c.id
    FROM categories c
    JOIN safe_hierarchy sh ON c.parent_id = sh.id
    WHERE sh.level < 20
      AND NOT (c.id = ANY(sh.path))  -- 防止循環(huán)
)
SELECT id, name, level, array_to_string(path, ' -> ') as path
FROM safe_hierarchy;

-- 為遞歸查詢創(chuàng)建合適的索引
CREATE INDEX idx_categories_parent_id ON categories(parent_id);
CREATE INDEX idx_categories_id_parent_id ON categories(id, parent_id);
-- 復(fù)合索引用于復(fù)雜遞歸查詢
CREATE INDEX idx_employees_manager_dept ON employees(manager_id, department_id);

-- ? 推薦：在CTE中盡早過濾數(shù)據(jù)
WITH filtered_orders AS (
    SELECT customer_id, amount, order_date
    FROM orders
    WHERE order_date >= '2024-01-01'  -- 盡早過濾
      AND status = 'completed'
      AND amount > 0
),
customer_stats AS (
    SELECT 
        customer_id,
        COUNT(*) as order_count,
        SUM(amount) as total_amount
    FROM filtered_orders  -- 使用已過濾的數(shù)據(jù)
    GROUP BY customer_id
)
SELECT * FROM customer_stats
WHERE order_count >= 5;  -- 進(jìn)一步過濾

-- ? 危險：可能導(dǎo)致無限遞歸
WITH RECURSIVE dangerous_recursion AS (
    SELECT 1 as n
    UNION ALL
    SELECT n + 1 FROM dangerous_recursion  -- 沒有終止條件！
)
SELECT * FROM dangerous_recursion;
-- ? 安全：包含終止條件
WITH RECURSIVE safe_recursion AS (
    SELECT 1 as n
    UNION ALL
    SELECT n + 1 FROM safe_recursion WHERE n < 100
)
SELECT * FROM safe_recursion;

-- 處理可能存在循環(huán)引用的數(shù)據(jù)
-- 假設(shè)categories表中存在循環(huán)引用：A -> B -> C -> A
-- ? 問題：可能導(dǎo)致無限遞歸
WITH RECURSIVE bad_hierarchy AS (
    SELECT id, parent_id, name FROM categories WHERE id = 1
    UNION ALL
    SELECT c.id, c.parent_id, c.name
    FROM categories c
    JOIN bad_hierarchy bh ON c.parent_id = bh.id
)
SELECT * FROM bad_hierarchy;
-- ? 解決：使用路徑跟蹤防止循環(huán)
WITH RECURSIVE good_hierarchy AS (
    SELECT id, parent_id, name, ARRAY[id] as path
    FROM categories WHERE id = 1
    UNION ALL
    SELECT c.id, c.parent_id, c.name, gh.path || c.id
    FROM categories c
    JOIN good_hierarchy gh ON c.parent_id = gh.id
    WHERE NOT (c.id = ANY(gh.path))
)
SELECT id, parent_id, name FROM good_hierarchy;

-- ? 過度使用：簡單查詢不需要CTE
WITH simple_cte AS (
    SELECT * FROM users WHERE status = 'active'
)
SELECT * FROM simple_cte WHERE age > 18;
-- ? 直接查詢更簡單高效
SELECT * FROM users 
WHERE status = 'active' AND age > 18;

-- ? 問題：大數(shù)據(jù)量遞歸可能導(dǎo)致內(nèi)存溢出
WITH RECURSIVE large_hierarchy AS (
    SELECT id, parent_id, name FROM large_table WHERE parent_id IS NULL
    UNION ALL
    SELECT lt.id, lt.parent_id, lt.name
    FROM large_table lt
    JOIN large_hierarchy lh ON lt.parent_id = lh.id
)
SELECT * FROM large_hierarchy;
-- ? 解決：分批處理或限制深度
WITH RECURSIVE controlled_hierarchy AS (
    SELECT id, parent_id, name, 0 as level FROM large_table WHERE parent_id IS NULL
    UNION ALL
    SELECT lt.id, lt.parent_id, lt.name, ch.level + 1
    FROM large_table lt
    JOIN controlled_hierarchy ch ON lt.parent_id = ch.id
    WHERE ch.level < 5  -- 限制深度
)
SELECT * FROM controlled_hierarchy;

-- ? 問題：數(shù)據(jù)類型不匹配
WITH RECURSIVE type_mismatch AS (
    SELECT 1 as id, 'root' as name  -- name是VARCHAR
    UNION ALL
    SELECT id + 1, id + 1 FROM type_mismatch WHERE id < 5  -- name變成了INTEGER
)
SELECT * FROM type_mismatch;
-- ? 解決：確保類型一致
WITH RECURSIVE type_consistent AS (
    SELECT 1 as id, 'root' as name
    UNION ALL
    SELECT id + 1, CAST(id + 1 AS VARCHAR) FROM type_consistent WHERE id < 5
)
SELECT * FROM type_consistent;

-- ? 正確處理NULL值
WITH RECURSIVE null_safe_hierarchy AS (
    SELECT id, parent_id, name, 0 as level
    FROM categories
    WHERE parent_id IS NULL  -- 明確處理NULL
    UNION ALL
    SELECT c.id, c.parent_id, c.name, nsh.level + 1
    FROM categories c
    JOIN null_safe_hierarchy nsh ON c.parent_id = nsh.id
    WHERE c.parent_id IS NOT NULL  -- 防止NULL值問題
      AND nsh.level < 10
)
SELECT * FROM null_safe_hierarchy;

-- 計算每個產(chǎn)品的銷售趨勢
WITH monthly_sales AS (
    SELECT 
        product_id,
        DATE_TRUNC('month', order_date) as month,
        SUM(amount) as monthly_amount
    FROM orders
    WHERE order_date >= '2024-01-01'
    GROUP BY product_id, DATE_TRUNC('month', order_date)
),
sales_with_trends AS (
    SELECT 
        product_id,
        month,
        monthly_amount,
        LAG(monthly_amount) OVER (PARTITION BY product_id ORDER BY month) as prev_month_amount,
        AVG(monthly_amount) OVER (
            PARTITION BY product_id 
            ORDER BY month 
            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
        ) as moving_avg_3_months
    FROM monthly_sales
)
SELECT 
    p.product_name,
    swt.month,
    swt.monthly_amount,
    swt.moving_avg_3_months,
    CASE 
        WHEN swt.prev_month_amount IS NULL THEN 'N/A'
        WHEN swt.monthly_amount > swt.prev_month_amount THEN 'Increasing'
        WHEN swt.monthly_amount < swt.prev_month_amount THEN 'Decreasing'
        ELSE 'Stable'
    END as trend
FROM sales_with_trends swt
JOIN products p ON swt.product_id = p.product_id
ORDER BY p.product_name, swt.month;

WITH RECURSIVE fibonacci AS (
    SELECT 
        1 as n,
        0::BIGINT as fib_current,
        1::BIGINT as fib_next
    UNION ALL
    SELECT 
        n + 1,
        fib_next,
        fib_current + fib_next
    FROM fibonacci
    WHERE n < 50 AND fib_next < 9223372036854775807  -- 防止溢出
)
SELECT n, fib_current as fibonacci_number
FROM fibonacci;

WITH RECURSIVE business_days AS (
    SELECT DATE '2024-01-01' as business_date
    WHERE EXTRACT(DOW FROM DATE '2024-01-01') BETWEEN 1 AND 5
    UNION ALL
    SELECT 
        CASE 
            WHEN EXTRACT(DOW FROM business_date + 1) = 6 THEN business_date + 3  -- 跳過周末
            WHEN EXTRACT(DOW FROM business_date + 1) = 0 THEN business_date + 2
            ELSE business_date + 1
        END
    FROM business_days
    WHERE business_date < DATE '2024-12-31'
),
business_days_with_holidays AS (
    SELECT bd.business_date
    FROM business_days bd
    LEFT JOIN holidays h ON bd.business_date = h.holiday_date
    WHERE h.holiday_date IS NULL  -- 排除節(jié)假日
)
SELECT business_date FROM business_days_with_holidays ORDER BY business_date;

-- 復(fù)雜的數(shù)據(jù)清洗流程
WITH 
-- 第一步：基礎(chǔ)數(shù)據(jù)清洗
cleaned_raw_data AS (
    SELECT 
        customer_id,
        TRIM(UPPER(customer_name)) as customer_name,
        CASE 
            WHEN email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$' 
            THEN LOWER(email)
            ELSE NULL
        END as email,
        CASE 
            WHEN phone ~ '^\d{10,15}$' THEN phone
            ELSE REGEXP_REPLACE(phone, '[^\d]', '', 'g')
        END as phone
    FROM raw_customer_data
    WHERE customer_name IS NOT NULL
),
-- 第二步：去重處理
deduplicated_data AS (
    SELECT DISTINCT ON (customer_name, email)
        customer_id,
        customer_name,
        email,
        phone,
        ROW_NUMBER() OVER (PARTITION BY customer_name, email ORDER BY customer_id) as rn
    FROM cleaned_raw_data
    WHERE email IS NOT NULL
),
-- 第三步：數(shù)據(jù)驗證
validated_data AS (
    SELECT 
        customer_id,
        customer_name,
        email,
        phone,
        CASE 
            WHEN LENGTH(customer_name) < 2 THEN 'Invalid Name'
            WHEN email IS NULL THEN 'Invalid Email'
            WHEN LENGTH(phone) < 10 THEN 'Invalid Phone'
            ELSE 'Valid'
        END as validation_status
    FROM deduplicated_data
    WHERE rn = 1
)
-- 最終結(jié)果
SELECT 
    customer_id,
    customer_name,
    email,
    phone,
    validation_status
FROM validated_data
WHERE validation_status = 'Valid';

-- 在有向圖中查找從起點到終點的所有路徑
WITH RECURSIVE all_paths AS (
    -- 起始節(jié)點
    SELECT 
        start_node,
        end_node,
        ARRAY[start_node, end_node] as path,
        1 as path_length
    FROM graph_edges
    WHERE start_node = :start_point
    UNION ALL
    -- 擴展路徑
    SELECT 
        ap.start_node,
        ge.end_node,
        ap.path || ge.end_node,
        ap.path_length + 1
    FROM all_paths ap
    JOIN graph_edges ge ON ap.end_node = ge.start_node
    WHERE NOT (ge.end_node = ANY(ap.path))  -- 避免循環(huán)
      AND ap.path_length < 10  -- 限制路徑長度
)
SELECT 
    start_node,
    end_node,
    path,
    path_length
FROM all_paths
WHERE end_node = :end_point  -- 過濾到目標(biāo)節(jié)點的路徑
ORDER BY path_length, path;