基于时间范围、组用户和滑动窗口上和值的MySQL查询
我想根据另一个现有表A中的信息创建一个新的表B。我想知道MySQL是否有考虑到一系列时间和组列A值的功能，然后只根据A列中的这些组来总结B列中的值。
表A为用户存储像日志一样的事件日志。一个用户可以在一天内发生多个事件。假设地说，我正在跟踪我的使用者什么时候吃水果，我想知道他们一周吃多少水果(7天)，还有他们吃了多少苹果。
因此，在表B中，我想计算表A中的每个条目，前7天水果和苹果的总数。
编辑：
很抱歉，我过分简化了我给定的信息，没有仔细考虑我的例子。
我最初只有表A，我试图从查询中创建表B。
假设：
- 用户/id可以在一天内多次记录一个条目。
- 和计数应用于日期和日期之间的id -7天。
- 水果栏代表7天间隔内的果实总数(苹果和香蕉都是水果)。
- 数据不只是从2013年9月5日开始的。它可以追溯到2000年，我想使用7天滑动窗口在2000年至2013年之间的所有日期。
总数超过了7天的滑动窗口。
下面是一个例子：
Table A:
| id | date-time | apples | banana |
---------------------------------------------
| 1 | 2013-9-5 08:00:00 | 1 | 1 |
| 2 | 2013-9-5 09:00:00 | 1 | 0 |
| 1 | 2013-9-5 16:00:00 | 1 | 0 |
| 1 | 2013-9-6 08:00:00 | 0 | 1 |
| 2 | 2013-9-9 08:00:00 | 1 | 1 |
| 1 | 2013-9-11 08:00:00 | 0 | 1 |
| 1 | 2013-9-12 08:00:00 | 0 | 1 |
| 2 | 2013-9-13 08:00:00 | 1 | 1 | 注:用户1在2013年记录了2个条目-9-5
查询后的结果应该是表B。
Table B
| id | date-time | apples | fruit |
--------------------------------------------
| 1 | 2013-9-5 08:00:00 | 1 | 2 |
| 2 | 2013-9-5 09:00:00 | 1 | 1 |
| 1 | 2013-9-5 16:00:00 | 2 | 3 |
| 1 | 2013-9-6 08:00:00 | 2 | 4 |
| 2 | 2013-9-9 08:00:00 | 2 | 3 |
| 1 | 2013-9-11 08:00:00 | 2 | 5 |
| 1 | 2013-9-12 08:00:00 | 0 | 3 |
| 2 | 2013-9-13 08:00:00 | 2 | 4 |在2013-9-12年，滑动窗口移动，只包括9-6到9-12.这就是为什么id 1从2个苹果之和到0个苹果的原因。
回答 2
Stack Overflow用户
发布于 2013-09-09 19:03:43
您需要数年的数据才能正确地使用日期算术。是我加的。
你的数据里有件奇怪的事。似乎每个人每天都有多个日志条目。您假设有一个隐式顺序，以某种方式“在”前面的日志条目之后设置后面的日志条目。如果SQL和MySQL这样做，这只是偶然的:表中没有行的隐式排序。另外，如果我们复制日期/id组合，那么自联接(read on)有很多重复行，并破坏了和。
因此，我们首先需要创建数据的每日汇总表，如下所示：
select id, `date`, sum(apples) as apples, sum(banana) as banana
from fruit
group by id, `date`此摘要将包含最多每id每天一行。
接下来，我们需要做一个有限的交叉产品自我连接，所以我们得到七天的水果价值。
select --whatever--
from (
-- summary query --
) as a
join (
-- same summary query once again
) as b
on ( a.id = b.id
and b.`date` between a.`date` - interval 6 day AND a.`date` )between子句在on中给我们七天时间(今天和前六天)。注意，带有别名b的联接中的表是7天的内容，而a表是今天的内容。
最后，我们必须根据您的说明总结这个结果。结果的查询如下。
select a.id, a.`date`,
sum(b.apples) + sum(b.banana) as fruit_last_week,
a.apples as apple_today
from (
select id, `date`, sum(apples) as apples, sum(banana) as banana
from fruit
group by id, `date`
) as a
join (
select id, `date`, sum(apples) as apples, sum(banana) as banana
from fruit
group by id, `date`
) as b on (a.id = b.id and
b.`date` between a.`date` - interval 6 day AND a.`date` )
group by a.id, a.`date`, a.apples
order by a.`date`, a.id这里有一个小提琴：http://sqlfiddle.com/#!2/670b2/15/0
Stack Overflow用户
发布于 2013-09-09 18:52:06
假设：
- 每id/日期一行
- 清点日期为日期至日期之间的id -7天。
- 水果=香蕉
- “日期”栏实际上是一个日期(包括年份)，而不仅仅是月/日。
那么这个SQL应该可以做到这一点：
INSERT INTO B
SELECT a1.id, a1.date, SUM( a2.banana ), SUM( a2.apples )
FROM (SELECT DISTINCT id, date
FROM A
WHERE date > NOW() - INTERVAL 7 DAY
) a1
JOIN A a2
ON a2.id = a1.id
AND a2.date <= a1.date
AND a2.date >= a1.date - INTERVAL 7 DAY
GROUP BY a1.id, a1.date一些问题：
- 上述假设是否正确？
- 表A比香蕉和苹果含有更多的水果吗？如果是的话，真正的结构是什么样子的？
https://stackoverflow.com/questions/18704614
复制相似问题
腾讯云开发者
