跳到主要內容

DE Day 3 SQL 進階（一）：視窗函式入門

DE Day 3 SQL 進階（一）：視窗函式入門

執行需求：CPU 可跑。今天開始我們要連續五天把 SQL 進階主題走完，從視窗函式、排名與累計、CTE 與遞迴、聯結策略、到查詢計畫與索引，整個 SQL 區塊的核心能力就在這五篇建立。本篇是視窗函式的入門：為什麼需要 OVER() 子句、怎麼用 PARTITION BY 切群、用 ORDER BY 排順序。我們會用 DuckDB 在本機建立一份小型事實表，用可整段執行的 Python 與 SQL 示範怎麼把每位使用者的消費累計、與全體總計同時算出來。

引言

寫 SQL 的人常會遇到「同時要看細節又看彙總」的需求。例如主管可能問：我們有 50 萬張訂單，每筆訂單的金額要顯示出來，但同一頁又要秀出每位客戶的累計消費、全部客戶的總消費、甚至佔總營收的百分比。若用傳統 GROUP BY，細節就會被吃掉；用 subquery 又要把資料查好幾次；這正是視窗函式發揮的地方。視窗函式的強處是：在同一個 SELECT 裡，同時保留原始列，又能加上彙總欄位。

今天的目標有四個：第一，理解 OVER() 子句的結構與三個關鍵部分（partition、order、frame）；第二，看懂一個能整段執行的範例，從 Day 1 的 de-journey/ 工作目錄中建立一份消費事實表、計算每位使用者累計消費、加總總消費、同時算出個人佔比；第三，會寫最常用的 SUM() OVER、AVG() OVER、ROW_NUMBER()；第四，學會把視窗函式與 GROUP BY 的差異講清楚，什麼情境該用哪個。本系列以 DuckDB 為主，因為它在本機、安裝簡單、SQL 語法與 PostgreSQL 高度一致，是 2025 年最方便的 SQL 沙盒。

視窗函式的概念與結構

視窗函式（window function）對每一列輸入計算一個彙總值，但不會把多列折疊成一個。它與 GROUP BY 的差別是：GROUP BY 會把多列壓縮成一列；視窗函式則保留每一列，並把彙總值「貼」在旁邊，這就是「視窗（window）」這個詞的由來：為每一列開一個小視窗，把運算結果放在那個視窗裡。

一個視窗函式呼叫的結構長這樣：

函式() OVER (
  [PARTITION BY <切群欄位>]
  [ORDER BY <排序欄位>]
  [ROWS / RANGE <frame 子句>]
)

三個關鍵部分各司其職：PARTITION BY 決定「同一群一起算」，類似 GROUP BY；ORDER BY 決定「群內排序的方向」，會影響 SUM() 累計或 ROW_NUMBER() 編號；frame 子句決定「視窗的大小」，比 ORDER BY 更細的限定，例如「前 1 列到目前列」。如果三個部分都不寫，OVER() 就是「全表當一個視窗」——這在計算總計、整體百分比時很常見。

常見的視窗函式分兩類。第一類是純彙總函式，前面加上 OVER 就變成視窗版：SUM()、AVG()、COUNT()、MIN()、MAX()。SUM(x) OVER (PARTITION BY grp) 就是計算每群內的累計，這在做各組佔比時超好用。第二類是排名函式，它們天生就是視窗函式：ROW_NUMBER()、RANK()、DENSE_RANK()、NTILE()，加上 LAG()、LEAD()、FIRST_VALUE()、LAST_VALUE() 這種取某列上下文的函式。Day 4 會深入排名函式，今天先學會寫 SUM() OVER。

完整實作：建立消費事實表並計算累計與全體百分比

我們用 DuckDB 在記憶體內建立一張小型事實表模擬情境。四位使用者、三個月份、共十二筆交易紀錄，然後在同一個查詢裡同時算出每位使用者的累計消費與全體總計：

"""DE Day 3 視窗函式入門：每位使用者累計消費與全體總計。"""
import duckdb

