mysql - SQL to get number count per minute -


i have 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 count of rows minute last ten minutes. should return ten numbers being count of rows updated last. ideas on how , efficiently?

last 10 results

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

--get minute component of update time select minute(updated_at) sec --count number of records have minute , count(1) cnt  mytable --use group ensure return 1 row per minute group minute(updated_at) --list recent working backwards order minute(updated_at) desc --return 10 results limit 10 

results last 10 minutes

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

--get minute component of update time select minute(y.d) min --count number of records have minute --use m.id instead of 1 or * ensure there's no result mytable --we don't count rows , count(m.id) cnt  (     --get current date's minute, offset given amount     select date_add(now(), interval -x.a minute) d         (        --the list of given amounts offset above date        select 0        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 ensure above list drives results get,  --regardless of whether there matching entries in mytable left outer join mytable m --join on minute of each date  on minute(m.updated_at) = minute(y.d) --use group ensure return 1 row per minute group minute(y.d) --list recent working backwards order minute(y.d) desc 

Comments

Popular posts from this blog

css - SVG using textPath a symbol not rendering in Firefox -

Java 8 + Maven Javadoc plugin: Error fetching URL -

datatable - Matlab struct computations -