Menu
Amazon Redshift
Database Developer Guide (API Version 2012-12-01)

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:

Copy
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.

Syntax

Copy
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.

Return Type

FLOAT8

Examples

See RATIO_TO_REPORT Window Function Examples.