![](https://i-blog.csdnimg.cn/blog_migrate/85458856c557f88674ef240850c3be7f.png)
Postgres2015全国用户大会将于11月20至21日在北京丽亭华苑酒店召开。本次大会嘉宾阵容强大,国内顶级PostgreSQL数据库专家将悉数到场,并特邀欧洲、俄罗斯、日本、美国等国家和地区的数据库方面专家助阵:
- Postgres-XC项目的发起人铃木市一(SUZUKI Koichi)
- Postgres-XL的项目发起人Mason Sharp
- pgpool的作者石井达夫(Tatsuo Ishii)
- PG-Strom的作者海外浩平(Kaigai Kohei)
- Greenplum研发总监姚延栋
- 周正中(德哥), PostgreSQL中国用户会创始人之一
- 汪洋,平安科技数据库技术部经理
- ……
|
![]() |
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) |
rank 返回值在分组内的等级, 如果值有重复的话, 跳级处理.dense_rank 返回值在分组内的等级, 如果值有重复的话, 不跳级处理.percent_rank 返回 (rank - 1) / (total rows - 1), rank指当前rank值, rows指当前组的记录数 cume_dist 返回(number of rows preceding or peer with current row) / (total rows), 即截至当前记录等级一共有多少行除以本组的总行数.
postgres=# select *,row_number() over(partition by info order by id),rank() over(partition by info order by id),dense_rank() over(partition by info order by id),percent_rank() over(partition by info order by id),cume_dist() over(partition by info order by id) from test;id | info | row_number | rank | dense_rank | percent_rank | cume_dist------+-------+------------+------+------------+--------------+-------------------1 | test1 | 1 | 1 | 1 | 0 | 12 | test2 | 1 | 1 | 1 | 0 | 0.4444444444444442 | test2 | 2 | 1 | 1 | 0 | 0.4444444444444442 | test2 | 3 | 1 | 1 | 0 | 0.4444444444444442 | test2 | 4 | 1 | 1 | 0 | 0.4444444444444443 | test2 | 5 | 5 | 2 | 0.5 | 0.5555555555555564 | test2 | 6 | 6 | 3 | 0.625 | 0.6666666666666675 | test2 | 7 | 7 | 4 | 0.75 | 0.7777777777777786 | test2 | 8 | 8 | 5 | 0.875 | 0.8888888888888897 | test2 | 9 | 9 | 6 | 1 | 18 | test3 | 1 | 1 | 1 | 0 | 0.5100 | test3 | 2 | 2 | 2 | 1 | 11000 | test4 | 1 | 1 | 1 | 0 | 1(13 rows)
以info='test2'这个组为例 :2 | test2 | 1 | 1 | 1 | 0 | 0.4444444444444442 | test2 | 2 | 1 | 1 | 0 | 0.4444444444444442 | test2 | 3 | 1 | 1 | 0 | 0.4444444444444442 | test2 | 4 | 1 | 1 | 0 | 0.444444444444444id=2 的 rank和dense_rank都是1.percent_rank 返回 (rank - 1) / (total rows - 1), rank指当前rank值, rows指当前组的记录数 cume_dist 返回(number of rows preceding or peer with current row) / (total rows), 截至当前记录等级一共有多少行除以本组的总行数.所以percent_rank = (1-1)/(9-1)=0cume_dist = (4)/(9) = 0.444444444444444
3 | test2 | 5 | 5 | 2 | 0.5 | 0.555555555555556rank = 5, 跳级dense_rank = 2, 不跳级percent_rank = (5-1)/(9-1)=0.5cume_dist = (5)/(9) = 0.555555555555556
Each of the aggregates listed in Table 9-52 is associated with a window function of the same name defined in Section 9.21. In each case, the aggregate result is the value that the associated window function would have returned for the "hypothetical" row constructed from args, if such a row had been added to the sorted group of rows computed from the sorted_args.
Table 9-52. Hypothetical-Set Aggregate Functions
Function | Direct Argument Type(s) | Aggregated Argument Type(s) | Return Type | Description |
---|---|---|---|---|
rank(args) WITHIN GROUP (ORDER BY sorted_args) | VARIADIC "any" | VARIADIC "any" | bigint | rank of the hypothetical row, with gaps for duplicate rows |
dense_rank(args) WITHIN GROUP (ORDER BY sorted_args) | VARIADIC "any" | VARIADIC "any" | bigint | rank of the hypothetical row, without gaps |
percent_rank(args) WITHIN GROUP (ORDER BYsorted_args) | VARIADIC "any" | VARIADIC "any" | double precision | relative rank of the hypothetical row, ranging from 0 to 1 |
cume_dist(args) WITHIN GROUP (ORDER BY sorted_args) | VARIADIC "any" | VARIADIC "any" | double precision | relative rank of the hypothetical row, ranging from 1/N to 1 |
For each of these hypothetical-set aggregates, the list of direct arguments given in args must match the number and types of the aggregated arguments given in sorted_args. Unlike most built-in aggregates, these aggregates are not strict, that is they do not drop input rows containing nulls. Null values sort according to the rule specified in the ORDER BY clause.
12345
postgres=# select * from test order by info,id;id | info------+-------1 | test12 | test22 | test22 | test22 | test23 | test24 | test25 | test26 | test27 | test28 | test3100 | test31000 | test4(13 rows)
postgres=# select info,rank(4.9) within group (order by id),dense_rank(4.9) within group (order by id) from test group by info;info | rank | dense_rank-------+------+------------test1 | 2 | 2test2 | 7 | 4test3 | 1 | 1test4 | 1 | 1(4 rows)
postgres=# select info,rank(5) within group (order by id),dense_rank(5) within group (order by id) from test group by info;info | rank | dense_rank-------+------+------------test1 | 2 | 2test2 | 7 | 4test3 | 1 | 1test4 | 1 | 1(4 rows)postgres=# select info,rank(5.1) within group (order by id),dense_rank(5.1) within group (order by id) from test group by info;info | rank | dense_rank-------+------+------------test1 | 2 | 2test2 | 8 | 5test3 | 1 | 1test4 | 1 | 1(4 rows)
postgres=# select info,rank(4.9) within group (order by id),dense_rank(4.9) within group (order by id),percent_rank(4.9) within group (order by id),cume_dist(4.9) within group (order by id) from test group by info;info | rank | dense_rank | percent_rank | cume_dist-------+------+------------+-------------------+-------------------test1 | 2 | 2 | 1 | 1test2 | 7 | 4 | 0.666666666666667 | 0.7test3 | 1 | 1 | 0 | 0.333333333333333test4 | 1 | 1 | 0 | 0.5(4 rows)
2 | test22 | test22 | test22 | test23 | test24 | test24.9 | test2 # 计算位置5 | test26 | test27 | test2
postgres=# select info,rank(5) within group (order by id),dense_rank(5) within group (order by id),percent_rank(5) within group (order by id),cume_dist(5) within group (order by id) from test group by info;info | rank | dense_rank | percent_rank | cume_dist-------+------+------------+-------------------+-------------------test1 | 2 | 2 | 1 | 1test2 | 7 | 4 | 0.666666666666667 | 0.8test3 | 1 | 1 | 0 | 0.333333333333333test4 | 1 | 1 | 0 | 0.5(4 rows)
插入计算值5后, 数据变成2 | test22 | test22 | test22 | test23 | test24 | test25 | test2 # 计算位置, 即参数值5 | test26 | test27 | test2
postgres=# select info,rank(5.1) within group (order by id),dense_rank(5.1) within group (order by id),percent_rank(5.1) within group (order by id),cume_dist(5.1) within group (order by id) from test group by info;info | rank | dense_rank | percent_rank | cume_dist-------+------+------------+-------------------+-------------------test1 | 2 | 2 | 1 | 1test2 | 8 | 5 | 0.777777777777778 | 0.8test3 | 1 | 1 | 0 | 0.333333333333333test4 | 1 | 1 | 0 | 0.5(4 rows)
插入计算值5.1后, 数据变成 :2 | test22 | test22 | test22 | test23 | test24 | test25 | test25.1 | test2 # 计算位置, 即参数值6 | test27 | test2
postgres=# select info,rank(5) within group (order by id desc),dense_rank(5) within group (order by id desc),percent_rank(5) within group (order by id desc),cume_dist(5) within group (order by id desc) from test group by info;info | rank | dense_rank | percent_rank | cume_dist-------+------+------------+-------------------+-----------test1 | 1 | 1 | 0 | 0.5test2 | 3 | 3 | 0.222222222222222 | 0.4test3 | 3 | 3 | 1 | 1test4 | 2 | 2 | 1 | 1(4 rows)
7 | test26 | test25 | test2 # 注意, 这才是计算位置, 即插入位置.5 | test24 | test23 | test22 | test22 | test22 | test22 | test2