postgresql windows function 第三篇

    前面两篇已经很好的介绍了窗口函数，这篇我们来关注一下function name这个部分。postgresql有一些内置的函数，专门用于窗口函数。如下
   

Function	Return Type	Description
row_number()	bigint	number of the current row within its partition, counting from 1
rank()	bigint	rank of the current row with gaps; same as row_number of its first peer
dense_rank()	bigint	rank of the current row without gaps; this function counts peer groups
percent_rank()	double precision	relative rank of the current row: (rank - 1) / (total rows - 1)
cume_dist()	double precision	relative rank of the current row: (number of rows preceding or peer with current row) / (total rows)
ntile(num_buckets integer)	integer	integer ranging from 1 to the argument value, dividing the partition as equally as possible
lag(value any [, offsetinteger [, default any ]])	same type as value	returns value evaluated at the row that is offset rows before the current row within the partition; if there is no such row, instead return default. Bothoffset and default are evaluated with respect to the current row. If omitted, offset defaults to 1 and default to null
lead(value any [, offsetinteger [, default any ]])	same type as value	returns value evaluated at the row that is offset rows after the current row within the partition; if there is no such row, instead return default. Bothoffset and default are evaluated with respect to the current row. If omitted, offset defaults to 1 and default to null
first_value(value any)	same type as value	returns value evaluated at the row that is the first row of the window frame
last_value(value any)	same type as value	returns value evaluated at the row that is the last row of the window frame
nth_value(value any, nthinteger)	same type as value	returns value evaluated at the row that is the nth row of the window frame (counting from 1); null if no such row

这里我们一个一个的来。
  row_number() 
  

点击(此处)折叠或打开

1. postgres=# select depname, empno, salary, row_number() over() from empsalary;
2.  depname | empno | salary | row_number
3. ----------+-------+--------+------------
   
4.  develop  | 11 | 5200 | 1
   
5.  develop  | 7  | 4200 | 2
   
6.  develop  | 9  | 4500 | 3
   
7.  personel | 5  | 3500 | 4
   
8.  personel | 6  | 6500 | 5
   
9.  personel | 12 | 6500 | 6
   
10.  personel | 15 | 8900 | 7
    
11. (7 行记录)

其实这个函数就是给窗口内的每一行编号，从一号开始。不过如果没有排序的话，这样的编号感觉也没有意思啊。
 

点击(此处)折叠或打开

1. postgres=# select depname, empno, salary, row_number() over() from empsalary order by salary;
2.  depname | empno | salary | row_number
   
3. ----------+-------+--------+------------
   
4.  personel | 5 | 3500 | 4
   
5.  develop | 7 | 4200 | 2
   
6.  develop | 9 | 4500 | 3
   
7.  develop | 11 | 5200 | 1
   
8.  personel | 6 | 6500 | 5
   
9.  personel | 12 | 6500 | 6
   
10.  personel | 15 | 8900 | 7
    
11. (7 行记录)

