3.5. 窗口函數 · PostgreSQL 中文文檔 9.3

# 3.5\. 窗口函數 _窗口函數_在和當前行相關的一組表行上執行計算。這相當于一個可以由聚合函數完成的計算類型。但不同于常規的聚合函數，使用的窗口函數不會導致行被分組到一個單一的輸出行；行保留其獨立的身份。在后臺，窗口函數能夠訪問的不止查詢結果的當前行。這里是一個例子，說明如何比較每個員工的工資和在他或她的部門的平均工資： ``` SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary; ``` ``` depname | empno | salary | avg -----------+-------+--------+----------------------- develop | 11 | 5200 | 5020.0000000000000000 develop | 7 | 4200 | 5020.0000000000000000 develop | 9 | 4500 | 5020.0000000000000000 develop | 8 | 6000 | 5020.0000000000000000 develop | 10 | 5200 | 5020.0000000000000000 personnel | 5 | 3500 | 3700.0000000000000000 personnel | 2 | 3900 | 3700.0000000000000000 sales | 3 | 4800 | 4866.6666666666666667 sales | 1 | 5000 | 4866.6666666666666667 sales | 4 | 4800 | 4866.6666666666666667 (10 rows) ``` 前三輸出列直接來自表`empsalary`，并有一個針對表中的每一行的輸出行。第四列將代表所有含有相同的`depname`值的表行的平均值作為當前值。（這實際上與標準`avg`聚合函數的功能相同，但是`OVER`子句使其被視為一個窗口函數并在一組合適的行上執行計算。）窗口函數的調用總是包含一個`OVER`子句，后面直接跟著窗口函數的名稱和參數。這是它在語法上區別于普通函數或聚合功能的地方。 `OVER`子句決定如何將查詢的行進行拆分以便給窗口函數處理。 `OVER`子句內的`PARTITION BY`列表指定將行劃分成組或分區，組或分區共享相同的`PARTITION BY`表達式的值。對于每一行，窗口函數在和當前行落在同一個分區的所有行上進行計算。你還可以使用窗口函數`OVER`內的`ORDER BY`來控制行的順序。（`ORDER BY`窗口甚至不需要與行的輸出順序相匹配。）下面是一個例子： ``` SELECT depname, empno, salary, rank() OVER (PARTITION BY depname ORDER BY salary DESC) FROM empsalary; ``` ``` depname | empno | salary | rank -----------+-------+--------+------ develop | 8 | 6000 | 1 develop | 10 | 5200 | 2 develop | 11 | 5200 | 2 develop | 9 | 4500 | 4 develop | 7 | 4200 | 5 personnel | 2 | 3900 | 1 personnel | 5 | 3500 | 2 sales | 1 | 5000 | 1 sales | 4 | 4800 | 2 sales | 3 | 4800 | 2 (10 rows) ``` 正如此處所示，`rank`函數按照由`ORDER BY`子句定義的順序，在當前行的分區里為每個不同的`ORDER BY`值產生了一個數值排名。 `rank` 不需要明確的參數，因為它的行為完全取決于`OVER`子句。窗口函數的行來自查詢的`FROM`子句產生，并且如果有的話，經過`WHERE`,`GROUP BY`和`HAVING`子句過濾的"虛擬表"。比如，被移除掉的行，因為不符合`WHERE`條件，所以是不能被任何窗口函數可見的。一個查詢可以包含多個窗口函數，通過不同的`OVER`子句用不同的方式分割數據，但是他們都作用在這個虛擬表定義的同一個行集合。我們已經看到了，如果行排序并不重要，`ORDER BY`可以省略。在只有一個包含了所有行的分區情況下，也可以省略`PARTITION BY`。還有一個與窗口函數相關的重要的概念：對于每一行，有在其分區范圍內的行集，又稱為它的_window frame_。許多（但不是全部）窗口函數，只作用于window frame中的行上，而不是整個分區。默認情況下，如果使用`ORDER BY`，那么這個frame包含從分區開始到當前行的所有行，以及那些當前行后面的，根據`ORDER BY` 子句等于當前行的所有行，如果省略`ORDER BY`，那么，frame默認包含分區中的所有行。 \[1\] 下面是一個使用`sum`的例子： ``` SELECT salary, sum(salary) OVER () FROM empsalary; ``` ``` salary | sum --------+------- 5200 | 47100 5000 | 47100 3500 | 47100 4800 | 47100 3900 | 47100 4200 | 47100 4500 | 47100 4800 | 47100 6000 | 47100 5200 | 47100 (10 rows) ``` 如上，因為在`OVER`子句中沒有使用`ORDER BY`，因此， window frame與分區(不使用`PARTITION BY`時即整個表)相同；換句話說，每一次sum求和都是使用表中所有的salary，所以我們得到的每個輸出行的結果相同。但是，如果我們添加`ORDER BY`子句，我們會得到不同的結果： ``` SELECT salary, sum(salary) OVER (ORDER BY salary) FROM empsalary; ``` ``` salary | sum --------+------- 3500 | 3500 3900 | 7400 4200 | 11600 4500 | 16100 4800 | 25700 4800 | 25700 5000 | 30700 5200 | 41100 5200 | 41100 6000 | 47100 (10 rows) ``` 這里的總和是從第一個（最低）工資到當前一個，包括任何當前重復的（注意重復薪金的結果）。窗口函數僅允許在查詢的`SELECT`列表和`ORDER BY`子句中使用。在其他地方禁止使用，比如`GROUP BY`,`HAVING`和`WHERE`子句。這是因為它們邏輯上在處理這些子句之后執行。此外，窗口函數在標準聚合函數之后執行。這意味在一個窗口函數的參數中包含一個標準聚合函數的調用是有效的，但反過來不行。執行窗口計算后，如果有必要對行進行過濾或分組，你可以使用子查詢。例如： ``` SELECT depname, empno, salary, enroll_date FROM (SELECT depname, empno, salary, enroll_date, rank() OVER (PARTITION BY depname ORDER BY salary DESC, empno) AS pos FROM empsalary ) AS ss WHERE pos < 3; ``` 上面的查詢只顯示內部查詢結果中`rank`小于3的行。當查詢涉及多個窗口函數時，可以寫成每一個都帶有單獨的`OVER`子句，但是，如果期待為多個窗口函數采用相同的窗口行為，這樣做就會產生重復，并且容易出錯。作為代替，每個窗口行為可以在`WINDOW`子句中進行命名，然后再被`OVER`引用。例如： ``` SELECT sum(salary) OVER w, avg(salary) OVER w FROM empsalary WINDOW w AS (PARTITION BY depname ORDER BY salary DESC); ``` 有關窗口函數的更多詳細信息請查閱[Section 4.2.8](#calibre_link-1331), [Section 9.21](#calibre_link-1804),[Section 7.2.4](#calibre_link-1332),和 [SELECT](#calibre_link-104)的參考頁。 ### Notes \[1\] 當然，還有其他定義window frame的方法，但本教程并不包括它們。詳情請參閱[Section 4.2.8](#calibre_link-1331)。