多個 CTE 的複雜查詢範例

這堂課我們要來玩點查詢魔法啦！我們會做幾個用多個 CTE 的範例，讓你看看它們怎麼互相結合，打造出複雜又多步驟的查詢。這些例子在現實生活中超實用，尤其是遇到複雜的分析需求時。

範例 1：學生表現分析

想像一下，我們有一個大學資料庫，裡面有三張表：

資料表 students：

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    student_name TEXT NOT NULL
);

資料表 grades：

CREATE TABLE grades (
    grade_id SERIAL PRIMARY KEY,
    student_id INT REFERENCES students(student_id),
    course_id INT NOT NULL,
    grade NUMERIC(3, 1) NOT NULL
);

資料表 courses：

CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    course_name TEXT NOT NULL
);

任務：取得平均分數超過 85 分的學生清單，並顯示他們的平均分數和他們修的課程名稱。

查詢：

WITH high_achievers AS (
    -- 找出平均分數高的學生
    SELECT 
        student_id, 
        AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
    HAVING AVG(grade) > 85
),
student_courses AS (
    -- 找出每個學生修的課程
    SELECT 
        s.student_id, 
        c.course_name
    FROM grades g
    JOIN courses c ON g.course_id = c.course_id
    JOIN students s ON g.student_id = s.student_id
)
-- 合併結果
SELECT 
    s.student_name, 
    ha.avg_grade, 
    sc.course_name
FROM high_achievers ha
JOIN student_courses sc ON ha.student_id = sc.student_id
JOIN students s ON s.student_id = ha.student_id;

說明：

1. 在第一個 CTE（high_achievers）我們計算每個學生的平均分數，只選出分數大於 85 的人。
2. 第二個 CTE（student_courses）把學生和他們修的課程對起來。
3. 主查詢把兩個 CTE 的資料合併，得到學生名、平均分數和他們修的課程。

範例 2：網路商店銷售報表

假設我們在經營一個網路商店，有這幾張表：

資料表 orders（訂單）：

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    total_amount NUMERIC(10, 2) NOT NULL
);

資料表 customers（客戶）：

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    customer_name TEXT NOT NULL
);

資料表 order_items（訂單商品）：

CREATE TABLE order_items (
    order_item_id SERIAL PRIMARY KEY,
    order_id INT REFERENCES orders(order_id),
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    price NUMERIC(10, 2) NOT NULL
);

任務：做一份報表，顯示每個客戶：

• 訂單總數。
• 上個月所有訂單的總金額。
• 他買過的所有獨特商品清單。

查詢：

WITH recent_orders AS (
    -- 選出上個月的訂單
    SELECT 
        order_id, 
        customer_id, 
        total_amount 
    FROM orders
    WHERE order_date >= CURRENT_DATE - INTERVAL '1 month'
),
customer_summary AS (
    -- 計算每個客戶的訂單數和總金額
    SELECT 
        ro.customer_id,
        COUNT(ro.order_id) AS total_orders,
        SUM(ro.total_amount) AS total_spent
    FROM recent_orders ro
    GROUP BY ro.customer_id
),
customer_products AS (
    -- 選出每個客戶買過的獨特商品
    SELECT DISTINCT
        ro.customer_id,
        oi.product_id
    FROM recent_orders ro
    JOIN order_items oi ON ro.order_id = oi.order_id
)
-- 合併結果
SELECT 
    c.customer_name,
    cs.total_orders,
    cs.total_spent,
    ARRAY_AGG(cp.product_id) AS purchased_products
FROM customer_summary cs
JOIN customers c ON cs.customer_id = c.customer_id
JOIN customer_products cp ON cp.customer_id = c.customer_id
GROUP BY c.customer_name, cs.total_orders, cs.total_spent;

說明：

1. 第一個 CTE（recent_orders）選出上個月的訂單。
2. 第二個 CTE（customer_summary）計算每個客戶的訂單數和總金額。
3. 第三個 CTE（customer_products）取得每個客戶買過的獨特商品。
4. 最後查詢把資料合併，並用 ARRAY_AGG() 把商品清單組成陣列。

範例 3：員工階層分析

我們有一張員工表：

資料表 employees：

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    employee_name TEXT NOT NULL,
    manager_id INT NULL
);

任務：建立員工階層，從總經理開始，並標示每個員工在階層中的等級。

查詢：

WITH RECURSIVE employee_hierarchy AS (
    -- 從沒有主管的員工（總經理）開始
    SELECT 
        employee_id, 
        employee_name, 
        manager_id, 
        1 AS level
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    -- 加入當前等級的所有下屬
    SELECT 
        e.employee_id,
        e.employee_name,
        e.manager_id,
        eh.level + 1
    FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
-- 輸出階層
SELECT 
    employee_id, 
    employee_name, 
    manager_id, 
    level
FROM employee_hierarchy
ORDER BY level, employee_id;

1. 在遞迴查詢裡，我們從總經理（沒有主管的員工）開始。
2. 每一步都把當前等級的下屬加進來，level 加一。
3. 主查詢選出整個階層，依等級和員工編號排序。

實用小技巧與常見錯誤

• CTE 太多：能用子查詢就不要硬用 CTE，因為 CTE 有時會因為資料 materialization 影響效能。
• CTE 命名：給你的 CTE 取簡單又有意義的名字，查詢才好讀。
• 執行順序：記得 CTE 是照你宣告的順序執行的。
• 資料分組：只有真的需要時才用 GROUP BY，不然會多做很多沒必要的運算。

這些範例都展示了怎麼用 CTE 把複雜的問題拆成步驟，讓查詢更好讀、也更好維護。現在你已經有工具可以用 PostgreSQL 解決複雜的分析問題啦！