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, 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 of expr1 argument for the partition

  • The 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 |
+--------------+----------+--------+----------------+