PostgreSQL視窗函數

標籤：track   部門   body   ram   注意   html   例子   order by   win   

 

轉自：http://time-track.cn/postgresql-window-function.html

PostgreSQL提供了視窗函數的特性。視窗函數也是計算一些行集合（多個行組成的集合，我們稱之為視窗window frame）的資料，有點類似與聚集合函式（aggregate function）。但和常規的聚集合函式不同的是，視窗函數不會將參與計算的行合并成一行輸出，而是保留它們原來的樣子。看下面一個例子：

有一個表示員工薪資的表（部門、員工id，工資）：

postgres=# d empsal           Table "public.empsal" Column  |       Type        | Modifiers ---------+-------------------+----------- depname | character varying |  empno   | integer           |  salary  | integer           |

表內現在有如下資料：

postgres=# select * from empsal ;  depname  | empno | salary -----------+-------+-------- develop   |    11 |   5200 develop   |     7 |   4200 develop   |     9 |   4500 develop   |     8 |   6000 develop   |    10 |   5200 personnel |     5 |   3500 personnel |     2 |   3900 sales     |     3 |   4800 sales     |     1 |   5000 sales     |     4 |   4800(10 rows)

我們現在想將每個員工的工資與他所在部門的平均工資進行比較，SQL語句該如何寫？利用視窗函數，該查詢可以很容易的實現：

postgres=# SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsal;  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)

可以看到，聚集合函式avg的含義沒有變，仍然是求平均值。但和普通的聚集合函式不同的是，它不再對錶中所有的salary求平均值，而是對同一個部門（PARTITION BY指定的depname）內的salary求平均值，而且得到的結果由同一個部門內的所有行共用，並沒有將這些行合并。為了更好的體現普通聚集合函式與視窗函數中的聚集合函式的區別，再看下面的兩個查詢：

postgres=# SELECT avg(salary) FROM empsal;          avg          ----------------------- 4710.0000000000000000(1 row)postgres=# SELECT avg(salary) OVER (PARTITION BY depname) FROM empsal;          avg          ----------------------- 5020.0000000000000000 5020.0000000000000000 5020.0000000000000000 5020.0000000000000000 5020.0000000000000000 3700.0000000000000000 3700.0000000000000000 4866.6666666666666667 4866.6666666666666667 4866.6666666666666667(10 rows)

視窗函數總是包含OVER子句，它指定了視窗函數的名字和參數，也是由這個關鍵字來區分常規聚集合函式和視窗函數。OVER子句裡面的內容決定視窗函數即將處理的資料該如何劃分。在OVER子句裡面我們使用PARTITION BY將資料劃分成一個個的組（或者稱之為分區）。聚集合函式處理的時候以分區為單位進行處理，處理結果也由同一個分區內的所有行共用。比如上面的例子，PARTITION BY後面跟著的欄位是depname，所以avg函數將以部門為單位進行計算。其實，這個分區就是視窗（window frame），這也是視窗函數名字的由來。

我們還可以在一個視窗中使用ORDER BY來對輸出進行排序：

postgres=# SELECT depname, empno, salary, rank() OVER (PARTITION BY depname ORDER BY salary DESC) FROM empsal;  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     |     3 |   4800 |    2 sales     |     4 |   4800 |    2(10 rows)

視窗函數處理的行來自於FROM子句產生的“virtual table”，如果還有WHERE、GROUP BY、HAVING子句的話，還要經過這些條件的過濾，合格子句才會作為視窗函數的輸入。另外，一個查詢可以包含多個視窗函數。

剛才提到，我們使用PARTITION BY來劃分視窗，如果省略了該關鍵字，那麼整個表將作為一個視窗來處理：

postgres=# SELECT salary, sum(salary) OVER () FROM empsal; salary |  sum  --------+-------   5200 | 47100   4200 | 47100   4500 | 47100   6000 | 47100   5200 | 47100   3500 | 47100   3900 | 47100   4800 | 47100   5000 | 47100   4800 | 47100(10 rows)

但是，需要注意的是，如果在OVER子句中省略了PARTITION BY但卻包含了ORDER BY子句，情況將和上面不太一樣：

