尾数查询 [英] Trailing Sum Query

问题描述

我正在寻找总结日期，需要找到一种方法来计算 3 天的尾随总和，即当前日期和前 2 天的总和.我正在使用 MariaDB，一个 MYSQL 分支.

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.

这是数据的一个子集；

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 |
+------------+--------+-----------+

如果前几天不存在，它甚至可以打印 NaN 或将其留空.任何想法或建议将不胜感激.

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

推荐答案

使用 MySQL 变量的快速方法

A fast way using MySQL variables

示例表:

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);

查询:

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

(您始终可以选择此项以仅获取前 3 列)

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

这篇关于尾数查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！