使用MySQL的分组和按分钟计数

Question

嗨，大家好，我真的很努力地从一个大型的时间戳数据库中获得一天中的每一分钟的计数，我希望能够从数据。

因此，一天中有1440分钟，我如何获得每分钟的计数？

我可以在一个小时内得到每分钟的计数，我很难理解如何扩展以获得每天每分钟的计数。

我有了这个代码，我使用的那一刻，

SELECT FROM_UNIXTIME(
     CEILING(UNIX_TIMESTAMP(`timestamp`)/900)*900 
        ) AS timeslice 
    , COUNT(*) AS mycount 
    FROM visitor 
WHERE `timestamp` >= '20012-01-01' 
    AND `timestamp` < '20012-02-14' 
GROUP BY timeslice 

我希望这是有道理的，感谢您的帮助！

任何疑问，请向我

丹

Answer 1

看起来你不想按日/月/年分组，只需几分钟：

select minute, sum(if(adate is null, 0, 1)) DateAmount from (
    select @num := @num + 1 as minute from 
    (select 1 union all select 2 union all select 3 union all 
    select 4 union all select 5 union all select 6 union all 
    select 7 union all select 8 union all select 9 union all 
    select 10 union all select 11 union all select 12) as t1, 
    (select 1 union all select 2 union all select 3 union all 
    select 4 union all select 5 union all select 6 union all 
    select 7 union all select 8 union all select 9 union all 
    select 10 union all select 11 union all select 12) as t2, 
    (select 1 union all select 2 union all select 3 union all 
    select 4 union all select 5 union all select 6 union all 
    select 7 union all select 8 union all select 9 union all 
    select 10) as t3, 
    (select @num := - 1) as t4 
) as MinuteInDay 
left join visitors v 
on minute(v.aDate) = MinuteInDay.minute 
group by minute 

对于这组数据：

+---------------------+ 
|  aDate  | 
+---------------------+ 
| 2012-01-01 00:00:00 | 
| 2012-01-01 00:01:00 | 
| 2012-01-01 00:02:00 | 
| 2012-01-01 00:02:00 | 
| 2012-01-02 00:02:00 | 
| 2012-01-03 00:03:00 | 
+---------------------+ 

这将导致：

+--------+------------+ 
| MINUTE | DATEAMOUNT | 
+--------+------------+ 
| 0  | 1   | 
| 1  | 1   | 
| 2  | 3   | 
| 3  | 1   | 
| 4  | 0   | 
| 5  | 0   | 
| ... | ...  | 
| 1439 | 0   | 
+--------+------------+ 

希望这有助于。PS：如果你不需要0分钟而只是忽略它们，那将会容易得多！

Answer 2

做，这是创建一个已经在它分钟效用表的最简单方法。如果你聪明的话，你也可以把小时和子午线放在一起。

您可以通过搜索datawarehouse时间维度找到更多信息。

Answer 3

根据记录的数量，您可以尝试使用以下查询（检查其执行时间）。

select d.everyminute, 
     (select count(*) from visitor 
       where `timestamp` between d.everyminute and 
             d.everyminute + interval 59 second) cnt 
     from 
(
select @rownum:[email protected]+1, 
     date('2012-02-15') + interval (@rownum-1) minute everyminute from 

(select 0 union all select 1 union all select 2) t, 

(select 0 union all select 1 union all select 2 union all select 3 union all 
select 4 union all select 5 union all select 6 union all select 7 union all 
select 8 union all select 9) t1, 

(select 0 union all select 1 union all select 2 union all select 3 union all 
select 4 union all select 5 union all select 6 union all select 7 union all 
select 8 union all select 9) t2, 

(select 0 union all select 1 union all select 2 union all select 3 union all 
select 4 union all select 5 union all select 6 union all select 7 union all 
select 8 union all select 9) t3, 

(select @rownum:=0) r where @rownum < 24*60 
) d 

结果是（我把一些时间戳到测试DB）

2012-02-15 00:00:00 0 
2012-02-15 00:01:00 0 
2012-02-15 00:02:00 2 
2012-02-15 00:03:00 1 
2012-02-15 00:04:00 0 
2012-02-15 00:05:00 1 
2012-02-15 00:06:00 0 
2012-02-15 00:07:00 0 
2012-02-15 00:08:00 0 
2012-02-15 00:09:00 0 
2012-02-15 00:10:00 0 

，并依此类推，直至2012-02-15 23:59:00

现有记录有时间戳2012-02-15 00:02:00，2012-02-15 00:02:00，2012-02-15 00:03:00和2012-02-15 00:05:00

ps：考虑到夏季/冬季时间跳跃你可以重写where @rownum < 24*60 as having everyminute < (date('2012-02-15') + interval 1 day - interval 1 minute)

正如您所看到的，即使在此分钟内没有db记录时，它也会计数每分钟。

Answer 4

我会做这样的事情：

SELECT MOD(FLOOR(UNIX_TIMESTAMP(时间戳)/60), 1440) AS timeslice, COUNT(*) AS mycount FROM visitor WHERE时间戳>= '20012-01-01' AND时间戳< '20012-02-14' GROUP BY timeslice

自Unix纪元它会算的1440分钟批次，忽略夏令时间等