mysql 计算时间跨度_mysql选择时间跨度之间的行数

好吧,我意识到我有点迟了,但无论如何我想发布我的答案:-)

你需要什么可以使用子查询完成,但这可能需要很长时间才能完成一个大表…

考虑到这个问题,我采用了两种不同的方法.

其中一个已经在其他答案中得到处理,它的工作原理是在特定时间点开始,查看此时开始的间隔,然后查看紧随其后的相等持续时间间隔.这导致清晰,可理解的结果,并且可能是需要的(例如,每个日历日用户不得超过100次下载).然而,这将完全错过用户在午夜之前的一小时内进行99次下载而在新一天的第一小时内进行另外99次下载的情况.

因此,如果所需的结果更像是“十大下载列表”,那么这是另一种方法.这里的结果乍一看可能不太明白,因为单个下载可以计入多个间隔.这是因为间隔将(并且需要)重叠.

这是我的设置.我从你的语句中创建了表并添加了两个索引：

CREATE INDEX downloads_timestamp on downloads (dl_date);

CREATE INDEX downloads_user_id on downloads (user_id);

我插入表中的数据：

SELECT * FROM downloads;

+----+----------+---------+---------------------+

| id | stuff_id | user_id | dl_date |

+----+----------+---------+---------------------+

| 1 | 1 | 1 | 2011-01-24 09:00:00 |

| 2 | 1 | 1 | 2011-01-24 09:30:00 |

| 3 | 1 | 1 | 2011-01-24 09:35:00 |

| 4 | 1 | 1 | 2011-01-24 10:00:00 |

| 5 | 1 | 1 | 2011-01-24 11:00:00 |

| 6 | 1 | 1 | 2011-01-24 11:15:00 |

| 7 | 1 | 1 | 2011-01-25 09:15:00 |

| 8 | 1 | 1 | 2011-01-25 09:30:00 |

| 9 | 1 | 1 | 2011-01-25 09:45:00 |

| 10 | 1 | 2 | 2011-01-24 08:00:00 |

| 11 | 1 | 2 | 2011-01-24 12:00:00 |

| 12 | 1 | 2 | 2011-01-24 12:01:00 |

| 13 | 1 | 2 | 2011-01-24 12:02:00 |

| 14 | 1 | 2 | 2011-01-24 12:03:00 |

| 15 | 1 | 2 | 2011-01-24 12:00:00 |

| 16 | 1 | 2 | 2011-01-24 12:04:00 |

| 17 | 1 | 2 | 2011-01-24 12:05:00 |

| 18 | 1 | 2 | 2011-01-24 12:06:00 |

| 19 | 1 | 2 | 2011-01-24 12:07:00 |

| 20 | 1 | 2 | 2011-01-24 12:08:00 |

| 21 | 1 | 2 | 2011-01-24 12:09:00 |

| 22 | 1 | 2 | 2011-01-24 12:10:00 |

| 23 | 1 | 2 | 2011-01-25 14:00:00 |

| 24 | 1 | 2 | 2011-01-25 14:12:00 |

| 25 | 1 | 2 | 2011-01-25 14:25:00 |

+----+----------+---------+---------------------+

25 rows in set (0.00 sec)

如您所见,所有下载都发生在昨天或今天,并由两个不同的用户执行.

现在,我们要注意以下几点：“数学上”在“2011-01-24 0:00”和“2011-01-25 23”之间存在无限数量的24小时间隔(或任何其他持续时间间隔) ：59：59′ .但是,由于服务器的精度是一秒,因此可以归结为86,400个间隔：

First interval: 2011-01-24 0:00:00 -> 2011-01-25 0:00:00

Second interval: 2011-01-24 0:00:01 -> 2011-01-25 0:00:01

Third interval: 2011-01-24 0:00:02 -> 2011-01-25 0:00:02

.

.

.

86400th interval: 2011-01-24 23:59:59 -> 2011-01-25 23:59:59

因此,我们可以使用循环迭代所有这些间隔,并计算每个用户和每个间隔的下载次数.当然,并非所有区间对我们都有相同的兴趣,因此我们可以通过使用表中的时间戳作为“间隔开始”来跳过其中一些区间.

这是以下查询的作用.它使用表中的每个下载时间戳作为“间隔开始”,添加间隔持续时间,然后查询在此间隔期间每个用户的下载次数.

SET @duration = '24:00:00';

SET @limit = 5;

SELECT * FROM

(SELECT t1.user_id,

t1.dl_date startOfPeriod,

ADDTIME(t1.dl_date,@duration) endOfPeriod,

(SELECT COUNT(1)

FROM downloads t2

WHERE t1.user_id = t2.user_id

AND t1.dl_date <= t2.dl_date

AND ADDTIME(t1.dl_date,@duration) >= t2.dl_date) count

FROM downloads t1) t3

WHERE count > @limit;

这是结果：

+---------+---------------------+---------------------+-------+

| user_id | startOfPeriod | endOfPeriod | count |

+---------+---------------------+---------------------+-------+

| 1 | 2011-01-24 09:00:00 | 2011-01-25 09:00:00 | 6 |

| 1 | 2011-01-24 09:30:00 | 2011-01-25 09:30:00 | 7 |

| 1 | 2011-01-24 09:35:00 | 2011-01-25 09:35:00 | 6 |

| 1 | 2011-01-24 10:00:00 | 2011-01-25 10:00:00 | 6 |

| 2 | 2011-01-24 08:00:00 | 2011-01-25 08:00:00 | 13 |

| 2 | 2011-01-24 12:00:00 | 2011-01-25 12:00:00 | 12 |

| 2 | 2011-01-24 12:01:00 | 2011-01-25 12:01:00 | 10 |

| 2 | 2011-01-24 12:02:00 | 2011-01-25 12:02:00 | 9 |

| 2 | 2011-01-24 12:03:00 | 2011-01-25 12:03:00 | 8 |

| 2 | 2011-01-24 12:00:00 | 2011-01-25 12:00:00 | 12 |

| 2 | 2011-01-24 12:04:00 | 2011-01-25 12:04:00 | 7 |

| 2 | 2011-01-24 12:05:00 | 2011-01-25 12:05:00 | 6 |

+---------+---------------------+---------------------+-------+

12 rows in set (0.00 sec)