PostgreSQL的window函数整理(四)
mpno | salary | enroll_date
-----------+-----------+-------+--------+-------------
0.2 | develop | 8 | 6000 | 2006-10-01
0.6 | develop | 10 | 5200 | 2007-08-01
0.6 | develop | 11 | 5200 | 2007-08-15
0.8 | develop | 9 | 4500 | 2008-01-01
1 | develop | 7 | 4200 | 2008-01-01
0.5 | personnel | 2 | 3900 | 2006-12-23
1 | personnel | 5 | 3500 | 2007-12-10
0.25 | sales | 6 | 5500 | 2007-01-02
0.5 | sales | 1 | 5000 | 2006-10-01
1 | sales | 3 | 4800 | 2007-08-01
1 | sales | 4 | 4800 | 2007-08-08
(11 rows)
postgres=# select ntile(3)over(partition by depname order by salary desc),* from empsalary;
ntile | depname | empno | salary | enroll_date
-------+-----------+-------+--------+-------------
1 | develop | 8 | 6000 | 2006-10-01
1 | develop | 10 | 5200 | 2007-08-01
2 | develop | 11 | 5200 | 2007-08-15
2 | develop | 9 | 4500 | 2008-01-01
3 | develop | 7 | 4200 | 2008-01-01
1 | personnel | 2 | 3900 | 2006-12-23
2 | personnel | 5 | 3500 | 2007-12-10
1 | sales | 6 | 5500 | 2007-01-02
1 | sales | 1 | 5000 | 2006-10-01
2 | sales | 3 | 4800 | 2007-08-01
3 | sales | 4 | 4800 | 2007-08-08
(11 rows) www.2cto.com
postgres=# select lag(salary,2,null)over(partition by depname order by salary desc),* from empsalary;
lag | depname | empno | salary | enroll_date
------+-----------+-------+--------+-------------
| develop | 8 | 6000 | 2006-10-01
| develop | 10 | 5200 | 2007-08-01
6000 | 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
| personnel | 5 | 3500 | 2007-12-10
| sales | 6 | 5500 | 2007-01-02
| sales | 1 | 5000 | 2006-10-01
5500 | sales | 3 | 4800 | 2007-08-01
5000 | sales | 4 | 4800 | 2007-08-08
(11 rows)
postgres=# select first_value(salary)over(partition by depname order by salary desc),* from empsalary;
first_value | depname | empno | salary | enroll_date
-------------+-----------+-------+--------+-------------
6000 | develop | 8 | 6000 | 2006-10-01
6000 | develop | 10 | 5200 | 2007-08-01
6000 | develop | 11 | 5200 | 2007-08-15
6000 | develop | 9 | 4500 | 2008-01-01
6000 | develop | 7 | 4200 | 2008-01-01
3900 | personnel | 2 | 3900 | 2006-12-23
3900 | personnel | 5 | 3500 | 2007-12-10
5500 | sales | 6 | 5500 | 2007-01-02
5500 | sales | 1 | 5000 | 2006-10-01
5500 | sales | 3 | 4800 | 2007-08-01
5500 | sales | 4 | 4800 | 2007-08-08
(11 rows) www.2cto.com
postgres=# select last_value(salary)over(partition by depname order by salary desc),* from empsalary;
last_value | depname | empno | salary | enroll_date
------------+-----------+-------+--------+-------------
6000 | develop | 8 | 6000 | 2006-10-01
5200 | develop | 10 | 5200 | 2007-08-01
5200 | develop | 11 | 5200 | 2007-08-15
4500 | develop | 9 | 4500 | 2008-01-01
4200 | develop | 7 | 4200 | 2008-01-01
3900 | personnel | 2 | 3900 | 2006-12-23
3500 | personnel | 5 | 3500 | 2007-12-10
5500 | sales | 6 | 5500 | 2007-01-02
5000 | sales |