PolarDB:Analytic function RATIO_TO_REPORT

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

1. Create the test table.

   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);

2. Use the RATIO_TO_REPORT function.

   • Calculate 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;

     Sample 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)

   • Partition the table by column a and calculate the ratio of the value in column b of each row to the sum of all values in column b in the same partition.

     SELECT *, ratio_to_report(b) OVER (PARTITION BY a) FROM rtp;

     Sample 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)