汇总MySQL中每一行最近n天的数据

Question

我有以下格式的数据-

Date        Amount
1/1/2000       1
2/1/2000       1    
3/1/2000       1
4/1/2000       2
5/1/2000       1
6/1/2000       1
7/1/2000       1

这里，每一行代表当天收集的金额，我想准备一个表，其中每一行代表最近3天收集的金额。因此，对于3/1/2000 -它将显示amount =3 (amount 1在1/1,1在2/1,1在3/1，所以1+1+1 =3

所以，从上面的数据中，我想要的表是-

Date        Amount
1/1/2000       1    //1
2/1/2000       2    //1+1    
3/1/2000       3    //1+1+1
4/1/2000       4    //1+1+2
5/1/2000       4    //1+2+1
6/1/2000       4    //2+1+1
7/1/2000       3    //1+1+1

如何写一个SQL查询这个?

I tried this -

select date, sum(amount) as amount_sum
from SQLTABLE
where DATEDIFF(date,date)<=2 
group by date

Answer 1

您可以使用相关子查询来获取前两条记录的Amount值:

SELECT `Date`, 
       Amount + 
       COALESCE((SELECT Amount 
                 FROM mytable AS t2
                 WHERE t2.`Date` < t1.`Date`
                 ORDER BY `Date` DESC LIMIT 1), 0) +
       COALESCE((SELECT Amount 
                 FROM mytable AS t2
                 WHERE t2.`Date` < t1.`Date`
                 ORDER BY `Date` DESC LIMIT 1, 1), 0) AS Amount
FROM mytable AS t1 

即使连续记录之间有间隔，上述查询也能工作。

编辑:

如果连续记录之间没有间隔，则可以使用以下查询:

SELECT `Date`,       
       COALESCE((SELECT SUM(Amount)
                 FROM mytable AS t2
                 WHERE t2.date <= t1.date AND DATEDIFF(t1.date,t2.date) <= 2
                 ORDER BY `Date` DESC LIMIT 1), 0) AS Amount
FROM mytable AS t1 

Answer 2

这可以通过使用子选择来实现。

SELECT date, 
       (SELECT sum(amount) 
       from SQLTABLE t2 
       WHERE DATEDIFF(t1.date,t2.date) IN (0,1,2)) amount_sum 
from SQLTABLE t1