RATIO_TO_REPORT SQL analytic function can be used to calculate percentage of the entire dataset or partitioned data. RATIO_TO_REPORT returns a value between 0 and 1 which indicates the weight of a numerical value in respect to the sum of all numerical values in the same partition. If multiplied by 100, the value of ratio_to_report can be interpreted as percentage.

An illustrative example

Let’s look at the dataset we will be using for this tutorial:

select
    player_id,
    team,
    position,
    goals,
from soccer_players

Copy of Copy of favicon

Our data is about a soccer game where each row represents a player id, with columns for id, their team, and how many goals they have scored.

If we wanted to work out what percentage of all goal each player has scored, we can use ratio_to_report() window function as following:

select
    player_id,
    team,
    position,
    goals,
    ratio_to_report(goals) over () as overall_goals_percentage
from soccer_players
order by team, position;

Copy of Copy of Copy of favicon

The over() in the query above tells the database to expand the window in which the ratio_to_report() operates to the whole dataset.

The ratio_to_report() window function takes an expression (usually a column) as input and calculates the ratio of that expression to the window that is defined (in this case, the whole dataset).

Using ratio_to_report() to Calculate Partitioned Percentages

Let’s say we were not only interested in each players overall contribution to goals, but also their contribution to goals for their specific team. We can then add a partition by to the window:

select
    player_id,
    team,
    position,
    goals,
    ratio_to_report(goals) over (team) as overall_goals_percentage
from soccer_players
order by team, position;

Copy of favicon

See also: