sqlsqlitepartitionmoving-average

How to calculate moving average over two columns in SQL


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


Solution

  • 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