Ratio_to_report postgresql

RATIO_TO_REPORT in PostgreSQL

The RATIO_TO_REPORT function in PostgreSQL is used to calculate the ratio of a specific value to the total of a set of values. It returns the percentage of the specified value as compared to the total. This function is particularly useful when you want to calculate the contribution of a value within a group.

Syntax:

RATIO_TO_REPORT(expression) OVER (PARTITION BY column1, column2, …)

Parameters:

• expression: The value for which ratio needs to be calculated.
• OVER: Specifies the window or partition within which the calculation needs to be performed. It is optional and used when you want to calculate the ratio within a specific group.
• PARTITION BY: Specifies the columns that define the groups within which the ratio is calculated. It is optional and used when you want to calculate the ratio within a specific group.

Example:

Consider a table called “sales” with the following data:

To calculate the ratio of each product’s quantity to the total quantity, you can use the following query:

    SELECT product, quantity, RATIO_TO_REPORT(quantity) OVER () AS ratio
    FROM sales;
  

The query will return the following result:

In this example, the RATIO_TO_REPORT function calculates the ratio of each product’s quantity to the total quantity. The ratio is expressed as a decimal value between 0 and 1, representing the percentage contribution of each product’s quantity to the total.