Mysql窗口函数之组内SUM、AVG操作

-- 测试数据参考 https://www.cnblogs.com/thxj/p/12727589.html
-- 按照学科分组,统计组内平均分数和总分数
SELECT
    `name`,
    `course`,
    `score`,
    SUM( score ) over ( PARTITION BY `course` ) AS course_score_total ,
    ROUND(AVG(score) over (PARTITION BY `course`),2)  AS  course_score_avg
FROM
   `school_score`;
-- 执行结果
name    course    score  course_score_total  course_score_avg  
------  -------  ------  ------------------  ------------------
A       Chinese      80                 240               80.00
B       Chinese      90                 240               80.00
C       Chinese      70                 240               80.00
A       English      90                 274               91.33
B       English      85                 274               91.33
C       English      99                 274               91.33
A       Math         70                 250               83.33
B       Math        100                 250               83.33
C       Math         80                 250               83.33