我需要在过去24小时内输入的用户小时数。 从00:00到23:59每隔一个小时我得把它分组。 但是,用户必须有唯一的记录。
如何对其进行sql查询或口才查询?
我的解决方案是:
SELECT count(user_id) as Users,
CONCAT( HOUR(created_at), ' to ', CONCAT( HOUR(created_at), ':59:59' )
) as Time_Frame
FROM table_name
where created_at >= NOW() - INTERVAL 1 DAY
GROUP BY
DATE(created_at),
HOUR(created_at)
order by count(user_id) DESC
假设您想要最近的完整日历日期,您可以使用
select maketime(hour(enteredAt), 0, 0) as hour, count(*)
from t
where enteredAt >= curdate() - interval 1 day and
enteredAt < curdate()
group by hour
order by hour;