PostgreSQL的window函数整理(9)

来源：未知责任编辑：责任编辑发表时间:2015-09-17 09:42　点击:次

5200 | develop | 10 | 5200 | 2007-08-01

5200 | develop | 11 | 5200 | 2007-08-15

5200 | develop | 9 | 4500 | 2008-01-01

5200 | develop | 7 | 4200 | 2008-01-01

| personnel | 2 | 3900 | 2006-12-23

3500 | personnel | 5 | 3500 | 2007-12-10

| sales | 6 | 5500 | 2007-01-02

5000 | sales | 1 | 5000 | 2006-10-01

5000 | sales | 3 | 4800 | 2007-08-01

5000 | sales | 4 | 4800 | 2007-08-08

(11 rows) www.2cto.com

当一个查询涉及多个窗口函数的时候，可以用别名的办法来使用，更简单：

postgres=# select sum(salary)over w,avg(salary) over w,* from empsalary window w as (partition by depname order by salary desc);

-------+-----------------------+-----------+-------+--------+-------------

6000 | 6000.0000000000000000 | develop | 8 | 6000 | 2006-10-01

16400 | 5466.6666666666666667 | develop | 10 | 5200 | 2007-08-01

16400 | 5466.6666666666666667 | develop | 11 | 5200 | 2007-08-15

20900 | 5225.0000000000000000 | develop | 9 | 4500 | 2008-01-01

25100 | 5020.0000000000000000 | develop | 7 | 4200 | 2008-01-01

3900 | 3900.0000000000000000 | personnel | 2 | 3900 | 2006-12-23

7400 | 3700.0000000000000000 | personnel | 5 | 3500 | 2007-12-10

5500 | 5500.0000000000000000 | sales | 6 | 5500 | 2007-01-02

10500 | 5250.0000000000000000 | sales | 1 | 5000 | 2006-10-01

20100 | 5025.0000000000000000 | sales | 3 | 4800 | 2007-08-01

20100 | 5025.0000000000000000 | sales | 4 | 4800 | 2007-08-08

(11 rows) www.2cto.com

这个写法和下面的是一样的，不过更简单

SELECT sum(salary) OVER (PARTITION BY depname ORDER BY salary DESC), avg(salary) OVER (PARTITION BY depname ORDER BY salary DESC),* FROM empsalary;

参考文档：http://umitanuki.net/pgsql/wfv08/design.html