postgres=# SELECT salary, sum(salary) OVER(ORDER BY salary ) FROM empsal; salary |  sum  --------+-------   3500 |  3500   3900 |  7400   4200 | 11600   4500 | 16100   4800 | 25700   4800 | 25700   5000 | 30700   5200 | 41100   5200 | 41100   6000 | 47100(10 rows)

從結果可以看出，在省略了PARTITION BY但卻包含了ORDER BY子句的情況下，並不是整個表是一個視窗，而是將從最低（此例中是salary，所以這裡用最低這個詞）的行當前行作為一個視窗。這是要特別注意的。

最後，我們要注意視窗函數使用的情境：

• 只能在SELECT和ORDER BY子句中使用，不能在任何其他地方使用，比如GROUP BY、HAVING和WHERE子句。這是因為視窗函數的輸入是這些子句的輸出。這個先後邏輯不可以變。
• 可以在視窗函數的參數中使用聚集合函式，但是不能將窗內函數作為聚集合函式的參數。因為視窗函數要在聚集合函式之後執行。這個先後邏輯也不能變。

如果我們真的需要將視窗函數作為某個子句的輸入的話，我們可以構造一個SELECT子句，比如：

SELECT depname, empno, salaryFROM  (SELECT depname, empno, salary,          rank() OVER (PARTITION BY depname ORDER BY salary DESC, empno) AS pos     FROM empsal  ) AS ssWHERE pos < 3;postgres=# SELECT depname, empno, salarypostgres-# FROMpostgres-#   (SELECT depname, empno, salary,postgres(#           rank() OVER (PARTITION BY depname ORDER BY salary DESC, empno) AS pospostgres(#      FROM empsalpostgres(#   ) AS sspostgres-# WHERE pos < 3;  depname  | empno | salary -----------+-------+-------- develop   |     8 |   6000 develop   |    10 |   5200 personnel |     2 |   3900 personnel |     5 |   3500 sales     |     1 |   5000 sales     |     3 |   4800(6 rows)

如果一個查詢中包含多個視窗函數，那麼我們可以寫多個OVER子句，但如果這些視窗函數的作用是一樣的，那分開寫多個既是一種重複性工作，而且也容易出錯。這種情況下，我們可以將視窗裡面的內容寫成一個WINDOW子句，然後在多個OVER子句中引用。看下例中的兩種寫法：

第一種：SELECT sum(salary) OVER (PARTITION BY depname ORDER BY salary DESC),    avg(salary) OVER (PARTITION BY depname ORDER BY salary DESC) FROM empsal;postgres=# SELECT sum(salary) OVER (PARTITION BY depname ORDER BY salary DESC), avg(salary) OVER (PARTITION BY depname ORDER BY salary DESC) FROM empsal;  sum  |          avg          -------+-----------------------  6000 | 6000.0000000000000000 16400 | 5466.6666666666666667 16400 | 5466.6666666666666667 20900 | 5225.0000000000000000 25100 | 5020.0000000000000000  3900 | 3900.0000000000000000  7400 | 3700.0000000000000000  5000 | 5000.0000000000000000 14600 | 4866.6666666666666667 14600 | 4866.6666666666666667(10 rows)第二種：SELECT sum(salary) OVER w, avg(salary) OVER w  FROM empsal  WINDOW w AS (PARTITION BY depname ORDER BY salary DESC);postgres=# SELECT sum(salary) OVER w, avg(salary) OVER wpostgres-#   FROM empsalpostgres-#   WINDOW w AS (PARTITION BY depname ORDER BY salary DESC);  sum  |          avg          -------+-----------------------  6000 | 6000.0000000000000000 16400 | 5466.6666666666666667 16400 | 5466.6666666666666667 20900 | 5225.0000000000000000 25100 | 5020.0000000000000000  3900 | 3900.0000000000000000  7400 | 3700.0000000000000000  5000 | 5000.0000000000000000 14600 | 4866.6666666666666667 14600 | 4866.6666666666666667(10 rows)

 

PostgreSQL視窗函數（轉）