在一段时间内在mysql中创建一个ROLLING总和 [英] Create a ROLLING sum over a period of time in mysql

问题描述

我有一个列date和time_spent的表.我想为每个日期D找到一段时间内"time_spent"值的总和:(D-7-D)，即.过去一周+今天.

I have a table with columns date and time_spent. I want to find for each date D the sum of the values of 'time_spent' for the period of time : (D-7 - D), ie. past week + current day.

我找不到解决办法，因为我只能找到总和的示例，而找不到可变时期内的总和.

I can't figure out a way to do this, as I can only find examples for a total sum and not a sum over a variable period of time.

这是一个数据集示例:

CREATE TABLE rolling_total
(
  date date,
  time_spent int
);

INSERT INTO rolling_total VALUES ('2013-09-01','2'),
('2013-09-02','1'),
('2013-09-03','3'),
('2013-09-04','4'),
('2013-09-05','2'),
('2013-09-06','5'),
('2013-09-07','3'),
('2013-09-08','2'),
('2013-09-09','1'),
('2013-09-10','1'),
('2013-09-11','1'),
('2013-09-12','3'),
('2013-09-13','2'),
('2013-09-14','4'),
('2013-09-15','6'),
('2013-09-16','1'),
('2013-09-17','2'),
('2013-09-18','3'),
('2013-09-19','4'),
('2013-09-20','1'),
('2013-09-21','6'),
('2013-09-22','5'),
('2013-09-23','3'),
('2013-09-24','1'),
('2013-09-25','5'),
('2013-09-26','2'),
('2013-09-27','1'),
('2013-09-28','4'),
('2013-09-29','3'),
('2013-09-30','2')

结果如下:

date       | time_spent   | rolling_week_total
2013-09-01 |           2  |         2
2013-09-02 |           1  |         3
2013-09-03 |           3  |         6
2013-09-04 |           4  |        10
2013-09-05 |           2  |        12
2013-09-06 |           5  |        17
2013-09-07 |           3  |        20
2013-09-08 |           2  |        22
// now we omit values that are older than seven days
2013-09-09 |           1  |        21
2013-09-10 |           1  |        21
...

推荐答案

还有另一种解决方案

SELECT r1.date, r1.time_spent, sum(r2.time_spent) AS rolling_week_total
FROM rolling_total AS r1 JOIN rolling_total AS r2
    ON datediff(r1.date, r2.date) BETWEEN 0 AND 7
GROUP BY r1.date
ORDER BY r1.date
LIMIT 8

这篇关于在一段时间内在mysql中创建一个ROLLING总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！