平均电影时长的流派总计和 moving average 是多少？ -- （注意：您需要在问题中显示输出 table。）
/* 输出格式： -
| genre | avg_duration | running_avg_duration | moving_avg_duration |
|---|---|---|---|
| comdy | 145 | 102.44 | 134 |
| . | . | . | . |
| . | . | . | . |
类型 table：- 电影 ID、类型
电影 table: - id, title, year, date_published, duration, country, worldwide_gross_income, languages, production_company
回答1
SELECT genre,
ROUND(AVG(duration),2) AS avg_duration,
SUM(ROUND(AVG(duration),2)) OVER(ORDER BY genre ROWS UNBOUNDED
PRECEDING) AS running_total_duration,
AVG(ROUND(AVG(duration),2)) OVER(ORDER BY genre ROWS 10 PRECEDING)
AS moving_avg_duration
FROM movie AS m
INNER JOIN genre AS g
ON m.id= g.movie_id
GROUP BY genre
ORDER BY genre;