SQL获取每分钟的数量计数 [英] SQL to get number count per minute

问题描述

我有一个像这样的数据集:

I have a dataset like:

id       name      updated_at
1        test1     2014-06-30 09:00:00
1        test2     2014-06-30 09:01:10
1        test3     2014-06-30 09:01:23
1        test4     2014-06-30 09:01:43
1        test5     2014-06-30 09:02:02
1        test6     2014-06-30 09:02:34
1        test7     2014-06-30 09:03:22
1        test8     2014-06-30 09:03:28

我需要获取最近十分钟内每分钟的行数.因此，它应始终返回十个数字，它们是最后更新的行数.关于如何高效地做到这一点的任何想法?

I need to get a count of the rows by minute for the last ten minutes. So it should always return ten numbers being the count of the rows that were updated last. Any ideas on how to do it and efficiently?

推荐答案

最近10条结果

http://sqlfiddle.com/#!9/3d586/22

--get the minute component of the update time
select minute(updated_at) as Sec
--count the number of records which have this minute
, count(1) as Cnt 
from myTable
--use group by to ensure we return 1 row per minute
group by minute(updated_at)
--list from most recent working backwards
order by minute(updated_at) desc
--return up to 10 results
limit 10

最近10分钟的搜索结果

http://sqlfiddle.com/#!9/3d586/26

--get the minute component of the update time
select minute(y.d) as Min
--count the number of records which have this minute
--use m.id instead of 1 or * to ensure where there's no result from myTable
--we don't count any rows
, count(m.id) as Cnt 
from
(
    --get the current date's minute, offset by a given amount
    select date_add(now(), interval -x.a minute) d
    from
    (
       --the list of given amounts by which to offset the above date
       select 0 a
       union select 1
       union select 2
       union select 3
       union select 4
       union select 5
       union select 6
       union select 7
       union select 8
       union select 9
    ) x
) y
--left join to ensure the above list drives which results we get, 
--regardless of whether there are matching entries in myTable
left outer join myTable m
--join on the minute of each date 
on minute(m.updated_at) = minute(y.d)
--use group by to ensure we return 1 row per minute
group by minute(y.d)
--list from most recent working backwards
order by minute(y.d) desc

这篇关于SQL获取每分钟的数量计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！