NYC's Blog 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, salary
FROM
(SELECT depname, empno, salary,
rank() OVER (PARTITION BY depname ORDER BY salary DESC, empno) AS pos
FROM empsal
) AS ss
WHERE pos < 3;
postgres=# SELECT depname, empno, salary
postgres-# FROM
postgres-# (SELECT depname, empno, salary,
postgres(# rank() OVER (PARTITION BY depname ORDER BY salary DESC, empno) AS pos
postgres(# FROM empsal
postgres(# ) AS ss
postgres-# 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 w
postgres-# FROM empsal
postgres-# 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)
评论已关闭