PostgreSQL的window函数整理(二)
2 | 3900 | 2006-12-23
7400 | 3700.0000000000000000 | personnel | 5 | 3500 | 2007-12-10
20100 | 5025.0000000000000000 | sales | 3 | 4800 | 2007-08-01
20100 | 5025.0000000000000000 | sales | 1 | 5000 | 2006-10-01
20100 | 5025.0000000000000000 | sales | 4 | 4800 | 2007-08-08
20100 | 5025.0000000000000000 | sales | 6 | 5500 | 2007-01-02
(11 rows) www.2cto.com
b.统计人员在所在部门的薪水排名情况
postgres=# select rank() OVER (PARTITION BY depname ORDER BY salary),* from empsalary;
rank | depname | empno | salary | enroll_date
------+-----------+-------+--------+-------------
1 | develop | 7 | 4200 | 2008-01-01
2 | develop | 9 | 4500 | 2008-01-01
3 | develop | 10 | 5200 | 2007-08-01
3 | develop | 11 | 5200 | 2007-08-15
5 | develop | 8 | 6000 | 2006-10-01
1 | personnel | 5 | 3500 | 2007-12-10
2 | personnel | 2 | 3900 | 2006-12-23
1 | sales | 4 | 4800 | 2007-08-08
1 | sales | 3 | 4800 | 2007-08-01
3 | sales | 1 | 5000 | 2006-10-01
4 | sales | 6 | 5500 | 2007-01-02
(11 rows) www.2cto.com
3.一个有趣的例子 注意使用order by,结果会两样
create table foo(a int,b int) ;
insert into foo values (1,1);
insert into foo values (1,1);
insert into foo values (2,1);
insert into foo values (4,1);
insert into foo values (2,1);
insert into foo values (4,1);
insert into foo values (5,1);
insert into foo values (11,3);
insert into foo values (12,3);
insert into foo values (22,3);
insert into foo values (16,3);
insert into foo values (16,3);
insert into foo values (16,3);
postgres=# select sum(a) over (partition by b), a, b from foo;
sum | a | b
-----+----+---
19 | 1 | 1
19 | 1 | 1
19 | 2 | 1
19 | 4 | 1
19 | 2 | 1
19 | 4 | 1
19 | 5 | 1
93 | 11 | 3
93 | 12 | 3
93 | 22 | 3
93 | 16 | 3
93 | 16 | 3
93 | 16 | 3
(13 rows)
www.2cto.com
postgres=# select sum(a) over (partition by b order by a), a, b from foo;
sum | a | b
-----+----+---
2 | 1 | 1
2 | 1 | 1
6 | 2 | 1
6 | 2 | 1
14 | 4 | 1
14 | 4 | 1
19 | 5 | 1
11 | 11 | 3
23 | 12 | 3
71 | 16 | 3
71 | 16 | 3
71 | 16 | 3
93 | 22 | 3
(13 rows)
postgres=# select a, b, sum(a) over (partition by b order by a ROWS
postgres(# BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) from foo;
a | b | sum www.2cto.com
----+---+-----
1 | 1 | 19
1 | 1 | 19
2 | 1 | 19
2 | 1 | 19
4 | 1 | 19
4 | 1 | 19
5 | 1 | 19
11 | 3 | 93
12 | 3 | 93
16 | 3 | 93
16 | 3 | 93
16 | 3 | 93
22 | 3 | 93
(13 rows)
官网中的解释是： By default, if ORDER BY is supplied then the frame consists of all rows from the start of the partition up through the current row, plus any following rows that are equal to the current row according to the ORDER BY clause. When ORDER BY is omitted the default frame consists of all rows in the partition.
默认情况下，带了order by 参数会从分组的起始值开始一直叠加，直到当前值为止，当忽略order by 参数则会计算分组中所有值的和。
4.其他的窗口函数
row_number(): 从当前开始，不间断,如1，2，3，4，5，6
rank() :从当前开始，会间断，如1，2，2，4，5，6
dense_rank():从当前开始不间断，但会重复，如1，2，2，3，4，5
percent_rank()：从当前开始，计算在分组中的比例，如0，0.25，0.25，0.75，1，0，1 从0-1不断地循环 www.2cto.com
cume_dist()：当前行的排序除以分组的数量,如分组有4行，则值为0.25，0.5，0.75，1
ntile(num_buckets integer)：从1到当前值，除以分组的的数量，尽可能使分布平均
lag(value any [, offset integer [, default any ]])：偏移量函数，取滞后值,如lag(column_name,2,0)表示字段偏移量为2，没有则用default值代替，这里是0，不写