- Categories:
Window Functions (General)
RATIO_TO_REPORT¶
Returns the ratio of a value within a group to the sum of the values within the group. If expr1
evaluates
to null or the sum of expr1
within the group evaluates to 0, then RATIO_TO_REPORT returns null.
Syntax¶
RATIO_TO_REPORT( <expr1> ) [ OVER ( [ PARTITION BY <expr2> ] [ ORDER BY <expr3> ] ) ]
Arguments¶
expr1
This is an expression that evaluates to a numeric data type (INTEGER, FLOAT, DECIMAL, etc.).
expr2
This is the optional expression to partition by.
expr3
This is the optional expression to order by within each partition. Note that for this function, the order within the partition does not affect the output.
In this function, as in all window functions, this ORDER BY does not control the order of the entire query output.
Usage notes¶
RATIO_TO_REPORT is calculated as:
value of
expr1
argument for the current row / sum ofexpr1
argument for the partitionThe ORDER BY sub-clause in the OVER() clause is allowed in this function for syntactic consistency with other window functions, but the ORDER BY does not affect the calculation. Snowflake recommends not including an ORDER BY sub-clause in the OVER clause when using this function.
Examples¶
This simple example shows the percentage of a store chain’s profit that was generated by each individual store:
CREATE TABLE store_profit ( store_ID INTEGER, province VARCHAR, profit NUMERIC(11, 2)); INSERT INTO store_profit (store_ID, province, profit) VALUES (1, 'Ontario', 300), (2, 'Saskatchewan', 250), (3, 'Ontario', 450), (4, 'Ontario', NULL) -- hasn't opened yet, so no profit yet. ;SELECT store_ID, profit, 100 * RATIO_TO_REPORT(profit) OVER () AS percent_profit FROM store_profit ORDER BY store_ID; +----------+--------+----------------+ | STORE_ID | PROFIT | PERCENT_PROFIT | |----------+--------+----------------| | 1 | 300.00 | 30.00000000 | | 2 | 250.00 | 25.00000000 | | 3 | 450.00 | 45.00000000 | | 4 | NULL | NULL | +----------+--------+----------------+
This shows the percentage of profit within each province generated by each store in that province:
SELECT province, store_ID, profit, 100 * RATIO_TO_REPORT(profit) OVER (PARTITION BY province) AS percent_profit FROM store_profit ORDER BY province, store_ID; +--------------+----------+--------+----------------+ | PROVINCE | STORE_ID | PROFIT | PERCENT_PROFIT | |--------------+----------+--------+----------------| | Ontario | 1 | 300.00 | 40.00000000 | | Ontario | 3 | 450.00 | 60.00000000 | | Ontario | 4 | NULL | NULL | | Saskatchewan | 2 | 250.00 | 100.00000000 | +--------------+----------+--------+----------------+