Digoal.zhou

Digoal.zhou

公益是一辈子的事, I'm digoal, Just do it.

PostgreSQL aggregate function 4 : Hypothetical-Set Aggregate Functions

7 minute read

背景

本文讲一下聚合函数的最后一个分类, Hypothetical-Set Aggregate Functions.

这类聚合函数还有对应的窗口函数, 首先来看一下对应窗口函数的用法.

函数 返回类型 描述
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)

the four ranking functions are defined so that they give the same answer for any two peer 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 |                 1  
    2 | test2 |          1 |    1 |          1 |            0 | 0.444444444444444  
    2 | test2 |          2 |    1 |          1 |            0 | 0.444444444444444  
    2 | test2 |          3 |    1 |          1 |            0 | 0.444444444444444  
    2 | test2 |          4 |    1 |          1 |            0 | 0.444444444444444  
    3 | test2 |          5 |    5 |          2 |          0.5 | 0.555555555555556  
    4 | test2 |          6 |    6 |          3 |        0.625 | 0.666666666666667  
    5 | test2 |          7 |    7 |          4 |         0.75 | 0.777777777777778  
    6 | test2 |          8 |    8 |          5 |        0.875 | 0.888888888888889  
    7 | test2 |          9 |    9 |          6 |            1 |                 1  
    8 | test3 |          1 |    1 |          1 |            0 |               0.5  
  100 | test3 |          2 |    2 |          2 |            1 |                 1  
 1000 | test4 |          1 |    1 |          1 |            0 |                 1  
(13 rows)

算法 :

以info=’test2’这个组为例 :

    2 | test2 |          1 |    1 |          1 |            0 | 0.444444444444444  
    2 | test2 |          2 |    1 |          1 |            0 | 0.444444444444444  
    2 | test2 |          3 |    1 |          1 |            0 | 0.444444444444444  
    2 | test2 |          4 |    1 |          1 |            0 | 0.444444444444444

id=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)=0  
cume_dist = (4)/(9) = 0.444444444444444  

    3 | test2 |          5 |    5 |          2 |          0.5 | 0.555555555555556

rank = 5, 跳级

dense_rank = 2, 不跳级

percent_rank = (5-1)/(9-1)=0.5  
cume_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.

这些用法比较奇特, 其实是要返回给定参数在集合中的位置.

例如 :

1  
2  
3  
4  
5

如果我们给一个参数值是2.2, 应该排在以上数据中的第三行.

例子 :

postgres=# select * from test order by info,id;  
  id  | info    
------+-------  
    1 | test1  
    2 | test2  
    2 | test2  
    2 | test2  
    2 | test2  
    3 | test2  
    4 | test2  
    5 | test2  
    6 | test2  
    7 | test2  
    8 | test3  
  100 | test3  
 1000 | 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 |          2  
 test2 |    7 |          4  
 test3 |    1 |          1  
 test4 |    1 |          1  
(4 rows)

4.9在test1这个分组, 排名第2, 并且这个分组只有1个值, 所以没有gap.

重点关注test2这个组, 这个组有9个值, 其中有4个重复值2, 所以4.9在这里排名需要考虑gap.

rank 返回7, 即4.9在这里考虑GAP排名第7

dense_rank 返回4, 即4.9在这里不考虑GAP排名第4.

又如 :

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 |          2  
 test2 |    7 |          4  
 test3 |    1 |          1  
 test4 |    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 |          2  
 test2 |    8 |          5  
 test3 |    1 |          1  
 test4 |    1 |          1  
(4 rows)

最后要看计算0~1代表位置的聚合函数percent_rank和cume_dist.

算法

percent_rank 返回 (rank - 1) / (total rows - 1), rank指当前rank值, rows指当前组的记录数

cume_dist 返回(number of rows preceding or peer with current row) / (total rows), 截至当前记录等级一共有多少行除以本组的总行数.

例子1 :

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 |                 1  
 test2 |    7 |          4 | 0.666666666666667 |               0.7  
 test3 |    1 |          1 |                 0 | 0.333333333333333  
 test4 |    1 |          1 |                 0 |               0.5  
(4 rows)

同样以test2为分组, 讲解算法. 把4.9插入到这个分组后. 数据应该变成 :

    2 | test2  
    2 | test2  
    2 | test2  
    2 | test2  
    3 | test2  
    4 | test2  
    4.9 | test2  # 计算位置  
    5 | test2  
    6 | test2  
    7 | test2

