按小时分组，然后在每组中计数＃ [英] Group by hour of day then Count # in each group

问题描述

我对目前的任务感到困惑。它可以处理显示房间租赁中最受欢迎的时间。

我有一个大型数据库（25万个条目），输入日期/时间存储为日期时间。
示例：

  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小时的时间间隔分组，然后返回每个时间间隔内的条目数。

$ p $ 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查询每个区间，但我很不满意在那个庄园做。

任何人都可以帮助我理解如何更好地完成这种类型的查询吗？

PS我正在通过PHP访问MySQL，如果这与这个答案有关。

解决方案

 选择日期格式（yourdatetime，'％H'），count（*）
来自yourtable 
按日期格式组织（yourdatetime，'％H'）; 
  

I am having difficulity with my current task. It deals with displaying the most popular time of day for room rental.

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

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

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?

P.S. I am accessing MySQL via PHP if that is relevant to this answer.

解决方案

Select date_format(yourdatetime, '%H'), count(*)
From yourtable
Group by date_format(yourdatetime, '%H');

这篇关于按小时分组，然后在每组中计数＃的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！