跳到主要內容

DE Day 5 SQL 進階（三）：CTE 與遞迴查詢

DE Day 5 SQL 進階（三）：CTE 與遞迴查詢

執行需求：CPU 可跑。今天我們把 SQL 進階區塊的第三篇主題走完：CTE（Common Table Expression）與遞迴查詢。CTE 是把複雜的查詢改寫成可讀性更好的鏈式結構，讓大型查詢可以被分段、除錯、重用；遞迴 CTE 則是 SQL 處理「階層式資料」與「時間序列展開」的最強武器。本篇會把 Day 3 與 Day 4 的視窗函式查詢改寫成 CTE 形式，並用「員工組織圖」、「日曆維度」兩個實戰情境示範遞迴 CTE 的標準寫法。

引言

當一個 SQL 查詢長到 50 行以上，子查詢巢狀到三層以上時，閱讀與除錯就會變得很痛苦。CTE（Common Table Expression）是 SQL 標準（SQL:1999 起）的解法：用 WITH 子句把每段查詢命名並暫存在記憶體內，後續的查詢可以像引用資料表一樣重用它。這個寫法的好處是：第一，邏輯分段清楚；第二，可以重複引用；第三，遞迴 CTE 是處理樹狀與圖狀資料的唯一標準做法；第四，DuckDB、PostgreSQL、SQL Server、BigQuery、Snowflake 全部原生支援。

今天的目標有四個：第一，理解 WITH 子句的結構與多 CTE 鏈式寫法；第二，把 Day 3 與 Day 4 的視窗函式查詢改寫為 CTE 版本，看前後的差異；第三，學會 WITH RECURSIVE 的標準結構：種子查詢（base case）與遞迴查詢（recursive case）；第四，用員工組織圖、日曆維度兩個實戰情境，可整段跑的 Python + DuckDB 範例，建立你對遞迴 CTE 的信心。

CTE 的基本結構與鏈式寫法

CTE 的標準語法是 WITH <name> AS (<query>), <name2> AS (<query2>...)，之後 SELECT ... FROM <name> 引用。先用一個簡單的例子展示：

import duckdb

con = duckdb.connect()
result = con.execute("""
    WITH regional_sales AS (
        SELECT region,
               SUM(amount) AS total_amount,
               COUNT(*)    AS n_orders
        FROM (VALUES
            ('North', 1000),
            ('North', 1500),
            ('South', 2000),
            ('South',  500)
        ) AS t(region, amount)
        GROUP BY region
    )
    SELECT *,
           ROUND(total_amount * 100.0 / SUM(total_amount) OVER (), 2) AS share_pct
    FROM regional_sales
    ORDER BY total_amount DESC
""").fetchall()

for r in result:
    print(f"region={r[0]} total={r[1]} n={r[2]} share_pct={r[3]}")
# 輸出：region=South total=2500 n=2 share_pct=50.0
# 輸出：region=North total=2500 n=2 share_pct=50.0

這個查詢把 regional_sales 當成一個暫存的虛擬表：先做 GROUP BY 求出每區的合計與筆數，最後再對這個 CTE 做視窗函式的全體占比。WITH 後面的 regional_sales 就像是一張只活在這個查詢的表，可以被後續的 SELECT 引用任意次。

鏈式 CTE 的寫法是這樣：

result = con.execute("""
    WITH regional_sales AS (
        SELECT region, SUM(amount) AS total_amount
        FROM (VALUES
            ('North', 1000), ('North', 1500),
            ('South', 2000), ('South', 500)
        ) AS t(region, amount)
        GROUP BY region
    ),
    ranked AS (
        SELECT region, total_amount,
               ROW_NUMBER() OVER (ORDER BY total_amount DESC) AS rn
        FROM regional_sales
    )
    SELECT region, total_amount, rn
    FROM ranked
    WHERE rn <= 10
    ORDER BY rn
""").fetchall()

for r in result:
    print(f"region={r[0]} total={r[1]} rn={r[2]}")
# 輸出：region=South total=2500 rn=1
# 輸出：region=North total=2500 rn=2

這段展示兩個 CTE 接在一起：regional_sales 先算出每區合計；ranked 在它的基礎上排名；最後 SELECT 用 WHERE rn <= 10 取前 10 名。把這段與子查詢版本做對照，會發現 CTE 寫法可讀性明顯勝出。實務上，當查詢有三層以上邏輯時，幾乎都會採用 CTE 寫法。