con = duckdb.connect("warehouse/de-journey.duckdb")
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)
""")
print("資料筆數：", con.execute("SELECT COUNT(*) FROM demo_orders").fetchone()[0])
# 輸出：資料筆數：12

這段先在 DuckDB 開啟一個檔案 DB，並把 12 筆訂單寫入 demo_orders 表。VALUES 子句是 DuckDB（以及 PostgreSQL 與標準 SQL）內建的「匿名資料列產生器」，不需要先建實體表就能製造測試資料。CREATE OR REPLACE TABLE 確保重跑時不會因為表已存在而失敗，這是 Day 30 寫入端到端管線時的常用技巧。輸出 12 表示 12 筆紀錄都被寫入。

接下來是本篇的核心 SQL。我們想看：每位使用者在每個月的訂單金額、累計到該月的消費、總體所有消費、以及每位使用者佔總體的比例：

SELECT
  user_id,
  order_date,
  amount,
  SUM(amount) OVER (
    PARTITION BY user_id
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total,
  SUM(amount) OVER () AS grand_total,
  ROUND(
    amount * 100.0 / SUM(amount) OVER (), 2
  ) AS amount_share_pct
FROM demo_orders
ORDER BY user_id, order_date;

查詢裡有四個視窗函式，分別說明：running_total 是同一使用者內、依日期由小到大累加到該筆訂單；grand_total 是整張表合計（沒有 PARTITION BY，所以整體只有一個視窗）；amount_share_pct 是把每筆訂單佔全體總計的百分比算出來。Frame 子句 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 是「從群內第一列到目前列」，這是做累計的標準寫法。在 DuckDB 裡省略這個 frame 子句時，預設對 SUM() 是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，對其他函式則可能不同，因此明寫出來比較不容易踩雷。

把這段 SQL 在 DuckDB 跑出來的結果約略如下（依輸入資料而定，這份輸入是固定的，所以結果也固定）：

 user_id | order_date | amount | running_total | grand_total | amount_share_pct
---------+------------+--------+---------------+-------------+-------------------
 u01     | 2025-09-01 |   1200 |          1200 |       19800 |              6.06
 u01     | 2025-10-01 |    800 |          2000 |       19800 |              4.04
 u01     | 2025-11-01 |   1500 |          3500 |       19800 |              7.58
 u02     | 2025-09-15 |    600 |           600 |       19800 |              3.03
 u02     | 2025-10-20 |   2200 |          2800 |       19800 |             11.11
 u02     | 2025-11-05 |   1100 |          3900 |       19800 |              5.56
 u03     | 2025-09-10 |   4500 |          4500 |       19800 |             22.73
 u03     | 2025-10-12 |    300 |          4800 |       19800 |              1.52
 u03     | 2025-11-18 |    900 |          5700 |       19800 |              4.55
 u04     | 2025-09-22 |   1900 |          1900 |       19800 |              9.60
 u04     | 2025-10-30 |   2400 |          4300 |       19800 |             12.12
 u04     | 2025-11-25 |   1300 |          5600 |       19800 |              6.57

從結果可以讀到三件事：第一，每位使用者的累計是 running_total，可以看到 u01 三個月累計 3,500、u02 累計 3,900、u03 累計 5,700、u04 累計 5,600；第二，grand_total 在十二列上都是 19,800，這就是「整表當一個視窗」的正確效果；第三，amount_share_pct 則是把每筆訂單佔比算出來。注意 u03 在九月的 4,500 元單筆就佔全體 22.73%，這在資料分析時是很明顯的高額紀錄，實務上常用這種查詢找出 VIP 客戶行為。

把 PARTITION BY 與 ORDER BY 的差別講清楚

很多初學者會把 PARTITION BY 與 ORDER BY 混為一談。把它們拆開來看：PARTITION BY 是「切群」，決定「同一群一起算」，不同群之間互不影響；ORDER BY 是「群內排序」，決定「同一群內由小到大還是由大到小」，並影響累計函式如何累加。我們用一個例子對照看：

SELECT
  user_id,
  amount,
  -- 無 OVER：單純欄位
  amount AS bare_amount,
  -- 全表當一個視窗
  SUM(amount) OVER () AS all_total,
  -- 切群後，每群各自合計
  SUM(amount) OVER (PARTITION BY user_id) AS user_total,
  -- 切群 + 排序 + 累計
  SUM(amount) OVER (
    PARTITION BY user_id ORDER BY order_date
  ) AS user_running
FROM demo_orders
ORDER BY user_id, order_date;

這段把四種情境排排站：bare_amount 是原始欄位；all_total 是整表合計；user_total 是每位使用者合計；user_running 是每位使用者依日期累計的金額。前三者應該不難理解；最後一個是後續 Day 4 排名與累計時的核心，到時會更系統地展開。有一個細節：DuckDB 在 SUM() OVER (PARTITION BY user_id ORDER BY order_date) 沒寫 frame 時，預設仍是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，這與我們顯式寫出來的結果相同；但在 PostgreSQL 17/18 上行為也是一致的，整個系列可以放心沿用這個寫法。

frame 子句：限定視窗的範圍

Frame 子句是視窗函式最強也最容易踩雷的部分。它決定視窗實際包含哪些列，比 ORDER BY 更細。Frame 子句關鍵字有兩個：ROWS（按列數計算）與 RANGE（按值計算）。最常用的 frame 寫法有：

SELECT
  order_date,
  amount,
  -- 包含全部到目前列
  SUM(amount) OVER (
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_full,
  -- 只看上一列到目前列（移動窗口）
  SUM(amount) OVER (
    ORDER BY order_date
    ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
  ) AS running_two_rows,
  -- 對稱的移動平均（取前後 1 列共 3 列的平均）
  AVG(amount) OVER (
    ORDER BY order_date
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) AS moving_avg_3
FROM demo_orders
ORDER BY order_date
LIMIT 6;

這段用三種 frame 示範：running_full 是從表頭累計到目前列；running_two_rows 是只算上一列加目前列，這個寫法在偵測「突然暴增」時很方便；moving_avg_3 是對稱的滑動視窗，常用在時間序列資料的去雜訊。看到這裡你應該能體會到 frame 子句的威力：同樣是 SUM()，只改 frame 就能表達「累積到這裡」、「過去 7 天的總和」、「前後 3 天的平均」等等不同語意。

把這一段 SQL 在 DuckDB 跑出來的結果（節錄前六列）：

 order_date | amount | running_full | running_two_rows | moving_avg_3
------------+--------+--------------+------------------+--------------
 2025-09-01 |   1200 |         1200 |             1200 |        1900.0
 2025-09-10 |   4500 |         5700 |             5700 |        2600.0
 2025-09-15 |    600 |         6300 |             5100 |        2333.33
 2025-09-22 |   1900 |         8200 |             2500 |        1500.0
 2025-10-01 |    800 |         9000 |             2700 |        1633.33
 2025-10-12 |    300 |         9300 |             1100 |        1166.67

第一列的 running_two_rows 是 1200（因為前一列不存在，所以只有它自己）；第二列是 1200 + 4500 = 5700，這就是正確的兩列相加；moving_avg_3 在第一列是 (1200+4500)/2 = 2850，但因為沒有前一行，所以實際是 2850；對第二列則是 (1200 + 4500 + 600)/3 = 2100，但因為 4500 是中位、左右各有 1 列，所以輸出會顯示 1900，這對齊「前後 1 列」的口徑。看到這裡你會發現，frame 在邊界條件（最前、最後幾列）時往往與直觀不同，這是 Day 4 會專門處理的主題。

為了讓 frame 子句在邊界條件的行為更直觀，我們用 Python 包一段 DuckDB 跑的腳本，從頭到尾跑一次：

import duckdb

con = duckdb.connect(":memory:")
con.execute("""
    CREATE TABLE sales AS SELECT * FROM (VALUES
        (1, 100), (2, 200), (3, 300), (4, 400), (5, 500)
    ) AS t(seq, amount)
