Mysql窗口函数之组内逐行连续SUM、AVG操作

最新推荐文章于 2024-09-04 20:51:50 发布

原创最新推荐文章于 2024-09-04 20:51:50 发布 · 879 阅读

1 ·

CC 4.0 BY-SA版权

mysql 专栏收录该内容

3 篇文章

订阅专栏

本文详细介绍了一种使用SQL进行分数累加查询的方法，通过PARTITION BY和ORDER BY结合窗口函数SUM实现按学科分组并逐行连续累加分数的功能。展示了如何根据不同排序规则（升序或降序）获取不同结果，对于理解SQL窗口函数的应用场景非常有帮助。

-- 测试数据 参考 https://www.cnblogs.com/thxj/p/12727589.html
CREATE TABLE `school_score` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(1) DEFAULT NULL,
    `course` char(10) DEFAULT NULL,
  `score`  int (2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ;
CREATE TABLE `school_score` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(1) DEFAULT NULL,
    `course` char(10) DEFAULT NULL,
  `score`  int (2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ;

INSERT INTO `school_score` VALUES (1, 'A','Chinese',80),(2, 'B','Chinese',90),
(3, 'C','Chinese',70),(4, 'A','Math',70),(5, 'B','Math',100),(6, 'C','Math',80),
(7, 'A','English',90),(8, 'B','English',85),(9, 'C','English',99);
-- 按学科分组并且逐行连续累加分数
SELECT
    `name`,
    `course`,
    `score`,
    SUM(score) over (PARTITION BY `course` ORDER BY score DESC ) AS course_score_total
FROM
`school_score`;
-- 执行结果
name    course    score  course_score_total  
------  -------  ------  --------------------
B       Chinese      90                    90
A       Chinese      80                   170
C       Chinese      70                   240
C       English      99                    99
A       English      90                   189
B       English      85                   274
B       Math        100                   100
C       Math         80                   180
A       Math         70                   250

注意:这里的ORDER BY score很关键,如果没有加ORDER BY结果就是组内SUM,显然这并不是我们想要的,并且ORDER BY的规则不一样每行聚合的结果可能会不一样,但最终组内聚合的结果一样
例如:

SELECT
    `name`,
    `course`,
    `score`,
    SUM(score) over (PARTITION BY `course` ORDER BY score ASC/*这里升序*/) AS course_score_total
FROM
`school_score`;
-- 执行结果,注意和上面的不同
name    course    score  course_score_total  
------  -------  ------  --------------------
C       Chinese      70                    70
A       Chinese      80                   150
B       Chinese      90                   240
B       English      85                    85
A       English      90                   175
C       English      99                   274
A       Math         70                    70
C       Math         80                   150
B       Math        100                   250