把 Day 3 的視窗查詢改寫成 CTE

回頭看 Day 3 的視窗函式範例，它把 demo_orders 直接餵給視窗函式。當我們要把「使用者內 + 全體」的兩種彙總分開計算、再組合時，可以用兩個 CTE 表達：

import duckdb

con = duckdb.connect()
con.execute("""
    CREATE OR REPLACE TABLE demo_orders AS
    SELECT * FROM (VALUES
        ('u01', '2025-09-01', 1200), ('u01', '2025-10-01', 800), ('u01', '2025-11-01', 1500),
        ('u02', '2025-09-15', 600),  ('u02', '2025-10-20', 2200),('u02', '2025-11-05', 1100),
        ('u03', '2025-09-10', 4500), ('u03', '2025-10-12', 300), ('u03', '2025-11-18', 900),
        ('u04', '2025-09-22', 1900), ('u04', '2025-10-30', 2400),('u04', '2025-11-25', 1300)
    ) AS t(user_id, order_date, amount)
""")

result = con.execute("""
    WITH user_running AS (
        -- 第一層 CTE：每位使用者內累計
        SELECT user_id, order_date, amount,
               SUM(amount) OVER (
                   PARTITION BY user_id ORDER BY order_date
               ) AS running_total
        FROM demo_orders
    ),
    overall_agg AS (
        -- 第二層 CTE：全體總計（一次就算好）
        SELECT SUM(amount) AS grand_total FROM demo_orders
    )
    SELECT r.user_id, r.order_date, r.amount, r.running_total,
           o.grand_total,
           ROUND(r.amount * 100.0 / o.grand_total, 2) AS amount_share_pct
    FROM user_running r CROSS JOIN overall_agg o
    ORDER BY r.user_id, r.order_date
""").fetchall()

for r in result:
    print(f"user={r[0]} date={r[1]} amount={r[2]} running={r[3]} grand={r[4]} share={r[5]}")
# 輸出：user=u01 date=2025-09-01 amount=1200 running=1200 grand=19800 share=6.06
# 輸出：(其餘類似)

這個版本把 Day 3 的查詢拆成兩段：第一段 CTE 算每位使用者內的累計，第二段 CTE 算全體總計；最後把兩者 CROSS JOIN 結合起來。重點是 CROSS JOIN：因為 overall_agg 只有 1 列，CROSS JOIN 不會放大資料量，比 JOIN ... ON 1=1 更明確。當 grand_total 很大、重複算多次會慢時，把它獨立成 CTE 可以讓 DuckDB 的最佳化器把它當成「常數」處理，提速效果顯著。

這裡順手示範另一個簡單的鏈式 CTE：把一個來源表加上「類別排名」與「佔全體的比例」兩段邏輯。先把來源彙總，再排名，最後再算佔比，這三段拆開後閱讀起來比巢狀子查詢好讀三倍以上：

result = con.execute("""
    WITH regional_sales AS (
        SELECT region, SUM(amount) AS total_amount
        FROM (VALUES
            ('North', 1000), ('North', 1500),
            ('South', 2000), ('South', 500),
            ('East',  3000), ('East',  900)
        ) AS t(region, amount)
        GROUP BY region
    ),
    ranked AS (
        SELECT region, total_amount,
               RANK() OVER (ORDER BY total_amount DESC) AS rk
        FROM regional_sales
    )
    SELECT region, total_amount, rk,
           ROUND(total_amount * 100.0 / SUM(total_amount) OVER (), 2) AS share_pct
    FROM ranked
    ORDER BY rk
""").fetchall()

for r in result:
    print(f"region={r[0]} total={r[1]} rank={r[2]} share={r[3]}")
# 輸出：region=East total=3900 rank=1 share=42.86
# 輸出：region=South total=2500 rank=2 share=27.47
# 輸出：region=North total=2500 rank=2 share=27.47

這段我們用了三個運算：先 GROUP BY 算出每區合計，再用 RANK() 排名，最後用 SUM() OVER () 算全體佔比。rank=2 出現兩次（South 與 North 同分都用 2），這就是 RANK 的特性：同分同名，下一筆跳號。我們刻意把它放在 CTE 中，方便未來加欄位、加邏輯，例如「只取排名前 2」、「篩選佔比超過 30% 的區域」都只要在 SELECT 階段加 WHERE 就能做到。

用 CROSS JOIN UNNEST 做維度展開

