PostgreSQL的window函数整理(7)

来源：未知责任编辑：责任编辑发表时间:2015-09-17 09:42　点击:次

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