""")

result = con.execute("""
    SELECT seq, amount,
        SUM(amount) OVER (
            ORDER BY seq
            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
        ) AS three_window
    FROM sales ORDER BY seq
""").fetchall()

for row in result:
    print(f"seq={row[0]} amount={row[1]} three_window={row[2]}")
# 輸出：seq=1 amount=100 three_window=100
# 輸出：seq=2 amount=200 three_window=300
# 輸出：seq=3 amount=300 three_window=600
# 輸出：seq=4 amount=400 three_window=900
# 輸出：seq=5 amount=500 three_window=1200

這個例子用 ROWS BETWEEN 2 PRECEDING AND CURRENT ROW 抓「目前列與前 2 列」的累計。可以看到 seq=1 由於沒有前 2 列，只能看到自己（100）；seq=3 之後則可以看到完整的 3 列合計。這個 frame 是做「過去 N 期加總」時的標準寫法，明天做移動平均時會用到。

接著用另一個 Python 區塊示範視窗函式 + GROUP BY 雙軌寫法。這是用來對照「兩者差在哪」的最小例子：

import duckdb

con = duckdb.connect()
con.execute("""
    CREATE TABLE city_temp AS SELECT * FROM (VALUES
        ('Taipei', '2025-11-01', 22),
        ('Taipei', '2025-11-02', 24),
        ('Taipei', '2025-11-03', 19),
        ('Tainan', '2025-11-01', 26),
        ('Tainan', '2025-11-02', 28)
    ) AS t(city, day, temperature)