另一個與 CTE 搭配好用的是 UNNEST 展開。當我們要把一個陣列展開成多列時，可以在 CTE 內先 UNNEST 再做後續邏輯。底下是模擬「每位使用者三個月都應該有訂單，但缺漏的月份怎麼補」的例子：

result = con.execute("""
    WITH months(m) AS (
        SELECT UNNEST(['2025-09-01', '2025-10-01', '2025-11-01'])::DATE
    ),
    users(u) AS (
        SELECT DISTINCT user_id FROM demo_orders
    ),
    cartesian AS (
        -- 完整笛卡兒積：所有使用者 × 所有月份
        SELECT u, m FROM users CROSS JOIN months
    ),
    filled AS (
        -- LEFT JOIN 真實訂單表，缺漏補 0
        SELECT c.u AS user_id,
               c.m AS order_date,
               COALESCE(o.amount, 0) AS amount
        FROM cartesian c
        LEFT JOIN demo_orders o
            ON c.u = o.user_id AND c.m = o.order_date
    )
    SELECT user_id, order_date, amount
    FROM filled
    WHERE user_id = 'u01'
    ORDER BY order_date
""").fetchall()

for r in result:
    print(f"user={r[0]} date={r[1]} amount={r[2]}")
# 輸出：user=u01 date=2025-09-01 amount=1200
# 輸出：user=u01 date=2025-10-01 amount=800
# 輸出：user=u01 date=2025-11-01 amount=1500

這段用三個 CTE 串成完整的「使用者 × 月份」笛卡兒積，加上 COALESCE() 把沒下單的月份補 0。這是把稀疏資料變成密集時間序列的標準寫法，在 Day 24 訂單分析維度建模時會大量重複使用。UNNEST 是把陣列展開成多列，DuckDB 與 PostgreSQL 都原生支援。注意第一步是把 '2025-09-01' 文字 cast 成 DATE，讓後續 JOIN 對齊欄位型別。

遞迴 CTE：員工組織圖

遞迴 CTE 是 CTE 家族裡最威力的成員。SQL 標準把它定義為「自己引用自己」，透過種子查詢（base case）與遞迴查詢（recursive case）兩個部分，實現「一階一階展開」的邏輯。我們用員工組織圖做第一個範例：

import duckdb

con = duckdb.connect()
con.execute("""
    CREATE OR REPLACE TABLE employees AS
    SELECT * FROM (VALUES
        (1, NULL, 'CEO 林總'),
        (2, 1,    '工程副總 John'),
        (3, 1,    '營運副總 Mary'),
        (4, 2,    '後端工程師 Allen'),
        (5, 2,    '前端工程師 Beth'),
        (6, 3,    '業務經理 Carl'),
        (7, 3,    '客服經理 Diana'),
        (8, 4,    '後端實習生 Eric')
    ) AS t(emp_id, manager_id, name)
""")

result = con.execute("""
    WITH RECURSIVE org_tree AS (
        -- 種子查詢：找出最上層（沒有直屬主管）
        SELECT emp_id, manager_id, name, 0 AS depth,
               CAST(name AS VARCHAR) AS path
        FROM employees WHERE manager_id IS NULL

        UNION ALL

        -- 遞迴查詢：把下一層（目前這層的下屬）找出來
        SELECT e.emp_id, e.manager_id, e.name, t.depth + 1 AS depth,
               t.path || ' > ' || e.name AS path
        FROM employees e
        JOIN org_tree t ON e.manager_id = t.emp_id
    )
    SELECT depth, path FROM org_tree ORDER BY path
""").fetchall()

for r in result:
    indent = '    ' * r[0]
    print(f"{indent}depth={r[0]} path={r[1]}")
# 輸出：depth=0 path=CEO 林總
# 輸出：    depth=1 path=CEO 林總 > 工程副總 John
# 輸出：        depth=2 path=CEO 林總 > 工程副總 John > 後端工程師 Allen
# 輸出：            depth=3 path=CEO 林總 > 工程副總 John > 後端工程師 Allen > 後端實習生 Eric
# 輸出：        depth=2 path=CEO 林總 > 工程副總 John > 前端工程師 Beth
# 輸出：    depth=1 path=CEO 林總 > 營運副總 Mary
# 輸出：        depth=2 path=CEO 林總 > 營運副總 Mary > 業務經理 Carl
# 輸出：        depth=2 path=CEO 林總 > 營運副總 Mary > 客服經理 Diana

