RATIO_TO_REPORT是一个分析函数,用于计算某一列的值在指定分组中的所占的比率。

语法

RATIO_TO_REPORT(col) over ([partition by xxx]) ; 
  • col:需要查询的列,如果该值为空,则比率的值也为空。
  • [partition by xxx]:指定的分组,如果省略该子句,则会计算当前值占指定分组中所有值的比率。

示例

select *, ratio_to_report(b) over () from rtp;
 a | b | c  |    ratio_to_report     
---+---+----+------------------------
 1 | 5 |  4 | 0.25000000000000000000
 1 | 5 |  6 | 0.25000000000000000000
 2 | 3 | 10 | 0.15000000000000000000
 2 | 7 |    | 0.35000000000000000000
(4 rows)

select *, ratio_to_report(b) over (partition by a) from rtp;
 a | b | c  |    ratio_to_report     
---+---+----+------------------------
 1 | 5 |  4 | 0.50000000000000000000
 1 | 5 |  6 | 0.50000000000000000000
 2 | 3 | 10 | 0.30000000000000000000
 2 | 7 |    | 0.70000000000000000000
(4 rows)