Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://docs.snowflake.com/en/sql-reference/functions/ratio_to_report below:

Website Navigation

• Reference
• Function and stored procedure reference
• Window

RATIO_TO_REPORT | Snowflake Documentation

Categories:

Window functions (General)

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.

RATIO_TO_REPORT( <expr1> ) [ OVER ( [ PARTITION BY <expr2> ] [ ORDER BY <expr3> ] ) ]

Copy

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.

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

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

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4