簡單 CTE 做資料準備：範例跟真實案例

簡單 CTE 做資料準備：範例跟真實案例

你大概已經搞懂 CTE 的基本用法，甚至寫 WITH 都快變成肌肉記憶了。今天我們來更深入一點——看看怎麼在真實情境下用 CTE 做資料準備。想像一下你要做一份報表或寫一個複雜的 SQL 查詢：第一步就是把原料拆開——然後才開始煮出好吃的分析「湯」。

CTE 在這裡超好用，因為它可以做中間步驟：像是篩選、計算、聚合、算平均值——反正資料準備要用到的都能搞定。你可以把複雜查詢拆成幾個很清楚的邏輯區塊，每個區塊只做一件事：挑出需要的資料、算平均、或是幫最後查詢準備好資料。這樣不只程式碼好讀，也不用一直複製貼上重複的片段，更不用硬生生建一堆 temp table（如果你根本不想要的話）。

這種 CTE 的寫法特別適合你要做報表、做複雜篩選，或是想在進一步處理前先「清理」資料。這時候 CTE 不只是個技術小技巧，而是完整的策略——一步一步把邏輯排好，過程都在你掌控之中。

準備好了嗎？我們馬上來看範例。

用 CTE 篩選資料

CTE 超適合把你要的資料「撈」出來，這樣後面就只要處理你真的需要的東西。你不用寫一堆巢狀查詢，先把資料篩出來、給這個步驟取個名字——然後就可以像用一般 table 一樣用這個結果。

假設我們有一張 students 表，裡面存學生的成績：

表 students

假設你想挑出成績大於 85 的人。用 CTE 做這件事超直觀：

WITH excellent_students AS (
    SELECT student_id, first_name, last_name, grade
    FROM students
    WHERE grade > 85
)
SELECT * FROM excellent_students;

結果：

這裡有什麼方便的？

你先把要的資料挑出來，給這個步驟一個名字——excellent_students。這個結果你可以繼續用：像是跟別的表 join、再做一次篩選、或是算平均成績。整個查詢很清楚、簡單又不會亂掉，特別是查詢很大的時候。

用 CTE 做資料聚合

接下來我們來看要怎麼算資料筆數或平均值。比如說，我們有一張 enrollments 表，記錄學生選了哪些課。

表 enrollments

我們想知道每門課有多少學生選。

查詢範例：

WITH course_enrollments AS (
    SELECT course_id, COUNT(student_id) AS student_count
    FROM enrollments
    GROUP BY course_id
)
SELECT * FROM course_enrollments;

結果：

這裡重點：

• 我們用 course_id 分組，算出每門課有幾個學生。
• 表 course_enrollments 現在就有這些資訊，可以拿來做進一步分析。

準備報表資料

如果你要做一份詳細報表，需要經過好幾步資料處理，CTE 就是你的好朋友。它可以把整個邏輯拆成幾個很清楚的區塊，而且不用多建一堆 temp table。想像你有一張 grades 表存成績，還有一張 students 表存學生資料。你要做一份報表，只列出平均分數超過 80 的學生。

表 grades

表 students

不用寫很長的巢狀查詢，可以一步一步來：

WITH avg_grades AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
    HAVING AVG(grade) > 80
),
students_with_grades AS (
    SELECT s.student_id, s.first_name, s.last_name, ag.avg_grade
    FROM students s
    JOIN avg_grades ag ON s.student_id = ag.student_id
)
SELECT * FROM students_with_grades;

第一步（avg_grades）我們算出每個學生的平均分數，直接只留成績好的（超過 80）。第二步（students_with_grades）把這些資料跟 students 表 join 起來，拿到名字跟姓氏。最後 SELECT 出來的表格就可以直接貼進報表——都算好了、篩好了、排版也很漂亮。

結果：

這種寫法就是 CTE 的強大之處：你可以專心在邏輯跟結構，不用分心去建或刪 temp table 這種雜事。

計算複雜指標

有時候你要在一個查詢裡組合不同的資料。比如說，我們要算每門課：

1. 學生人數。
2. 課程平均分數。

查詢範例：

WITH course_counts AS (
    SELECT course_id, COUNT(student_id) AS student_count
    FROM enrollments
    GROUP BY course_id
),
course_avg_grades AS (
    SELECT e.course_id, AVG(g.grade) AS avg_grade
    FROM enrollments e
    JOIN grades g ON e.student_id = g.student_id
    GROUP BY e.course_id
)
SELECT cc.course_id, cc.student_count, cag.avg_grade
FROM course_counts cc
JOIN course_avg_grades cag ON cc.course_id = cag.course_id;

常見錯誤要避開

用 CTE 的時候，其實很容易踩到幾個常見地雷。

第一個——過度 materialization。如果你建太多 CTE，PostgreSQL 可能會把它們的結果存成 temp table，就算你只用一次。結果查詢會變慢，完全沒必要。

第二個錯誤——濾掉資料的順序搞錯。如果你在不同階段亂加 filter，最後查出來的資料可能跟你想的不一樣。比如說，你可能太早就把重要資料濾掉了。

所以 CTE 最好用在資料要經過幾個連續轉換的時候——這時候它的優點才會完全發揮，讓你寫出乾淨、好懂又有效率的 code。