PostgreSQL窗口函数

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)


添加新评论

选择表情 captcha

友情提醒:不填或错填验证码会引起页面刷新,导致已填的评论内容丢失。

|