這個範例把組織的 8 位員工由 CEO 一路展開。遞迴 CTE 的兩個部分各司其職：種子查詢（WHERE manager_id IS NULL）找到 CEO；遞迴查詢（JOIN org_tree t ON e.manager_id = t.emp_id）每次把目前這層員工的直屬下屬找出來，並把 depth + 1、path || ' > ' 累加。最終路徑看起來像 CEO 林總 > 工程副總 John > 後端工程師 Allen > 後端實習生 Eric，可以直接餵給前端做組織圖元件。

這份 DuckDB 的語法與 PostgreSQL 完全相同；SQLite 在 3.8.3 以後也支援。MySQL 8 支援 WITH RECURSIVE 但語法略有差異（UNION ALL 後不可放 UNION DISTINCT），跨平台部署時要記得確認。

遞迴 CTE 在 ETL 的另一個常見用途是「資料沿革（data lineage）」：從某個彙總指標往上追到原始事實表，列出每一段聚合過程。底下這段示範把三段日彙總往上回溯：

result = con.execute("""
    WITH RECURSIVE lineage AS (
        SELECT 3 AS step,
               'daily_summary' AS table_name,
               12 AS row_count,
               CAST('demo_orders' AS VARCHAR) AS source_table
        UNION ALL
        SELECT step - 1,
               CASE step - 1
                   WHEN 2 THEN 'hourly_summary'
                   WHEN 1 THEN 'event_log'
                   ELSE 'raw_events'
               END,
               row_count * 10,
               source_table
        FROM lineage
        WHERE step > 1
    )
    SELECT step, table_name, row_count, source_table
    FROM lineage
    ORDER BY step DESC
""").fetchall()

for r in result:
    print(f"step={r[0]} table={r[1]} rows={r[2]} source={r[3]}")
# 輸出：step=3 table=daily_summary rows=12 source=demo_orders
# 輸出：step=2 table=hourly_summary rows=120 source=demo_orders
# 輸出：step=1 table=event_log rows=1200 source=demo_orders
# 輸出：step=0 table=raw_events rows=12000 source=demo_orders

這段是個示範用的歸納範例：把 daily → hourly → event → raw 的資料沿革列成四層，step 由 3 遞迴到 0，row_count 也逐層放大十倍。實務上，這個模式用於「資料追蹤」、「轉換管線視覺化」、「管線文件化」。DuckDB 把每段視為一次性查詢，效能足夠處理數百段的複雜管線。把這段寫成 queries/lineage.sql 是一個好做法，未來對 doc 化有幫助。

遞迴 CTE：日曆維度（time series spine）

另一個常見的遞迴用法是「時間序列脊椎（time series spine）」：從某個起始日遞迴到結束日，每天一列。這在做「依日期彙總」、「缺漏日期補 0」、「移動平均對齊」時非常方便。我們示範從 2025-09-01 展開到 2025-11-30 共 91 天的日曆：

result = con.execute("""
    WITH RECURSIVE calendar AS (
        -- 種子查詢：起始日
        SELECT DATE '2025-09-01' AS d

        UNION ALL

        -- 遞迴查詢：每次加 1 天，直到結束日
        SELECT d + INTERVAL '1 day'
        FROM calendar
        WHERE d < DATE '2025-11-30'
    )
    SELECT d,
           DATE_TRUNC('month', d) AS month,
           EXTRACT(DAY FROM d) AS day_of_month,
           EXTRACT(DOW FROM d) AS dow_iso
    FROM calendar
    ORDER BY d
    LIMIT 10
""").fetchall()

for r in result:
    print(f"date={r[0]} month={r[1]} day={r[2]} dow={r[3]}")
# 輸出：date=2025-09-01 month=2025-09-01 day=1 dow=1
# 輸出：date=2025-09-02 month=2025-09-01 day=2 dow=2
# 輸出：date=2025-09-03 month=2025-09-01 day=3 dow=3
# 輸出：(其餘類似)

這段從起始日 '2025-09-01' 遞迴加 1 天，到 '2025-11-30' 為止，最後 SELECT 限縮到前 10 筆展示。完整 91 天的結果可以用 SELECT * FROM calendar WHERE d BETWEEN '2025-09-15' AND '2025-09-30' 取出，做「連續日期的瀏覽數」、「缺漏日期補 0」這類報表。在 DuckDB 1.4 中，遞迴 CTE 雖然比顯式列舉 91 列來得冗長，但比維護一份外部 CSV 日曆更安全、更容易隨查詢動態調整。

