如何计算MySQL中每个组的运行总和 [英] How to calculate running sum for each group in MySQL

问题描述

直接求和我可以用 sum() 函数做.但我这里有不同的情况.我有一个只有 2 个字段的表 Student.例如，假设整个班级只有 1 个学生:

Straightforward summation I can do with sum() function. But I've a different situation here. I've a table Student with only 2 fields. For instance just assume that there is only 1 student in the entire class:

CREATE TABLE student
    (`dateOfExam` date, score int)
;
    
INSERT INTO student
    (`dateOfExam`, `score`)
VALUES
    ('2020-05-28',5),
    ('2020-05-29',5),
    ('2020-05-30',10),
    ('2020-06-03',10),
    ('2020-06-05',5),
    ('2020-07-21',20),
    ('2020-07-22',10),
    ('2020-07-28',10)
;

我有他参加考试的日子的分数，并在运行时多一列，即举行考试的月份:

And I have his scores for the days when exams were taken with one more column in the runtime which is the month in which exam was held:

查询是(昨天从stackoverflow得到帮助):

The query is (took help from stackoverflow yesterday):

select date_format(dateOfExam, '%Y-%m') ExamMonth
     , dateOfExam
     , score 
  from student;

结果:

+-----------+------------+-------+
| ExamMonth | dateOfExam | score |
+-----------+------------+-------+
| 2020-05   | 2020-05-28 |     5 |
| 2020-05   | 2020-05-29 |     5 |
| 2020-05   | 2020-05-30 |    10 |
| 2020-06   | 2020-06-03 |    10 |
| 2020-06   | 2020-06-05 |     5 |
| 2020-07   | 2020-07-21 |    20 |
| 2020-07   | 2020-07-22 |    10 |
| 2020-07   | 2020-07-28 |    10 |
+-----------+------------+-------+

我的要求是每个月都要奖励这个学生.我会继续把他每个月的每个日期的分数相加，当累积分数总和达到 10 时给他Reward1，当累积分数总和达到 20 时给他Reward2.所以决赛桌应该是这样的:

My requirement is I want to reward this student every month. I'll keep on adding his score for each date for each individual month and give him Reward1 when accumulated score sum reaches 10 and Reward2 when accumulated score sum reaches 20. So the final table should like this:

+---------------+---------------+-------+---------------+---------------+
| ExamMonth     |  dateOfExam   | Score |    Reward1    |   Reward2     |
+---------------+---------------+-------+---------------+---------------+
|    2020-05    |  2020-05-28   |   5   |               |               |
|               |  2020-05-29   |   5   |       Y       |               |
|               |  2020-05-30   |   10  |               |       Y       |
|---------------|---------------|-------|---------------|---------------|
|    2020-06    |  2020-06-03   |   10  |       Y       |               |
|               |  2020-06-05   |   5   |               |               |
|---------------|---------------|-------|---------------|---------------|
|    2020-7     |  2020-07-21   |   20  |       Y       |       Y       |
|               |  2020-07-22   |   10  |               |               |
|               |  2020-07-28   |   10  |               |               |
+---------------+---------------+-------+---------------+---------------+

奖励字段可以是布尔值，空奖励行可以设置为 N 或 False 或任何看起来合乎逻辑的内容.这没有帮助:计算运行总和

Reward fields can be boolean and empty reward rows can be set to N or False or whatever seems logical. This was not helpful: Calculate running sum

请帮助我实现这个目标.建议一些方法.

Please help me achieve this objective. Suggest some approach.

这是一个小提琴.

推荐答案

首先计算 CTE 中每个月的分数的运行总和.
然后应用您的条件:

First calculate the running sum of the scores for each month in a CTE.
Then apply your conditions:

with cte as (
  select date_format(dateOfExam, '%Y-%m') ExamMonth,
         dateOfExam, score, 
         sum(score) over (partition by date_format(dateOfExam, '%Y-%m') order by dateOfExam) total
  from student
)
select ExamMonth, dateOfExam, score, 
       case when sum(total >= 10) over (partition by ExamMonth order by dateOfExam) = 1 then 'Y' end Reward1,
       case when sum(total >= 20) over (partition by ExamMonth order by dateOfExam) = 1 then 'Y' end Reward2
from cte

请参阅演示.
结果:

See the demo.
Results:

> ExamMonth | dateOfExam | score | Reward1 | Reward2
> :-------- | :--------- | ----: | :------ | :------
> 2020-05   | 2020-05-28 |     5 | null    | null   
> 2020-05   | 2020-05-29 |     5 | Y       | null   
> 2020-05   | 2020-05-30 |    10 | null    | Y      
> 2020-06   | 2020-06-03 |    10 | Y       | null   
> 2020-06   | 2020-06-05 |     5 | null    | null   
> 2020-07   | 2020-07-21 |    20 | Y       | Y      
> 2020-07   | 2020-07-22 |    10 | null    | null   
> 2020-07   | 2020-07-28 |    10 | null    | null 

这篇关于如何计算MySQL中每个组的运行总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！