Trailing Sum Query

I am looking to summarize date and need to find a way of doing a 3 day trailing sum, sum of the current date and the 2 previous days. I am using MariaDB, a MYSQL fork.

Here is a subset of the data开发者_Python百科;

select Date, Total from keywordSum limit 5;
+------------+--------+
| Date       | Total  |
+------------+--------+
| 2010-11-11 | 316815 |
| 2010-11-12 | 735305 |
| 2010-11-13 | 705116 |
| 2010-11-14 | 725020 |
| 2010-11-15 | 745378 |
+------------+--------+

I would like to end up with a result similar to this:

+------------+--------+-----------+
| Date       | Total  | 3DayTotal |
+------------+--------+-----------+
| 2010-11-11 | 316815 |    316815 |
| 2010-11-12 | 735305 |   1052120 |
| 2010-11-13 | 705116 |   1757236 |
| 2010-11-14 | 725020 |   2167441 |
| 2010-11-15 | 745378 |   2177514 |
+------------+--------+-----------+

It could even print NaN or leave it blank if the previous days don't exist. Any thoughts or suggestions would be greatly appreciated.

One simple way is to join the table to itself. Make sure it is indexed on a combination of date and total.

select t1.date
     , t1.total
     , t1.total 
      +coalesce(t2.total,0)
      +coalesce(t3.total,0)
  from theTable t1
  left 
  join theTable t2 on t1.date = date_Add(t2.date,interval 1 day)
  left
  join theTable t3 on t1.date = date_Add(t3.date,interval 2 day)

A fast way using MySQL variables

Sample table:

create table keywordsum (date datetime, total int);
insert keywordsum values
('2010-11-11',316815),
('2010-11-12',735305),
('2010-11-13',705116),
('2010-11-14',725020),
('2010-11-15',745378);

Query:

select
  k.date, k.total, k.total + ifnull(@d1,0) + ifnull(@d2,0) running_total,
  @d2 := @d1,
  @d1 := k.total
from (select @d1 := null, @d2 := null) vars
cross join keywordsum k
order by k.date

(You can always subselect this to get only the first 3 columns)