前面两篇已经很好的介绍了窗口函数,这篇我们来关注一下function name这个部分。postgresql有一些内置的函数,专门用于窗口函数。如下
这里我们一个一个的来。
row_number()
其实这个函数就是给窗口内的每一行编号,从一号开始。不过如果没有排序的话,这样的编号感觉也没有意思啊。
怪了,使用order by 但是这里放的地方错误,可以从这里推断,row_number()是比order by 先执行的。
正确的写法是
rank()
从结果可以看出,rank其实和row_number()差不多,唯一的差别有两个5,没有6.这就是所谓的gap吧。
dense_rank()
dense_rank()和rank是一样的,但是区别还是有的,他是没有gap的。
percent_rank() 和 cume_dist() 这两个函数不知道有什么用,不过计算的公式就在上面,看看就知道怎么算了。
ntile()
这个函数的作用是将结果分组,3 表示分为三组。每一组再编号。
lag(value any [, offsetinteger [, default any ]])
获取前一行的对应列,如果没有的话,就用0表示。而lead刚好和他相反。自己试一下就知道了。
first_value()
获取windows frame的第一行。
last_value() 刚好和first_value()相反,取最后一列。
再来一个
为什么这里会不一样的,仔细想想order by为什么会导致这么样的结果,如果你看了第一篇的话,就能明白了。
而nth_value()我想你自己从字面上就能理解了吧。这里就不讲解了。
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()
点击(此处)折叠或打开
- postgres=# select depname, empno, salary, row_number() over() from empsalary;
- depname | empno | salary | row_number
- ----------+-------+--------+------------
- develop | 11 | 5200 | 1
- develop | 7 | 4200 | 2
- develop | 9 | 4500 | 3
- personel | 5 | 3500 | 4
- personel | 6 | 6500 | 5
- personel | 12 | 6500 | 6
- personel | 15 | 8900 | 7
- (7 行记录)
点击(此处)折叠或打开
- postgres=# select depname, empno, salary, row_number() over() from empsalary order by salary;
- depname | empno | salary | row_number
- ----------+-------+--------+------------
- personel | 5 | 3500 | 4
- develop | 7 | 4200 | 2
- develop | 9 | 4500 | 3
- develop | 11 | 5200 | 1
- personel | 6 | 6500 | 5
- personel | 12 | 6500 | 6
- personel | 15 | 8900 | 7
- (7 行记录)
正确的写法是
点击(此处)折叠或打开
- postgres=# select depname, empno, salary, row_number() over(order by salary) from empsalary ;
- depname | empno | salary | row_number
- ----------+-------+--------+------------
- personel | 5 | 3500 | 1
- develop | 7 | 4200 | 2
- develop | 9 | 4500 | 3
- develop | 11 | 5200 | 4
- personel | 6 | 6500 | 5
- personel | 12 | 6500 | 6
- personel | 15 | 8900 | 7
- (7 行记录)
rank()
点击(此处)折叠或打开
- postgres=# select depname, empno, salary, rank() over(order by salary) from empsalary ;
- depname | empno | salary | rank
- ----------+-------+--------+------
- personel | 5 | 3500 | 1
- develop | 7 | 4200 | 2
- develop | 9 | 4500 | 3
- develop | 11 | 5200 | 4
- personel | 6 | 6500 | 5
- personel | 12 | 6500 | 5
- personel | 15 | 8900 | 7
- (7 行记录)
dense_rank()
点击(此处)折叠或打开
- postgres=# select depname, empno, salary, dense_rank() over(order by salary) from empsalary ;
- depname | empno | salary | dense_rank
- ----------+-------+--------+------------
- personel | 5 | 3500 | 1
- develop | 7 | 4200 | 2
- develop | 9 | 4500 | 3
- develop | 11 | 5200 | 4
- personel | 6 | 6500 | 5
- personel | 12 | 6500 | 5
- personel | 15 | 8900 | 6
- (7 行记录)
percent_rank() 和 cume_dist() 这两个函数不知道有什么用,不过计算的公式就在上面,看看就知道怎么算了。
ntile()
点击(此处)折叠或打开
- postgres=# select depname, empno, salary, ntile(3) over(order by salary) from empsalary ;
- depname | empno | salary | ntile
- ----------+-------+--------+-------
- personel | 5 | 3500 | 1
- develop | 7 | 4200 | 1
- develop | 9 | 4500 | 1
- develop | 11 | 5200 | 2
- personel | 6 | 6500 | 2
- personel | 12 | 6500 | 3
- personel | 15 | 8900 | 3
- (7 行记录)
lag(value any [, offsetinteger [, default any ]])
点击(此处)折叠或打开
- postgres=# select depname, empno, salary, lag(salary,1,0) over(order by salary)
- from empsalary ;
- depname | empno | salary | lag
- ----------+-------+--------+------
- personel | 5 | 3500 | 0
- develop | 7 | 4200 | 3500
- develop | 9 | 4500 | 4200
- develop | 11 | 5200 | 4500
- personel | 6 | 6500 | 5200
- personel | 12 | 6500 | 6500
- personel | 15 | 8900 | 6500
- (7 行记录)
first_value()
点击(此处)折叠或打开
- postgres=# select depname, empno, salary, first_value(salary) over () from empsa
- lary;
- depname | empno | salary | first_value
- ----------+-------+--------+-------------
- develop | 11 | 5200 | 5200
- develop | 7 | 4200 | 5200
- develop | 9 | 4500 | 5200
- personel | 5 | 3500 | 5200
- personel | 6 | 6500 | 5200
- personel | 12 | 6500 | 5200
- personel | 15 | 8900 | 5200
- (7 行记录)
last_value() 刚好和first_value()相反,取最后一列。
点击(此处)折叠或打开
- postgres=# select depname, empno, salary, last_value(salary) over () from empsal
- ary;
- depname | empno | salary | last_value
- ----------+-------+--------+------------
- develop | 11 | 5200 | 8900
- develop | 7 | 4200 | 8900
- develop | 9 | 4500 | 8900
- personel | 5 | 3500 | 8900
- personel | 6 | 6500 | 8900
- personel | 12 | 6500 | 8900
- personel | 15 | 8900 | 8900
- (7 行记录)
点击(此处)折叠或打开
- postgres=# select depname, empno, salary, last_value(salary) over (order by sala
- ry) from empsalary;
- depname | empno | salary | last_value
- ----------+-------+--------+------------
- personel | 5 | 3500 | 3500
- develop | 7 | 4200 | 4200
- develop | 9 | 4500 | 4500
- develop | 11 | 5200 | 5200
- personel | 6 | 6500 | 6500
- personel | 12 | 6500 | 6500
- personel | 15 | 8900 | 8900
- (7 行记录)
而nth_value()我想你自己从字面上就能理解了吧。这里就不讲解了。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30010989/viewspace-1350990/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/30010989/viewspace-1350990/