mysql - 按 SUM(阈值)对 MySQL 查询进行分组

Question

我正在设置一个 MySQL 查询，该查询对数据库中的值求和并按阈值对这些值进行分组，因此它仅在达到特定值时才输出“里程碑”。

数据库看起来像:

date       - number
2018-12-01 - 500
2018-12-02 - 400
2018-12-03 - 300
2018-12-04 - 500
2018-12-05 - 400
etc.

预期输出:

Milestone 500 = 2018-12-01
Milestone 1000 = 2018-12-03
Milestone 2000 = 2018-12-05
etc.

里程碑应显示超过某个里程碑的第一个日期。所以 2018-12-03 是第一个超过 1000 的日期。

在每种情况下，“里程碑”彼此之间的距离不会相同。所以理想情况下，我正在寻找可以将 SUM 与 IF 和 GROUP 结合起来的查询？

我已经用谷歌搜索了几个小时，但我不知道该怎么做，除了分别遍历所有结果并计算总值 + if 语句，这很耗费资源。如果可能的话，我想将其保留在查询中。

Answer 1

例如:

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(date DATE NOT NULL PRIMARY KEY
,number INT NOT NULL
);

INSERT INTO my_table VALUES
('2018-12-01',500),
('2018-12-02',400),
('2018-12-03',300),
('2018-12-04',500),
('2018-12-05',400);

SELECT MIN(CASE WHEN total >=  500 THEN date END) milestone_500 
     , MIN(CASE WHEN total >= 1000 THEN date END) milestone_1000 
     , MIN(CASE WHEN total >= 2000 THEN date END) milestone_2000 
  FROM
     (
       SELECT x.*,SUM(y.number) total FROM my_table x JOIN my_table y ON y.date <= x.date GROUP BY x.date
     ) a;

+---------------+----------------+----------------+
| milestone_500 | milestone_1000 | milestone_2000 |
+---------------+----------------+----------------+
| 2018-12-01    | 2018-12-03     | 2018-12-05     |
+---------------+----------------+----------------+