RATIO_TO_REPORT is an analytic function that computes the ratio of a value to the sum of values in a partition.
Syntax
RATIO_TO_REPORT(col) OVER ([PARTITION BY expr])Parameters
| Parameter | Description |
|---|---|
col |
The column whose values are used in the calculation. If col is not specified, the function returns no value. |
PARTITION BY expr |
Divides the result set into partitions. If omitted, the function treats the entire result set as a single partition. |
Examples
The following examples use a sample table to show how RATIO_TO_REPORT calculates value ratios with and without partitioning.
CREATE TABLE rtp (
a INT,
b INT,
c INT
);
INSERT INTO rtp (a, b, c) VALUES
(1, 5, 4),
(1, 5, 6),
(2, 3, 10),
(2, 7, NULL);Calculate the ratio of each row to the total
The following query calculates the ratio of the value in column b of each row to the sum of all values in column b.
SELECT *, ratio_to_report(b) OVER () FROM rtp;Result:
a | b | c | ratio_to_report
---+---+----+-----------------
1 | 5 | 4 | 0.25
1 | 5 | 6 | 0.25
2 | 3 | 10 | 0.15
2 | 7 | | 0.35
(4 rows)Calculate the ratio of each row within a partition
The following query partitions the table by column a and calculates the ratio of the value in column b of each row to the sum of all values in column b within the same partition.
SELECT *, ratio_to_report(b) OVER (PARTITION BY a) FROM rtp;Result:
a | b | c | ratio_to_report
---+---+----+-----------------
1 | 5 | 4 | 0.5
1 | 5 | 6 | 0.5
2 | 3 | 10 | 0.3
2 | 7 | | 0.7
(4 rows)