mysql累积滚动_在一段时间内在mysql中创建滚动总和

我有一个表,其中包含date和time_spent列.我想为每个日期D查找一段时间内“ time_spent”值的总和：(D-7-D),即.过去一周的当前日期.

我找不到解决办法,因为我只能找到一个总和的示例,而不是一个可变时间段内的总和.

这是一个数据集示例：

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

...