RATIO_TO_REPORT is an analytic function. This function computes the ratio of a value in a column to the sum of values in a specified partition.
Syntax
RATIO_TO_REPORT(col) over ([partition by xxx]) ;
col
: specifies the column whose values are to be calculated. If you do not specify the col parameter, RATIO_TO_REPORT returns no value.[partition by xxx]
: specifies a partition. If you omit this clause, RATIO_TO_REPORT computes the ratio of a value in the column to the sum of all the values in the column.
Examples
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)