PostgreSQL的window函数整理(2)
来源：未知 责任编辑：责任编辑 发表时间:2015-09-17 09:42 点击:次
personnel | 5 | 3500 | 2007-12-10
sales | 4 | 4800 | 2007-08-08
sales | 6 | 5500 | 2007-01-02
personnel | 2 | 3900 | 2006-12-23
develop | 7 | 4200 | 2008-01-01
develop | 9 | 4500 | 2008-01-01
sales | 3 | 4800 | 2007-08-01
develop | 8 | 6000 | 2006-10-01
develop | 11 | 5200 | 2007-08-15
(11 rows) www.2cto.com
2.统计示例
a.统计各部门的总薪水，平均薪水和部门的详细情况
postgres=# select sum(salary) OVER (PARTITION BY depname),avg(salary) OVER (PARTITION BY depname),* from empsalary;
sum | avg | depname | empno | salary | enroll_date
-------+-----------------------+-----------+-------+--------+-------------
25100 | 5020.0000000000000000 | develop | 10 | 5200 | 2007-08-01
25100 | 5020.0000000000000000 | develop | 7 | 4200 | 2008-01-01
25100 | 5020.0000000000000000 | develop | 9 | 4500 | 2008-01-01
25100 | 5020.0000000000000000 | develop | 8 | 6000 | 2006-10-01
25100 | 5020.0000000000000000 | develop | 11 | 5200 | 2007-08-15
7400 | 3700.0000000000000000 | personnel | 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
相关新闻>>
最新推荐更多>>>
- 发表评论
-
- 最新评论 更多>>
