默认是null
PostgreSQL的window函数整理(三)
lead(value any [, offset integer [, default any ]])：偏移量函数，取提前值,类上 first_value(value any)：返回窗口框架中的第一个值
last_value(value any)：返回窗口框架中的最后一个值
nth_value(value any, nth integer)：返回窗口框架中的指定值，如nth_value(salary,2),则表示返回字段salary的第二个窗口函数值
5.其他窗口函数示例
postgres=# select row_number() over (partition by depname order by salary desc),* from empsalary;
row_number | depname | empno | salary | enroll_date
------------+-----------+-------+--------+-------------
1 | develop | 8 | 6000 | 2006-10-01
2 | develop | 10 | 5200 | 2007-08-01
3 | develop | 11 | 5200 | 2007-08-15
4 | develop | 9 | 4500 | 2008-01-01
5 | 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
2 | sales | 1 | 5000 | 2006-10-01
3 | sales | 3 | 4800 | 2007-08-01
4 | sales | 4 | 4800 | 2007-08-08
(11 rows)
www.2cto.com
postgres=# select rank() over(partition by depname order by salary desc),* from empsalary;
rank | depname | empno | salary | enroll_date
------+-----------+-------+--------+-------------
1 | develop | 8 | 6000 | 2006-10-01
2 | develop | 10 | 5200 | 2007-08-01
2 | develop | 11 | 5200 | 2007-08-15
4 | develop | 9 | 4500 | 2008-01-01
5 | 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
2 | sales | 1 | 5000 | 2006-10-01
3 | sales | 3 | 4800 | 2007-08-01
3 | sales | 4 | 4800 | 2007-08-08
(11 rows)
postgres=# select dense_rank() over(partition by depname order by salary desc),* from empsalary; www.2cto.com
dense_rank | depname | empno | salary | enroll_date
------------+-----------+-------+--------+-------------
1 | develop | 8 | 6000 | 2006-10-01
2 | develop | 10 | 5200 | 2007-08-01
2 | develop | 11 | 5200 | 2007-08-15
3 | develop | 9 | 4500 | 2008-01-01
4 | 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
2 | sales | 1 | 5000 | 2006-10-01
3 | sales | 3 | 4800 | 2007-08-01
3 | sales | 4 | 4800 | 2007-08-08
(11 rows)
postgres=# select percent_rank() over(partition by depname order by salary desc),* from empsalary;
percent_rank | depname | empno | salary | enroll_date
-------------------+-----------+-------+--------+-------------
0 | develop | 8 | 6000 | 2006-10-01
0.25 | develop | 10 | 5200 | 2007-08-01
0.25 | develop | 11 | 5200 | 2007-08-15
0.75 | develop | 9 | 4500 | 2008-01-01
1 | develop | 7 | 4200 | 2008-01-01
0 | personnel | 2 | 3900 | 2006-12-23
1 | personnel | 5 | 3500 | 2007-12-10
0 | sales | 6 | 5500 | 2007-01-02
0.333333333333333 | sales | 1 | 5000 | 2006-10-01
0.666666666666667 | sales | 3 | 4800 | 2007-08-01
0.666666666666667 | sales | 4 | 4800 | 2007-08-08
(11 rows)
postgres=# select cume_dist()over(partition by depname order by salary desc),* from empsalary; www.2cto.com
cume_dist | depname | e