PostgreSQL 筆記 - Common Table Expression 與 View

在 PostgreSQL 中，Common Table Expression (CTE) 與 View 是協助我們將查詢結構化並提升可讀性的強大工具。以下將依序介紹 CTE（含遞迴形式）、View 與 Materialized View 的使用方式與適用時機。

Common Table Expression (CTE)

CTE 以 WITH 關鍵字開頭，能在主要查詢中建立一個臨時結果集，讓查詢更清晰並方便重複使用。

基本範例

這段查詢將兩個標籤來源合併，並取得使用者在指定日期前的相關紀錄。

Recursive Common Table Expression

遞迴 CTE 特別適合用於處理樹狀或圖狀結構的資料。其特點是必須使用 UNION 來結合初始查詢與遞迴查詢。

範例：倒數計算

執行結果：

遞迴運作流程

1. 定義結果表與工作表。
2. 執行初始查詢並放入結果表與工作表。
3. 將遞迴查詢套用在工作表上。
4. 若產生新列，加入結果表並再次遞迴。
5. 若無新列產生，則結束遞迴。

遞迴 CTE 的應用

在社群平台上，遞迴 CTE 可以用來做「我們可能認識的人」推薦。例如：我和 B 是朋友，B 又和 C 是朋友，但我還不認識 C，系統就能推薦 C 給我。

Create a View

在 PostgreSQL 中，View 可以把它想成是一個「查詢結果的假資料表」。View 的內容是從其他資料表查詢來的資料，這些資料可以直接拿來用，也可以先做運算再用。我們可以像操作一般資料表一樣使用 View，把它放在查詢裡的任何地方。要注意的是，View 並不會真的建立一張新表或複製資料，它只是把查詢包裝起來，方便我們重複使用。而且 View 不只可以做 union，也可以在裡面計算出我們想要的任何結果。

NOTE: View 是一種「虛擬表」，它並不會真的建立或搬移資料，而是將查詢結果封裝，方便之後重複使用。

建立 View

每次呼叫 View 時，查詢都會被重新執行。

修改與刪除 View

View 的常見使用情境

使用 View 的常見情境包括：

1. 報表統計： 假設需要經常查詢「每位使用者的訂單總金額」，就能建立一個 View，把 users 和 orders 的 JOIN 及 SUM 運算包起來，之後只要查這個 View 就好。
2. 權限控管： 若要讓某些員工查詢顧客資料，但不能看到 email，可以建立一個 View 只回傳 id、username，而不包含敏感欄位。

Materialized View

Materialized View 與普通 View 不同，它會將查詢結果「實際儲存」下來，只在手動或特定時間點刷新資料，適合處理昂貴的查詢。

範例：統計每週的貼文與留言按讚數

有時候我們需要進行一些複雜的查詢，例如統計使用者在貼文與留言上的按讚數。如果資料量龐大，每次都即時計算不僅耗時，還會影響系統效能。這時候，我們可以考慮透過 Materialized View 來提升效率。

即時查詢（普通 SELECT）

上述查詢會在每次執行時重新計算，能保證結果即時正確，但如果要頻繁執行，就可能導致效能瓶頸。

建立 Materialized View

為了避免重複耗時計算，可以將結果快取下來，讓後續查詢速度大幅提升：

這樣一來，之後只需要：

就能快速取得結果，而不必每次都重新計算整個查詢。

Note: 適用於資料變動頻率不高，但查詢成本昂貴的情境。

更新 Materialized View

需要注意的是，Materialized View 本質上是一份快照。當底層資料（likes、posts、comments）更新後，這個快取結果不會自動更新。因此，我們必須手動告訴 PostgreSQL 重新整理：

執行上述指令後，Materialized View 的內容會重新計算並更新，確保查詢結果與最新資料同步。

參考資料

• SQL and PostgreSQL: The Complete Developer's Guide