MySQL / PHP组按小时,然后在每个组的计数# [英] MySQL/PHP Group by hour of day then Count # in each group
问题描述
我目前的任务有困难。
I am having difficulity with my current task. It deals with displaying the most popular time of day for room rental.
我有一个大型数据库(25万个条目),输入日期/时间存储为日期时间。
例如:
I have a large database (250,000+ entries), with entry dates/times stored as datetime. Example:
2015-06-10 19:30:15
2015-06-10 18:30:15
2015-06-10 14:03:15
2015-06-09 16:30:15
2015-06-09 14:30:15
2015-06-09 14:50:15
2015-06-08 13:30:15
2015-06-08 14:05:15
我需要做的是按1小时的间隔进行分组,然后返回每个间隔中条目数的计数。
What I need to do, is group by 1 hour intervals, then return the count of number of entries in each interval.
示例(不反映第一个示例集中的数据):
Example (Does not reflect data in first example set):
09:01 - 10:00 = 2
10:01 - 11:00 = 3
10:01 - 12:00 = 6
12:01 - 13:00 = 8
13:01 - 14:00 = 5
我能做到这一点的唯一方法是创建一个sql查询
The only way I have been able to do this, is create a sql query for each interval, but I am very dissatisfied doing it in that manor.
任何人都可以帮助我理解如何更好地完成这种类型的查询?
Can anyone help me understand how to better accomplish this type of query?
PS
推荐答案
Select date_format(yourdatetime, '%H'), count(*)
From yourtable
Group by date_format(yourdatetime, '%H');
这篇关于MySQL / PHP组按小时,然后在每个组的计数#的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!