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> ] ) ]
Copy

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 clause within the OVER clause is allowed in this function for syntactic consistency with other window functions but does not affect the calculation. Snowflake recommends not including the ORDER BY 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.
    ;
Copy
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 |
+----------+--------+----------------+
Copy

This example shows the percentage of profit within each province that was 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 |
+--------------+----------+--------+----------------+
Copy
Language: English