完整實作：訂單彙總搭配日曆維度

把日曆遞迴維度與實際訂單 LEFT JOIN，我們可以做出每一天的銷售彙總報表，即使當天沒訂單也會顯示出來：

result = con.execute("""
    WITH RECURSIVE calendar AS (
        SELECT DATE '2025-09-01' AS d
        UNION ALL
        SELECT d + INTERVAL '1 day' FROM calendar
        WHERE d < DATE '2025-11-30'
    ),
    daily_sales AS (
        SELECT order_date, SUM(amount) AS day_total
        FROM demo_orders
        GROUP BY order_date
    )
    SELECT c.d AS order_date,
           COALESCE(d.day_total, 0) AS day_total
    FROM calendar c
    LEFT JOIN daily_sales d ON c.d = d.order_date
    WHERE c.d BETWEEN DATE '2025-11-01' AND DATE '2025-11-10'
    ORDER BY c.d
""").fetchall()

for r in result:
    print(f"date={r[0]} total={r[1]}")
# 輸出：date=2025-11-01 total=1500
# 輸出：date=2025-11-02 total=0
# 輸出：date=2025-11-03 total=0
# 輸出：date=2025-11-04 total=0
# 輸出：date=2025-11-05 total=1100
# 輸出：date=2025-11-06 total=0
# 輸出：date=2025-11-07 total=0
# 輸出：date=2025-11-08 total=0
# 輸出：date=2025-11-09 total=0
# 輸出：date=2025-11-10 total=0

可以看到 11 月 1 日有 1500 元（u01），11 月 5 日有 1100 元（u02），其餘日期都是 0。這就是「密集時間序列」的寫法：先建一份完整日曆（用遞迴），再用 LEFT JOIN 餵入真實銷售，最後 COALESCE() 把缺漏補 0。這個模式在儀表板、缺漏檢測、定期報表都會用到。Day 24 訂單分析的維度建模會大量應用這個寫法。

常見錯誤與踩雷

第一個雷：遞迴 CTE 沒有寫 UNION ALL 終止條件，導致無限遞迴。SQL 標準要求遞迴 CTE 的遞迴查詢「在某個條件下不再產生新列」，否則引擎會視為錯誤。請務必在 UNION ALL 之後的下層查詢加上 WHERE depth < 10 或日期邊界條件。在 DuckDB、PostgreSQL、Snowflake 都有「最大遞迴深度」的設定，預設通常是 100 或 1000；越界的查詢會被中止並報錯。

第二個雷：CTE 名稱與資料表名稱重複。當 CTE 名稱（如 regional_sales）與現有資料表同名時，DuckDB 會以 CTE 為主，但仍建議把 CTE 命名為獨特字首（例如 cte_regional_sales），避免歧義。

第三個雷：CTE 與子查詢差異沒搞清楚。CTE 在大多數情境下與子查詢等價，但有一個關鍵差異：CTE 在同一個 SELECT 中可以被引用任意次，子查詢不行；CTE 也更容易被資料庫引擎「物化（materialize）」快取。當同一個子查詢被引用兩次以上時，改用 CTE 寫法通常有明顯的效能優勢。

第四個雷：UNNEST 在不同資料庫語法略有差異。DuckDB 接受 UNNEST(['a','b'])，PostgreSQL 也接受，但需要 SELECT * FROM UNNEST(...) 結構。SQLite 在 3.38 起有 json_each 達到類似效果，但語法不同。請依目標平台調整。

把日曆遞迴查詢擴充一個小功能：列出每個月的月初、月底、月份中的日數。這是維度建模中常見的「時間維度屬性」需求：

result = con.execute("""
    WITH RECURSIVE calendar AS (
        SELECT DATE '2025-09-01' AS d
        UNION ALL
        SELECT d + INTERVAL '1 day' FROM calendar
        WHERE d < DATE '2025-09-30'
    )
    SELECT
        DATE_TRUNC('month', d) AS month_start,
        MIN(d) OVER (PARTITION BY DATE_TRUNC('month', d)) AS first_day,
        MAX(d) OVER (PARTITION BY DATE_TRUNC('month', d)) AS last_day,
        d
    FROM calendar
    ORDER BY d
    LIMIT 5
""").fetchall()

for r in result:
    print(f"d={r[3]} month_start={r[0]} first={r[1]} last={r[2]}")
