I am trying to calculate a moving average for a team score, but this should include both the points scored when it is the away team and home team.
For example, I have the following table Match
:
away_team | home_team | Away_score | home_score |
---|---|---|---|
DET | BKN | 106 | 92 |
CHI | DAL | 99 | 119 |
OKC | DEN | 122 | 124 |
MIN | CHI | 135 | 119 |
UTA | CHI | 135 | 126 |
I can use a window function to get the moving average of the home_scores
of e.g. CHI, but what I want to calculate is the average of all the scores of CHI, so also the 99 points in the second row.
What I used to calculate the moving average was:
SELECT
*,
AVG(home_score) OVER (PARTITION BY home_team
ORDER BY match_date
ROWS BETWEEN 10 PRECEDING AND CURRENT ROW) AS avgscore
FROM
Match
However I need to include the average of Away_score for rows where the away_team is equal to the home_team in the current row (in my example where the away team is CHI).
So I would expect the output to be 114.67 (being the average of 99 + 119 + 126). I am currently only getting the average of the two home_scores, i.e. 122.5 (average of 119 and 126).
Consider reformatting data to long format with UNION ALL
of the home and away team sets and then calculate the running average by team across match dates:
WITH combn AS (
SELECT
match_date,
'away' AS team_type,
away_team AS team,
away_score AS score,
CASE
WHEN away_score > home_score THEN 'W'
WHEN away_score < home_score THEN 'L'
ELSE 'T'
END AS result
FROM Match
UNION ALL
SELECT
match_date,
'home' AS team_type,
home_team AS team,
home_score AS score,
CASE
WHEN home_score > away_score THEN 'W'
WHEN home_score < away_score THEN 'L'
ELSE 'T'
END AS result
FROM Match
)
SELECT
match_date,
team_type,
team,
score,
result,
AVG(score) OVER (
PARTITION BY team
ORDER BY match_date
ROWS BETWEEN 10 PRECEDING AND CURRENT ROW
) AS run_avg_score
FROM combn
ORDER BY team, match_date