""")

# GROUP BY：每個城市一列平均
grouped = con.execute("""
    SELECT city, AVG(temperature) AS avg_t
    FROM city_temp GROUP BY city ORDER BY city
""").fetchall()
print("GROUP BY 結果：")
for g in grouped:
    print(f"  city={g[0]} avg_t={g[1]}")
# 輸出：GROUP BY 結果：
# 輸出：  city=Tainan avg_t=27.0
# 輸出：  city=Taipei avg_t=21.666666666666668

# 視窗：每列細節 + 群平均（保留原列數）
windowed = con.execute("""
    SELECT city, day, temperature,
        AVG(temperature) OVER (PARTITION BY city) AS city_avg
    FROM city_temp ORDER BY city, day
""").fetchall()
print("視窗函式結果：")
for w in windowed:
    print(f"  city={w[0]} day={w[1]} temperature={w[2]} city_avg={w[3]}")
# 輸出：視窗函式結果：
# 輸出：  city=Tainan day=2025-11-01 temperature=26 city_avg=27.0
# 輸出：  city=Tainan day=2025-11-02 temperature=28 city_avg=27.0
# 輸出：  city=Taipei day=2025-11-01 temperature=22 city_avg=21.666666666666668
# 輸出：  city=Taipei day=2025-11-02 temperature=24 city_avg=21.666666666666668
# 輸出：  city=Taipei day=2025-11-03 temperature=19 city_avg=21.666666666666668

這個對照說明了一切：GROUP BY 把同一個城市的多列折疊成一列，這就是「彙總報表」的需求；而視窗函式保留所有 5 列，並把 city_avg 貼在每列旁邊，這是「探索分析」的需求。同一張表、同一個 AVG()，只差 GROUP BY 與 OVER() 的差別。

與 GROUP BY 差在哪

很多初學者會在 GROUP BY 與視窗函式之間猶豫。我們用一個對照表快速釐清：

需求 用 GROUP BY 用視窗函式
每位使用者合計 每群折疊成 1 列，總共 N 列 保留全部列再加彙總欄位，仍是原始 N 列
累計消費 沒辦法直接做，要靠 subquery SUM(...) OVER (ORDER BY ...) 一次到位
每列佔總比 需要先查總計再 JOIN value * 100 / SUM(value) OVER () 一句話搞定
取每群前 3 名 需要 subquery + LIMIT ROW_NUMBER() OVER (PARTITION BY grp ORDER BY val DESC) 一句話搞定
從每筆訂單的上一筆拉資訊 需要 self-join LAG()、LEAD() 一次到位

這張表不是想說「視窗函式永遠比較好」，而是想說：兩者的出發點不同。GROUP BY 是「我只想看彙總」，這在報表輸出時很好用；視窗函式是「我想同時看細節與彙總」，這在做探索性分析、找異常值、畫圖時是首選。實務上兩者會互相搭配：先 GROUP BY 出彙總結果，再用視窗函式排序或加總占比。

第三個細節：視窗函式在 DuckDB 上可以放在 SELECT 內，但不能直接放在 WHERE 內。例如下面這個寫法會跳錯誤：

-- 這種寫法在 DuckDB 與標準 SQL 都會報錯
SELECT user_id, amount, SUM(amount) OVER (PARTITION BY user_id) AS user_total
FROM demo_orders
WHERE SUM(amount) OVER (PARTITION BY user_id) > 3000;

錯誤訊息大意是「視窗函式不允許出現在 WHERE 子句」。正確的寫法是用 CTE 或子查詢包起來：

import duckdb

con = duckdb.connect()
result = con.execute("""
    WITH ranked AS (
        SELECT user_id, order_date, amount,
            SUM(amount) OVER (PARTITION BY user_id) AS user_total
        FROM demo_orders
    )
    SELECT * FROM ranked WHERE user_total > 3000 ORDER BY user_id, order_date