# 輸出：d=2025-09-01 month_start=2025-09-01 first=2025-09-01 last=2025-09-30
# 輸出：d=2025-09-02 month_start=2025-09-01 first=2025-09-01 last=2025-09-30
# 輸出：d=2025-09-03 month_start=2025-09-01 first=2025-09-01 last=2025-09-30
# 輸出：d=2025-09-04 month_start=2025-09-01 first=2025-09-01 last=2025-09-30
# 輸出：d=2025-09-05 month_start=2025-09-01 first=2025-09-01 last=2025-09-30

這段我們用了遞迴 CTE + 視窗函式：先用遞迴建立 9 月份每一天，再用 MIN/MAX OVER (PARTITION BY month) 把每個月的第一天、最後一天抓出來。這就是把兩個進階 SQL 觀念合在一起：用遞迴產生時間維度，用視窗函式補上聚合屬性，這是 Day 23 維度建模的核心模式之一。

效能與實務提醒

CTE 在 SQL 標準裡並沒有強制要求「物化」，但實務上大多數引擎（包括 DuckDB）會視情況選擇物化或直接內嵌。一個常見最佳實務是：若某個 CTE 在查詢中被引用 3 次以上，把它獨立實體化（CREATE TABLE ... AS）通常能加速；單次引用則對效能影響不大。

遞迴 CTE 的效能很大程度決定於「展開的列數」。當組織圖有上千節點、或日曆跨度一年 365 天時，DuckDB 的遞迴查詢仍是線性掃描，效能可控；但若要展開到百萬列，建議改用「應用程式層迴圈 + 寫入暫存表」。把這個「百萬列遞迴」當成 ETL 反模式，避免在生產環境直接用。Day 35 的效能與成本章節會再次回頭檢視。

另一個提醒：CROSS JOIN UNNEST 是 DuckDB 與 PostgreSQL 對維度展開的標準組合技。當你需要把陣列型態或多對多的關係展開時，這個寫法比傳統的 LATERAL JOIN 更直觀；但要記得最後用 COALESCE() 處理沒有對應事實表的列，否則 JOIN 內側會被吃掉。

小結

今天我們把 CTE 從基礎到遞迴一次走完：先用員工組織圖、日曆兩個遞迴 CTE 範例建立基本語感；接著把 Day 3 的視窗函式查詢用兩個 CTE 改寫，讓結構更清楚；再用日曆 + 訂單 LEFT JOIN 展示「稀疏變密集」的時間序列脊柱。最後用 CROSS JOIN UNNEST 示範「使用者 × 月份」的笛卡兒積。CTE 是把複雜查詢拆段、遞迴是解決樹狀資料與時間維度展開的工具，兩個觀念一起掌握，可以應付大部分的 SQL 進階需求。

把今天學到的關鍵詞抄進筆記本：WITH、WITH RECURSIVE、種子查詢、遞迴查詢、UNION ALL、UNNEST、COALESCE()、time series spine、組織圖遞迴。明天會進入「聯結策略與常見陷阱」，把 JOIN 的 INNER、LEFT、RIGHT、FULL、CROSS 一次釐清，並把多對多、自連接、半連接（SEMI JOIN / ANTI JOIN）做一番對照。

最後一個提醒：CTE 中可以再巢狀 CTE。例如先把 sales_region 用一個 CTE 算出來，再把 region 排名放在第二個 CTE，最後從這兩個 CTE 中選擇需要的欄位組合。這樣一層一層組合起來，可以把一個原本很長的查詢拆成可讀性高的鏈式結構，特別適合放在版本控制上做差異比對。另外一個值得記住的小細節是：DuckDB 在編譯 CTE 時通常會做 CTE 物化最佳化，這對鏈式查詢效能有利，跨平台時請先確認目標引擎是否也做同樣最佳化。明天開始的 JOIN 主題會大量沿用這個做法。

結語

CTE 與遞迴 CTE 是把 SQL 從「查詢語言」升級為「建模語言」的關鍵。寫過幾次就會發現：「原來這個查詢可以這樣結構化」，而不必再寫那種「子查詢包三層」的難讀 SQL。本系列會在 Day 23 的維度建模、Day 24 的訂單分析、Day 25-26 的 dbt 模型反覆用到 CTE 觀念；遞迴 CTE 則會在 Day 27 的 Airflow DAG 設計與 Day 41 的專案資料模型被當成基礎工具使用。請把今天可整段執行的範例保留在工作目錄裡，未來改寫或擴充時就有依據。

