Grouping MySQL query by SUM (threshold)

问题: I'm setting up a MySQL query that sums values from a database and groups these by thresholds, so it only outputs the 'milestones' when certain values were reached. The da...

问题:

I'm setting up a MySQL query that sums values from a database and groups these by thresholds, so it only outputs the 'milestones' when certain values were reached.

The database looks like:

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

Expected output:

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

Edit: The milestones should show what the first date was that a certain milestone was surpassed. So 2018-12-03 is the first date that 1000 was surpassed.

The 'milestones' won't be the same distance from each other in every occasion. So ideally I'm looking for a query that could combine SUM with IF and GROUP possibly?

I have been Googling for hours, but at a loss how I can do this, other than looping through all the results separately and counting the total values + if statements, which is resource consuming. If at all possible, I'd like to keep it within the query.

Thank you for your time!


回答1:

E.g.:

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     |
+---------------+----------------+----------------+
  • 发表于 2019-01-05 05:48
  • 阅读 ( 154 )
  • 分类:网络文章

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除