I would like to create an average of individual moving averages per team. The moving average of each player will be their own and not dependent on what team they were on that day (See Example).
I have a good understanding of how to do a moving average of just an individual player, but not how to combine multiple that occur in different rows.
One idea I had was merging every row of each team under one row first. However, that does not seem like the most ideal way. Can I partition over two columns to accomplish this?
Bonus question: is there a potential to weigh the players differently in their individual moving average depending on stat B (Example 2)?
For example: Team A average = AVG(MA_statA_player1, MA_statA_player2, & MA_statA_player3) Example 2: Team A average = AVG(MA_player1stat_b, MA_player2stat_b, & MA_player3*stat_b)
I have data like below:
| Team | ID | date | stat A | stat B |
|---|---|---|---|---|
| 1 | player1 | 5-31-2022 | 2.5 | 0.1 |
| 1 | player2 | 5-31-2022 | 2.9 | 0.5 |
| 1 | player3 | 5-31-2022 | 5 | 0.3 |
| 2 | player10 | 5-31-2022 | 6 | 0.75 |
| 2 | player12 | 5-31-2022 | 2.5 | 0.2 |
| 3 | player10 | 6-01-2022 | 2.5 | 0.12 |
| 3 | player2 | 6-01-2022 | 2.5 | 0.85 |
Example Expected Data; Each row is made up of a team with a date and a moving average of the team. The individual moving averages do not need to be there but are to show how the average team is generated. No Weight_ Average_team = (ma_playerX + ma_playerY)/2
| Team | date | ma_playX | ma_playerY | Average_team |
|---|---|---|---|---|
| 1 | 5-31-202 | 3.2 | 2.5 | 2.85 |
| 2 | 5-31-2022 | 5.6 | 2.9 | 4.25 |
| 3 | 6-01-2022 | 2.5 | 5 | 2.25 |
AVG(stat_A) OVER (PARTITION BY player ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)+ 2 AS avg7games