明天，我們會進入 SQL 進階區塊的最後兩篇之一：「聯結策略與常見陷阱」。我們會把 INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL OUTER JOIN、CROSS JOIN 做一輪對照，用三個實務情境（客戶與訂單、商品與分類、員工與組織）展示什麼時候用哪種聯結，並把多對多、自連接、半連接（SEMI JOIN / ANTI JOIN）這幾個進階主題展開。DuckDB、PostgreSQL、Trino 等主流引擎對聯結各有最佳實務，例如 DuckDB 偏好 hash join，PostgreSQL 會根據統計資訊自動選擇；在跨平台部署時請先確認目標引擎的偏好，這也是為什麼 Day 7 的 EXPLAIN 主題那麼重要：先看執行計畫才知道資料庫實際選了哪種聯結策略。

延伸資源

  • DuckDB CTE 與遞迴查詢範例：https://duckdb.org/docs/sql/recursive_ctes.html。本篇所用 WITH RECURSIVE 與 UNNEST 語法以這份為主。
  • PostgreSQL 18 遞迴查詢教學：https://www.postgresql.org/docs/current/queries-with.html。組織圖遞迴的權威來源。
  • 《SQL Antipatterns》（書籍）：Bill Karwin 著，深入討論 CTE 與子查詢的正確使用方式。
  • Modern SQL 部落格：https://modern-sql.com/。含豐富的 CTE 與遞迴案例。
  • DuckDB CLI 線上手冊：duckdb --help。命令列可用 duckdb -c "WITH RECURSIVE ..." 一次執行。
標籤：

留言

張貼留言

這個網誌中的熱門文章

Day 2 變數與資料型別

Day 2 變數與資料型別 引言 寫程式的過程中，變數與資料型別是處理資料的基礎。變數是存放資料的容器，資料型別則決定這筆資料有哪些特性、可以進行哪些操作。學會定義變數、認識各種資料型別，是學好 Python 的關鍵一步。 這篇文章會帶你了解 Python 中變數的觀念、如何定義變數，以及常見的資料型別，包括整數、浮點數、字串、布林值，還有串列、元組、字典與集合等容器型別。我們也會介紹變數的命名規則與撰寫風格建議，以及如何用 type() 檢查資料型別。 什麼是變數？如何在 Python 中定義變數 變數是在程式執行時用來存放資料的名稱。透過定義變數，我們可以給一筆資料一個名字，並在程式的其他地方用這個名字取用該筆資料。在 Python 中，變數不需要事先宣告型別，因為 Python 是動態型別語言，變數的型別由指定給它的值決定。 定義變數的基本語法 在 Python 中定義變數非常簡單，只要用賦值符號 = 把值指定給變數即可。例如： x = 5 # 定義變數 x，並把整數 5 賦值給它 name = "Alice" # 定義變數 name，並把字串 "Alice" 賦值給它 在這裡，x 是一個變數，被賦予整數 5；name 是另一個變數，被賦予字串 "Alice"。 變數的更新與覆寫 變數的值可以修改，也就是說，我們可以在程式的不同地方給同一個變數新的值。例如： x = 10 # x 最初被賦予 10 x = 15 # x 的值現在被更新為 15 這樣就能依照需求，在程式執行過程中靈活調整變數的值。 Python 的動態型別系統 Python 和某些靜態型別語言不同，定義變數時不需要宣告型別。賦值時，Python 會根據值自動判斷變數的型別。例如： x = 5 # x 是整數 x = 3.14 # x 變成浮點數 x = "Hi" # x 變成字串 同一個變數在程式執行過程中可以存放不同型別的值，這是 Python 的彈性之一。 常見資料型別 在 Python 中，資料型別決定我們可以對變數進行哪些操作...
張貼留言
閱讀完整內容

Day 1 Python 簡介與環境設定