一共10行.

percent_rank 返回 (rank - 1) / (total rows - 1), rank指当前rank值, rows指当前组的记录数

cume_dist 返回(number of rows preceding or peer with current row) / (total rows), 截至当前记录等级一共有多少行除以本组的总行数.

所以4.9对应的percent_rank 和 cume_dist 分别为 :

percent_rank = (rank - 1) / (total rows - 1) = (7-1)/(10-1) = 0.666666666666667   
cume_dist = (7)/10 = 0.7

例子2 :

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 |                 1  
 test2 |    7 |          4 | 0.666666666666667 |               0.8  
 test3 |    1 |          1 |                 0 | 0.333333333333333  
 test4 |    1 |          1 |                 0 |               0.5  
(4 rows)

插入计算值5后, 数据变成

    2 | test2  
    2 | test2  
    2 | test2  
    2 | test2  
    3 | test2  
    4 | test2  
    5 | test2  # 计算位置, 即参数值  
    5 | test2  
    6 | test2  
    7 | test2

依旧10行. 但是截至当前记录等级一共有多少行? 注意是8了.

percent_rank = (rank - 1) / (total rows - 1) = (7-1)/(10-1) = 0.666666666666667   
cume_dist = (8)/10 = 0.8

例子3 :

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 |                 1  
 test2 |    8 |          5 | 0.777777777777778 |               0.8  
 test3 |    1 |          1 |                 0 | 0.333333333333333  
 test4 |    1 |          1 |                 0 |               0.5  
(4 rows)

插入计算值5.1后, 数据变成 :

    2 | test2  
    2 | test2  
    2 | test2  
    2 | test2  
    3 | test2  
    4 | test2  
    5 | test2  
    5.1 | test2  # 计算位置, 即参数值  
    6 | test2  
    7 | test2

结果自己验证吧.

例子4 :

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.5  
 test2 |    3 |          3 | 0.222222222222222 |       0.4  
 test3 |    3 |          3 |                 1 |         1  
 test4 |    2 |          2 |                 1 |         1  
(4 rows)

插入计算值5后, 数据变成 :

    7 | test2  
    6 | test2  
    5 | test2  # 注意, 这才是计算位置, 即插入位置.  
    5 | test2  
    4 | test2  
    3 | test2  
    2 | test2  
    2 | test2  
    2 | test2  
    2 | test2

结果自己验证吧.

参考

1. 《PostgreSQL aggregate function 1 : General-Purpose Aggregate Functions》

2. 《PostgreSQL aggregate function 2 : Aggregate Functions for Statistics》

3. 《PostgreSQL aggregate function 3 : Aggregate Functions for Ordered-Set》

4. http://www.postgresql.org/docs/devel/static/functions-window.html

5. http://www.postgresql.org/docs/devel/static/functions-aggregate.html

Flag Counter

digoal’s 大量PostgreSQL文章入口

You May Also Enjoy

PostgreSQL(PPAS 兼容Oracle) 从零开始入门手册 - 珍藏版

17 minute read

背景 云数据库PPAS版，是阿里云与EnterpriseDB公司(简称EDB)合作基于PostgreSQL高度兼容Oracle语法的数据库服务，为用户提供易于操作的迁移工具，兼容范围涵盖：PL/SQL、数据类型、高级函数、表分区等。 用户可以直接在阿里云购买PPAS进行使用。 如果在购买PPAS前，想试用一下...

PostgreSQL pipelinedb 流计算插件 - IoT应用 - 实时轨迹聚合

1 minute read

背景 IoT场景，车联网场景，共享单车场景，人的行为位点等，终端实时上报的是孤立的位点，我们需要将其补齐成轨迹。 例如共享单车，下单，开锁，生成订单，骑行，关闭订单，关锁。这个过程有一个唯一的订单号，每次上报的位点会包含时间，订单号，位置。 根据订单号，将点聚合为轨迹。 使用pipelinedb插件，可以实...

PostgreSQL datediff 日期间隔（单位转换）兼容SQL用法

2 minute read

背景 使用datediff，对时间或日期相减，得到的间隔，转换为目标单位(日、月、季度、年、小时、秒。。。等)的数值。 DATEDIFF ( datepart, {date|timestamp}, {date|timestamp} ) 周 select datediff(week,'2009-01-0...