理解視窗函式 • PostgreSQL 的藝術

有視窗函式之前的 SQL 是一回事，之後的 SQL 是另一回事——這個工具就是這麼強大！視窗函式（window function）的核心概念是一次處理結果集中的多個值：你透過視窗看到一些同儕列（peer rows），並能像聚合函式一樣，從它們計算出單一輸出值。

視窗與 Frame#

第一步是理解函式能存取哪些資料：對每一列輸入，你能看到一個資料的 frame。array_agg() 是會建出陣列的聚合函式，正好用來觀察 window frame 的內容：

select x, array_agg(x) over (order by x)
  from generate_series(1, 3) as t(x);

 x | array_agg
---+-----------
 1 | {1}
 2 | {1,2}
 3 | {1,2,3}
(3 rows)

視窗定義 over (order by x) 其實等同於 over (order by x rows between unbounded preceding and current row)，寫全稱會得到一模一樣的結果。也可以使用其他 frame 規格：

select x,
       array_agg(x) over (rows between current row
                                   and unbounded following)
  from generate_series(1, 3) as t(x);

 x | array_agg
---+-----------
 1 | {1,2,3}
 2 | {2,3}
 3 | {3}
(3 rows)

若完全不寫 frame 子句，預設是每一列都看到整個資料集，這在計算總和與百分比時特別好用：

select x,
       array_agg(x) over () as frame,
       sum(x) over () as sum,
       x::float/sum(x) over () as part
  from generate_series(1, 3) as t(x);

 x | frame   | sum |         part
---+---------+-----+-------------------
 1 | {1,2,3} |   6 | 0.166666666666667
 2 | {1,2,3} |   6 | 0.333333333333333
 3 | {1,2,3} |   6 |               0.5
(3 rows)

你知道可以在單一 SQL 查詢中同時算出一欄的總和、以及當前值占總和的比例嗎？這正是視窗函式帶來的突破。

用 PARTITION BY 切分 Frame#

PARTITION BY 子句可定義另一種 frame：與當前列共享某個屬性的列成為同儕列，該屬性就是分區（partition）。以 F1 資料庫的比賽 890（2013 年匈牙利大獎賽）為例，列出所有參賽車手的名次，並同時顯示「與同車隊隊友相比的排名」：

select surname,
          constructors.name,
         position,
          format('%s / %s',
                row_number()
                    over(partition by constructorid
                             order by position nulls last),

                  count(*) over(partition by constructorid)
               )
            as "pos same constr"
    from      results
         join drivers using(driverid)
         join constructors using(constructorid)
   where raceid = 890
order by position;

partition by frame 讓我們看到 constructorid 與當前列相同的同儕列。這個分區在 format() 呼叫中用了兩次：搭配 row_number() 得到「在同車隊車手中的名次」，搭配 count(*) 得到「同車隊有幾位車手參賽」：

     surname   │    name     │ position │ pos same constr
═══════════════╪═════════════╪══════════╪═════════════════
 Hamilton      │ Mercedes    │        1 │ 1 / 2
 Räikkönen     │ Lotus F1    │        2 │ 1 / 2
 Vettel        │ Red Bull    │        3 │ 1 / 2
 Webber        │ Red Bull    │        4 │ 2 / 2
 Alonso        │ Ferrari     │        5 │ 1 / 2
 ...
 Rosberg       │ Mercedes    │       19 │ 2 / 2
 Bottas        │ Williams    │        ¤ │ 2 / 2
 Sutil         │ Force India │        ¤ │ 2 / 2
 Gutiérrez     │ Sauber      │        ¤ │ 2 / 2
(22 rows)

單一查詢就能同時取得每位車手的個別資訊，加上整場比賽層次的其他資訊。

視窗函式一律在 where 子句之後才執行，所以 frame 裡只看得到查詢可用結果集中的列。

可用的視窗函式#

所有你已經會用的聚合函式（sum、min、max、count、avg…）都能套在 window frame 上，而不只是 grouping 子句。PostgreSQL 還允許用 CREATE AGGREGATE 註冊自訂聚合，自訂聚合同樣能配上 window frame 定義。此外還有一批內建的專用視窗函式：

  select surname,
         position as pos,
         row_number()
           over(order by fastestlapspeed::numeric)
           as fast,
         ntile(3) over w as "group",
         lag(code, 1) over w as "prev",
         lead(code, 1) over w as "next"
    from      results
         join drivers using(driverid)
   where raceid = 890
  window w as (order by position)
order by position;

同一個視窗定義被多次重複使用，因此用 window w as (...) 給它命名以簡化 SQL。
每位車手查出：賽果名次、最快單圈速度的排名（row_number()）、用 ntile 把車手分組後的組號、以及前一位與下一位車手的代號（lag 與 lead）。

查詢結果（22 列節錄）

     surname   │ pos │ fast │ group │ prev │ next
═══════════════╪═════╪══════╪═══════╪══════╪══════
 Hamilton      │   1 │   20 │     1 │ ¤    │ RAI
 Räikkönen     │   2 │   17 │     1 │ HAM  │ VET
 Vettel        │   3 │   21 │     1 │ RAI  │ WEB
 Webber        │   4 │   22 │     1 │ VET  │ ALO
 Alonso        │   5 │   15 │     1 │ WEB  │ GRO
 ...
 Rosberg       │  19 │   19 │     3 │ DIR  │ BOT
 Sutil         │   ¤ │    2 │     3 │ GUT  │ ¤
 Gutiérrez     │   ¤ │    1 │     3 │ BOT  │ SUT
 Bottas        │   ¤ │    7 │     3 │ ROS  │ GUT
(22 rows)

可以看到最快單圈速度沒有想像中重要——單圈最快的兩位車手甚至沒完賽。就 SQL 而言，這也示範了同一查詢可以回傳兩種不同的序列，而且能引用其他列。

何時使用視窗函式#

視窗函式真正的魔法在於 OVER () 子句讓它看得到的資料 frame——由 PARTITION BY 與 ORDER BY 子句指定。

記住兩件事：
視窗子句永遠在查詢最後才被考慮（在 where 之後），frame 裡只有被選進輸出的列——例如無法直接對「不想顯示的值」計算百分比，那種情況得用子查詢。
當你要為結果集的每一列計算值，而該計算依賴同一結果集中的其他列時，就用視窗函式。經典例子是每週業績的行銷分析：同時輸出每天的營業額，以及與上週同日相比的變化。