MySQL 查询每日每小时峰值

Question

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

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

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

Answer 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