PARTITION BY a column
The PARTITION BY clause allows you to calculate separate "windows" based on columns you want to divide your results. For example, you can create a single column that calculates an overall average of goals scored for each season.
In this exercise, you will be creating a data set of games played by Legia Warszawa (Warsaw League), the top ranked team in Poland, and comparing their individual game performance to the overall average for that season.
Where do you see more outliers? Are they Legia Warszawa's home or away games?
Deze oefening maakt deel uit van de cursus
Data Manipulation in SQL
Oefeninstructies
- Complete the two window functions that calculate the home and away goal averages. Partition the window functions by season to calculate separate averages for each season.
- Filter the query to only include matches played by Legia Warszawa,
id = 8673.
Praktische interactieve oefening
Probeer deze oefening eens door deze voorbeeldcode in te vullen.
SELECT
date,
season,
home_goal,
away_goal,
CASE WHEN hometeam_id = 8673 THEN 'home'
ELSE 'away' END AS warsaw_location,
-- Calculate separately the average home and away goals scored, partitioned by season
___(___) ___(___ ___ ___) AS season_homeavg,
___(___) ___(___ ___ ___) AS season_awayavg
FROM match
-- Filter the data set for Legia Warszawa (id 8673) matches only
WHERE
___ = ___
OR ___ = ___
ORDER BY (home_goal + away_goal) DESC;