MySQL 查询每日每小时峰值-PHP中文网问答

文章专题 AI工具学习下载问答源码最近更新

PHP

会员中心讲师中心微信公众号

MySQL 查询每日每小时峰值

黄舟 2017-04-17 11:24:34

[MySQL讨论组]

321

现在的查询方法是这样的：

SELECT COUNT(r.user_id) AS users
FROM record r
WHERE r.created BETWEEN :date_from AND :date_till
GROUP BY HOUR(r.created)
ORDER BY users DESC
LIMIT 1

但这样如果查询多天的峰值就要循环执行多次查询，是否有更高效的方法？

黄舟

人生最曼妙的风景，竟是内心的淡定与从容！

全部回复(1)

天蓬老师2017-04-17 11:26:34 1楼

假设 r.created 是类似 2014-04-03 09:04:00

SELECT COUNT(r.user_id) AS users, HOUR(r.created) data_hour, FROM_UNIXTIME(unix_timestamp(r.created), "%Y-%m-%d") data_date
FROM record r
WHERE r.created BETWEEN :date_from AND :date_till
GROUP BY data_hour, data_date
ORDER BY data_date, users, data_hour DESC