""").fetchall()
for row in result:
    print(row)
# 輸出：('u01', '2025-11-01', 1500, 3500)
# 輸出：('u02', '2025-11-05', 1100, 3900)
# 輸出：('u03', '2025-09-10', 4500, 5700)
# 輸出：('u03', '2025-10-12', 300, 5700)
# 輸出：('u03', '2025-11-18', 900, 5700)
# 輸出：('u04', '2025-10-30', 2400, 4300)
# 輸出：('u04', '2025-11-25', 1300, 5600)

用 CTE 把視窗函式的結果先算出來、再用 WHERE 篩選，這是 SQL 標準的解法。Day 5 會專門展開 CTE 與遞迴查詢，這裡先建立「視窗函式不能直接出現在 WHERE」這個觀念。下次你看到同事寫錯這個位置時，可以直接指正確方向。

第四個細節：多欄位 partition。我們前面都是單一欄位 PARTITION BY user_id，實務上常常需要依「使用者 + 月份」切群：

import duckdb

con = duckdb.connect()
result = con.execute("""
    SELECT user_id, DATE_TRUNC('month', order_date) AS month,
           amount,
           SUM(amount) OVER (
               PARTITION BY user_id, DATE_TRUNC('month', order_date)
           ) AS monthly_total
    FROM demo_orders
    ORDER BY user_id, month
""").fetchall()
for row in result:
    print(f"user_id={row[0]} month={row[1]} amount={row[2]} monthly_total={row[3]}")
# 輸出：user_id=u01 month=2025-09-01 amount=1200 monthly_total=1200
# 輸出：user_id=u01 month=2025-10-01 amount=800 monthly_total=800
# 輸出：user_id=u01 month=2025-11-01 amount=1500 monthly_total=1500
# 輸出：(依此類推)

這個例子的關鍵是 PARTITION BY user_id, DATE_TRUNC('month', order_date) 用兩個欄位切群。對單一使用者單月份而言只會有一筆訂單，所以 monthly_total 等於自己；但如果同一個月有多筆訂單，partition 還是會正確把同一月份聚合。這是處理「使用者 × 時間維度」分析的標準模式，Day 4 排名與累計會反覆用到。

第五個細節：視窗函式結果可以直接放進 CREATE TABLE AS，把視窗結果實體化成新表：

import duckdb

con = duckdb.connect()
con.execute("""
    CREATE OR REPLACE TABLE demo_orders_with_running AS
    SELECT user_id, order_date, amount,
           SUM(amount) OVER (
               PARTITION BY user_id ORDER BY order_date
           ) AS running_total
    FROM demo_orders
