RATIO_TO_REPORT¶

Syntax¶

RATIO_TO_REPORT( <expr1> ) [ OVER ( [ PARTITION BY <expr2> ]
  [ ORDER BY <expr3> ] [ { ASC | DESC } ] [ NULLS { FIRST | LAST } ] ) ]

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

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