怪了，使用order by 但是这里放的地方错误，可以从这里推断，row_number(）是比order by 先执行的。
正确的写法是

点击(此处)折叠或打开

1. postgres=# select depname, empno, salary, row_number() over(order by salary) from empsalary ;
2.  depname | empno | salary | row_number
   
3. ----------+-------+--------+------------
   
4.  personel | 5 | 3500 | 1
   
5.  develop | 7 | 4200 | 2
   
6.  develop | 9 | 4500 | 3
   
7.  develop | 11 | 5200 | 4
   
8.  personel | 6 | 6500 | 5
   
9.  personel | 12 | 6500 | 6
   
10.  personel | 15 | 8900 | 7
    
11. (7 行记录)

rank（）

点击(此处)折叠或打开

1. postgres=# select depname, empno, salary, rank() over(order by salary) from empsalary ;
2.  depname | empno | salary | rank
   
3. ----------+-------+--------+------
   
4.  personel | 5 | 3500 | 1
   
5.  develop | 7 | 4200 | 2
   
6.  develop | 9 | 4500 | 3
   
7.  develop | 11 | 5200 | 4
   
8.  personel | 6 | 6500 | 5
   
9.  personel | 12 | 6500 | 5
   
10.  personel | 15 | 8900 | 7
    
11. (7 行记录)

从结果可以看出，rank其实和row_number()差不多，唯一的差别有两个5，没有6.这就是所谓的gap吧。

dense_rank()

点击(此处)折叠或打开

1. postgres=# select depname, empno, salary, dense_rank() over(order by salary) from empsalary ;
2.  depname | empno | salary | dense_rank
   
3. ----------+-------+--------+------------
   
4.  personel | 5 | 3500 | 1
   
5.  develop | 7 | 4200 | 2
   
6.  develop | 9 | 4500 | 3
   
7.  develop | 11 | 5200 | 4
   
8.  personel | 6 | 6500 | 5
   
9.  personel | 12 | 6500 | 5
   
10.  personel | 15 | 8900 | 6
    
11. (7 行记录)

dense_rank()和rank是一样的，但是区别还是有的，他是没有gap的。


percent_rank() 和 cume_dist() 这两个函数不知道有什么用，不过计算的公式就在上面，看看就知道怎么算了。


ntile()

点击(此处)折叠或打开

1. postgres=# select depname, empno, salary, ntile(3) over(order by salary) from empsalary ;
2.  depname | empno | salary | ntile
   
3. ----------+-------+--------+-------
   
4.  personel | 5 | 3500 | 1
   
5.  develop | 7 | 4200 | 1
   
6.  develop | 9 | 4500 | 1
   
7.  develop | 11 | 5200 | 2
   
8.  personel | 6 | 6500 | 2
   
9.  personel | 12 | 6500 | 3
   
10.  personel | 15 | 8900 | 3
    
11. (7 行记录)

这个函数的作用是将结果分组，3 表示分为三组。每一组再编号。


lag(value  any  [, offsetinteger  [, default  any  ]])

点击(此处)折叠或打开

1. postgres=# select depname, empno, salary, lag(salary,1,0) over(order by salary)
   
2.  from empsalary ;
   
3.  depname | empno | salary | lag
   
4. ----------+-------+--------+------
   
5.  personel | 5 | 3500 | 0
   
6.  develop | 7 | 4200 | 3500
   
7.  develop | 9 | 4500 | 4200
   
8.  develop | 11 | 5200 | 4500
   
9.  personel | 6 | 6500 | 5200
   
10.  personel | 12 | 6500 | 6500
    
11.  personel | 15 | 8900 | 6500
    
12. (7 行记录)

获取前一行的对应列，如果没有的话，就用0表示。而lead刚好和他相反。自己试一下就知道了。
first_value() 

点击(此处)折叠或打开

1. postgres=# select depname, empno, salary, first_value(salary) over () from empsa
   
2. lary;
   
3.  depname | empno | salary | first_value
   
4. ----------+-------+--------+-------------
   
5.  develop | 11 | 5200 | 5200
   
6.  develop | 7 | 4200 | 5200
   
7.  develop | 9 | 4500 | 5200
   
8.  personel | 5 | 3500 | 5200
   
9.  personel | 6 | 6500 | 5200
   
10.  personel | 12 | 6500 | 5200
    
11.  personel | 15 | 8900 | 5200
    
12. (7 行记录)

获取windows frame的第一行。
last_value() 刚好和first_value()相反，取最后一列。

点击(此处)折叠或打开

1. postgres=# select depname, empno, salary, last_value(salary) over () from empsal
   
2. ary;
   
3.  depname | empno | salary | last_value
   
4. ----------+-------+--------+------------
   
5.  develop | 11 | 5200 | 8900
   
6.  develop | 7 | 4200 | 8900
   
7.  develop | 9 | 4500 | 8900
   
8.  personel | 5 | 3500 | 8900
   
9.  personel | 6 | 6500 | 8900
   
10.  personel | 12 | 6500 | 8900
    
11.  personel | 15 | 8900 | 8900
    
12. (7 行记录)

再来一个

点击(此处)折叠或打开

1. postgres=# select depname, empno, salary, last_value(salary) over (order by sala
   
2. ry) from empsalary;
   
3.  depname | empno | salary | last_value
   
4. ----------+-------+--------+------------
   
5.  personel | 5 | 3500 | 3500
   
6.  develop | 7 | 4200 | 4200
   
7.  develop | 9 | 4500 | 4500
   
8.  develop | 11 | 5200 | 5200
   
9.  personel | 6 | 6500 | 6500
   
10.  personel | 12 | 6500 | 6500
    
11.  personel | 15 | 8900 | 8900
    
12. (7 行记录)

为什么这里会不一样的，仔细想想order by为什么会导致这么样的结果，如果你看了第一篇的话，就能明白了。

而nth_value()我想你自己从字面上就能理解了吧。这里就不讲解了。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30010989/viewspace-1350990/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30010989/viewspace-1350990/