PostgreSQL的window函数整理(3)
来源：未知 责任编辑：责任编辑 发表时间:2015-09-17 09:42 点击:次
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
相关新闻>>
最新推荐更多>>>
- 发表评论
-
- 最新评论 更多>>
