如何在MySQL中计算每个组的运行总和

Question

我可以用sum()函数进行直接求和。但我的情况不同。我有一个只有2个字段的表学生。例如，假设全班只有一个学生：

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)
;

我有他在考试当天的分数，运行时还有一列，也就是考试月份：

查询是(昨天得到了stackoverflow的帮助(：

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分时，我会给他奖励1，当累计得分达到20分时，会给他奖励2。所以最后的表格应该是这样的：

+---------------+---------------+-------+---------------+---------------+
| 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或任何看起来合乎逻辑的值。这没有帮助：计算运行总和

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

这是一把小提琴。

Answer 1

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

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

请参阅演示
结果：

> 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 

Answer 2

下面的代码段根据ExamMonth上的基本查询进行分组，然后在决定Reward1和Reward2的值时使用case。此查询仅为您提供指针。请按照最适合您的方式重写。

select DERIVED2.ExamMonth, CASE WHEN DERIVED2.Cumul_Score >= 10 THEN 'Y'
ELSE ''
END AS Rewards1,
CASE WHEN DERIVED2.Cumul_Score >= 20 THEN 'Y'
ELSE ''
END AS Rewards2
FROM 
(
select DERIVED1.ExamMonth, SUM(DERIVED1.score) as Cumul_Score 
FROM
(
select date_format(dateOfExam, '%Y-%m') ExamMonth,
dateOfExam, score
from student
order by dateOfExam
) DERIVED1
GROUP BY ExamMonth
) DERIVED2