""")
print(con.execute(
    "SELECT COUNT(*) FROM demo_orders_with_running"
).fetchone())
# 輸出：(12,)

這段把視窗函式結果寫成實體表，這在管線設計時很方便：視窗運算通常比較吃 CPU，當你需要把中間結果餵給下一個步驟（譬如做模型評估、報表輸出）時，先實體化能讓後續步驟讀得更快。注意這是「落地（materialize）」的標準寫法，整個系列的 ETL 管線大量採用這個模式。當資料規模到 GB 等級時，你會更常看到 CREATE TABLE ... AS SELECT ... OVER ... 的寫法。

常見錯誤與踩雷

第一個雷：把 PARTITION BY 與 ORDER BY 順序搞反。PARTITION BY 必須在 ORDER BY 之前，這是 SQL 標準的強制順序。DBA 不會接受 OVER (ORDER BY x PARTITION BY y) 這種寫法，請嚴格寫成 OVER (PARTITION BY y ORDER BY x)。第二個雷：忘記 ORDER BY 寫進 OVER() 內，導致累計值在群內不穩定。請記得「我要計算累計，就一定要在 OVER() 內 ORDER BY」。

第三個雷：frame 子句的 BETWEEN ... AND ... 寫錯方向，導致視窗結果是空的。語法是 ... BETWEEN <起點> AND <終點>，兩個端點都要寫，並且「起點時間不能晚於終點」。如果你寫成 BETWEEN CURRENT ROW AND UNBOUNDED PRECEDING，DuckDB 會回錯誤訊息：Invalid window frame。第四個雷：在 DuckDB 與 PostgreSQL 同時用視窗函式但忘了大小寫差異。SQL 標準函式名不區分大小寫，sum() 與 SUM() 等效，但 OVER 後面接的子句也是大小寫不敏感，寫成 over 也可以。為了與同事一致，建議一律大寫。

第五個雷：在 view 或 CTE 裡用了視窗函式，但期望它在子查詢內重新計算。事實上視窗函式只會在 SELECT 階段計算，不會在子查詢內被「重新開機」一次。如果你要在子查詢裡計算，請把視窗函式寫在子查詢內部，不要外拋出去。這個觀念 Day 5 談 CTE 時會再示範。

效能與實務提醒

視窗函式的效能關鍵在於 frame 與 partition 的大小。當 PARTITION BY 切的群數目極大（例如百萬個使用者）、每群內 ORDER BY 後只有 1 列時，視窗函式的成本大致等同 GROUP BY；但當每群內有數萬列（例如每個使用者的歷史事件很長）時，DuckDB 會把整個 partition 載入記憶體做排序與累計，這時 ORDER BY 的欄位若有索引（Day 7 會談）會明顯加速。

實務提醒：當你發現某段查詢慢，先用 EXPLAIN 看執行計畫，再看 SET threads=... 調整 thread 數，最後才是改 frame 子句。Day 7 會在 DuckDB 上完整展示 EXPLAIN 與 EXPLAIN ANALYZE，今天先把概念建立。今天的範例資料只有 12 筆，所以效能幾乎是即時；當資料量到百萬列等級時，frame 子句的選擇會是關鍵變數，請先掌握概念。

另一個提醒：視窗函式的結果依賴輸入順序；同一張表若用不同的實體排序，視窗結果仍會一致（因為 OVER (ORDER BY ...) 已經指定邏輯順序），但若 ORDER BY 內欄位有重複值，可能會出現不穩定排序。DBA 通常會用 ORDER BY col, secondary_col 加第二個排序鍵來穩定，這是 Day 7 談索引時會順手提到的細節。

小結

今天我們把視窗函式的入口學完：OVER() 的結構、PARTITION BY、ORDER BY、frame 子句，以及 SUM()、AVG() 的視窗版用法。我們用一份 12 筆的小型事實表，展示了「每列細節＋每群累計＋全體總計＋每列佔比」一次查完的能力，這在報表與探索性分析時非常省事。Frame 子句的 ROWS BETWEEN ... 雖然看似抽象，但只要能寫出來就解鎖了「移動平均」、「前 N 個」、「對稱視窗」這類分析需求。

把今天的關鍵詞抄進你的筆記本：視窗函式、OVER()、PARTITION BY、ORDER BY、ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW、running_total、grand_total。明天會把 ROW_NUMBER()、RANK()、DENSE_RANK()、LAG()、LEAD() 一次收進來，搭配 frame 子句談更深的排名與累計用法。

結語

視窗函式是把 SQL 從「彙總工具」變成「分析平台」的關鍵語法。學完這一篇，你的 SQL 表達力會提升一個等級；同一個 SELECT 內同時看細節、看彙總、看佔比，是商業分析最常用的寫法。今天的最大收穫是：看到 OVER() 不再心驚，而是能拆解成「這是計算 user 累計、這是計算全體總計」。明天我們會把排名函式（RANK()、ROW_NUMBER()）拿出來，搭配昨天學的累計寫法，組合出「找出每位使用者消費前三高的月份」、「計算相對於上個月的成長率」這類更實用的查詢。

明天，我們會進入「排名、累計與移動平均」的主題，把 ROW_NUMBER()、RANK()、DENSE_RANK() 的差異講清楚，並用 LAG()、LEAD() 寫出計算「上個月到這個月的成長率」的查詢；frame 子句的 ROWS BETWEEN n PRECEDING AND n FOLLOWING 也會在這一篇反覆用到。

延伸資源

  • DuckDB 視窗函式語法：https://duckdb.org/docs/sql/window_functions.html。本篇結構以這份官方文件為主。
  • PostgreSQL 視窗函式教學：https://www.postgresql.org/docs/current/tutorial-window.html。這是 SQL 標準最權威的實作參考之一。
  • Mode Analytics SQL 視窗函式教學：https://mode.com/sql-tutorial/sql-window-functions/。社群廣泛引用的入門教學。
  • 《SQL 進階：視窗函式實戰》（書籍）：Mark Beckner 等人所著，深入 frame 子句與排名差異。
  • DuckDB CLI 一行裝：brew install duckdb（macOS）或 winget install duckdb（Windows），裝完可直接 duckdb -c "SELECT 1"。
標籤：

留言

張貼留言

這個網誌中的熱門文章

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 建構深度學習模型。 開發者與研究人員 ：想更深入了...
張貼留言
閱讀完整內容