MySQL:变得“最忙”或“最受欢迎”来自日期时间字段的小时?
考虑下表,其中包含字段 - id (int) 和 date_created (datetime):
id date_created
1 2010-02-25 12:25:32
2 2010-02-26 13:40:37
3 2010-03-01 12:02:22
4 2010-03-01 12:10:23
5 2010-03-02 10:10:09
6 2010-03-03 12:45:03
我想知道这组数据一天中最繁忙/最受欢迎的时间。在此示例中,我要查找的结果是 12。
有想法吗?
Consider the following table which has the fields - id (int) and date_created (datetime):
id date_created
1 2010-02-25 12:25:32
2 2010-02-26 13:40:37
3 2010-03-01 12:02:22
4 2010-03-01 12:10:23
5 2010-03-02 10:10:09
6 2010-03-03 12:45:03
I want to know the busiest/most popular hour of the day for this set of data. In this example, the result I'm looking for would be 12.
Ideas?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(5)
要获取最受欢迎的时间,请使用此查询
如果您想查看所有数据,请使用此查询
To get just the most popular hour, use this query
If you want to look at all the data, go with this one
如果您想要更灵活一点,也许是半小时或一刻钟,您可以执行以下操作:
如果您想要最流行的 2 小时间隔,请使用 7200。最流行的 15 分钟间隔,请使用 900。您只需需要记住你正在处理秒(一小时 3600 秒)。
If you want something a little more flexible, perhaps to the half hour, or quarter hour, you can do the following:
If you want the most popular 2 hour interval, use 7200. The most popular 15 minute interval, use 900. You just need to remember you are dealing with seconds (3600 seconds in an hour).
使用
hour()
函数提取小时,然后执行常规聚合:SELECT count(hour(date_created)) AS c, hour(date_created) AS h FROM table GROUP BY h ORDER BY c DESC;
Use the
hour()
function to extract the hour, then do the usual aggregation:SELECT count(hour(date_created)) AS c, hour(date_created) AS h FROM table GROUP BY h ORDER BY c DESC;
我喜欢西蒙和彼得的答案,但我不能选择两者都接受。我将两者结合起来进行一个更清晰的查询,仅返回热门时间(我不需要计数)。
I like both Simon and Peter's answers, but I can't select both as accepted. I combined the 2 to make a cleaner query that only returned the popular hour (I don't need the counts).
你可以试试这个:
You could try this: