OVER and PARTITION BY

• Calculate separate values for different categories
• Calculate different calculations in the same column

AVG(home_goal) OVER(PARTITION BY season)

This will then return the overall average for, or PARTITION BY each season

Partition your data

• How many goals were scored in each match, and how did that compare to the overall average?

SELECT 
    date,
    (home_goal + away_goal) AS goals,
    AVG(home_goal + away_goal) OVER() AS oversall_avg
FROM match;

• How many goals were scored in each match, and how did that compare to the season’s average?

SELECT
    date,
    (home_goal + away_goal) AS goals,
    AVG(home_goal + away_goal) OVER(PARTITION BY season) AS season_avg
FROM match;

PARTITION by Multiple Columns

SELECT
    c.name,
    m.season,
    (home_goal + away_goal) AS goals,
    AVG(home_goal + away_goal)
        OVER(PARTITION BY m.season, c.name) AS season_ctry_avg
FROM country AS c
LEFT JOIN match AS m
ON c.id = m.country_id

The result set returns the average goals scored broken out by season and country

PARTITION BY considerations

• Can partition data by 1 or more columns
• Can partition aggregate calculations, ranks, etc