RATIO_TO_REPORT window function - Amazon Redshift

# RATIO_TO_REPORT window function

Calculates the ratio of a value to the sum of the values in a window or partition. The ratio to report value is determined using the formula:

`value of `ratio_expression `argument for the current row / sum of` ratio_expression `argument for the window or partition`

The following dataset illustrates use of this formula:

``````Row#	Value	Calculation	RATIO_TO_REPORT
1	2500	(2500)/(13900)	0.1798
2	2600	(2600)/(13900)	0.1870
3	2800	(2800)/(13900)	0.2014
4	2900	(2900)/(13900)	0.2086
5	3100	(3100)/(13900)	0.2230``````

The return value range is 0 to 1, inclusive. If ratio_expression is NULL, then the return value is `NULL`. If a value in partition_expression is unique, then function will return `1` for that value.

## Syntax

``````RATIO_TO_REPORT ( ratio_expression )
OVER ( [ PARTITION BY partition_expression ] )``````

## Arguments

ratio_expression

An expression, such as a column name, that provides the value for which to determine the ratio. The expression must have either a numeric data type or be implicitly convertible to one.

You cannot use any other analytic function in ratio_expression.

OVER

A clause that specifies the window partitioning. The OVER clause cannot contain a window ordering or window frame specification.

PARTITION BY partition_expression

Optional. An expression that sets the range of records for each group in the OVER clause.

FLOAT8

## Examples

The following examples use the WINSALES table. For a information about how to create the WINSALES table, see Sample table for window function examples.

The following example calculates the ratio-to-report value of each row of a seller's quantity to the total of all seller's quantities.

``````select sellerid, qty, ratio_to_report(qty)
over()
from winsales
order by sellerid;

sellerid  qty    ratio_to_report
--------------------------------------
1         30     0.13953488372093023
1         10     0.046511627906976744
1         10     0.046511627906976744
2         20     0.09302325581395349
2         20     0.09302325581395349
3         30     0.13953488372093023
3         20     0.09302325581395349
3         15     0.06976744186046512
3         10     0.046511627906976744
4         10     0.046511627906976744
4         40     0.18604651162790697
``````

The following example calculates the ratios of the sales quantities for each seller by partition.

``````select sellerid, qty, ratio_to_report(qty)
over(partition by sellerid)
from winsales;

sellerid   qty    ratio_to_report
-------------------------------------------
2          20     0.5
2          20     0.5
4          40     0.8
4          10     0.2
1          10     0.2
1          30     0.6
1          10     0.2
3          10     0.13333333333333333
3          15     0.2
3          20     0.26666666666666666
3          30     0.4
``````