Day 1 Python 簡介與環境設定 引言 在現在的科技環境裡，程式設計已經是一項重要技能。無論你是對資料科學有興趣、想成為開發者，或是想踏入人工智慧（AI）領域，學會寫程式都能明顯提升你的競爭力。在眾多程式語言中，Python 因為語法簡單、功能強大、應用範圍廣泛，成為許多人進入程式世界的第一選擇。這篇文章會帶你認識 Python 的背景與優勢，並一步步教你在不同系統上安裝與設定 Python 開發環境，最後寫出第一支 Python 程式。 為什麼選擇 Python？ Python 是一種高階程式語言，由 Guido van Rossum 在 1991 年發布。Python 的設計哲學強調程式碼的可讀性，並用縮排來定義程式區塊，這點和許多使用大括號的語言不同。簡潔的語法讓它成為初學者的理想選擇；就算是經驗豐富的開發者，也能用它完成複雜的專案。 Python 的優勢如下： 簡單易學 ：Python 的語法清楚、結構簡潔，初學者很快就能上手。和其他語言相比，學習曲線相對平緩，不需要先弄懂一堆複雜觀念，就能開始寫程式。 應用範圍廣泛 ：從資料科學、網頁開發、人工智慧、機器學習、自動化測試到網路爬蟲，Python 都有大量開源函式庫與工具支援，而且在這些領域都扮演關鍵角色。 豐富的函式庫與框架 ：Python 的函式庫生態系非常龐大。做資料分析有 NumPy、Pandas；開發網站有 Django、Flask；做深度學習有 TensorFlow、PyTorch。各種需求幾乎都能找到對應的套件，讓開發更有效率。 跨平台支援 ：Python 支援 Windows、macOS、Linux 等作業系統，程式通常不需要太多修改就能跨平台執行，讓開發與部署更有彈性。 活躍的社群 ：Python 擁有龐大的開發者社群。學習或開發上遇到問題，幾乎都能在社群與論壇（例如 Stack Overflow）找到答案，對初學者來說是很強的後盾，也能減少卡關時的挫折感。 Python 的應用領域 Python 的流行與強大功能，讓許多領域都開始大量使用它。以下是幾個常見的應用方向： 資料科學 ：隨著大數據與人工智慧興起，資料科學大量使用 Python。NumPy、Pandas 與 Matplotlib 等工具能處理和分析龐...
張貼留言
閱讀完整內容

Python 從入門到 PyTorch 深度學習：開啟 AI 世界的大門

Python 從入門到 PyTorch 深度學習：開啟 AI 世界的大門 隨著人工智慧（AI）與深度學習（Deep Learning）快速發展，越來越多人對這些技術產生興趣。不論你是想踏入 AI 領域的初學者，還是已經有程式基礎的開發者，學好 Python 與深度學習框架（例如 PyTorch），都能為你打開更多可能。 為什麼選擇 Python？ Python 已經是資料科學與人工智慧領域的首選語言。它的語法簡潔、容易上手，而且擁有龐大的生態系與大量開源函式庫。無論是資料處理、資料視覺化，還是建立機器學習與深度學習模型，Python 都能勝任。對想進入 AI 或資料科學領域的人來說，它幾乎是必備工具。 PyTorch 是什麼？ PyTorch 是由 Meta（原 Facebook）AI 研究團隊開發的開源深度學習框架，以易用、靈活和動態計算圖著稱，是許多 AI 研究人員與開發者的首選。相較於其他框架，PyTorch 的寫法更貼近原生 Python，對初學者相對友善。無論是簡單的實驗，還是複雜的深度學習模型，PyTorch 都能提供強大的支援。 這個系列能帶給你什麼？ 這個系列會從 Python 的基礎開始，帶你一步一步學習，最後能自己用 PyTorch 建立深度學習模型。即使你完全沒有寫過程式，也能跟著文章的節奏累積技能，理解 AI 與深度學習的核心觀念。 本系列涵蓋的主題 Python 基礎：從變數、條件判斷到函式與模組。 資料處理工具：用 NumPy 與 Pandas 有效率地操作資料。 資料視覺化：用 Matplotlib 與 Seaborn 把資料畫成圖表。 深度學習的數學基礎：線性代數、微積分與機率。 PyTorch 入門：理解張量、模型建構與 GPU 加速。 基礎深度學習模型：CNN 與 RNN 的實作應用。 深度學習專案實戰：從資料前處理到模型部署的端到端流程。 誰適合這個系列？ 程式初學者 ：如果你對 AI 充滿好奇，卻還沒寫過程式，系列的第一部分會帶你快速上手 Python，並幫助你理解深度學習的基本觀念。 資料科學愛好者 ：如果你已經熟悉一些資料處理方法，進階部分會教你如何用 PyTorch 建構深度學習模型。 開發者與研究人員 ：想更深入了...
張貼留言
閱讀完整內容