Geek Answers Handbook

SQL query to check speed limits

Let's say I have a messages table that users sent, each with a timestamp.

I want to make a request that will tell me (historically) the largest number of messages sent by the user in an hour.

Thus, in other words, in any given 1-hour period, most messages were sent.

Any ideas?

+4
source share
4 answers

Assuming the timestamp will be DATETIME - otherwise use FROM_UNIXTIME to convert to DATETIME ...

Due to [rolling] during the last hour:

  SELECT COUNT(*) AS cnt FROM MESSAGES m WHERE m.timestamp BETWEEN DATE_SUB(NOW(), INTERVAL 1 HOUR) AND NOW() GROUP BY m.user ORDER BY cnt DESC LIMIT 1

If you need a specific hour, indicate the hour:

  SELECT COUNT(*) AS cnt FROM MESSAGES m WHERE m.timestamp BETWEEN '2011-06-06 14:00:00' AND '2011-06-06 15:00:00' GROUP BY m.user ORDER BY cnt DESC LIMIT 1
+2
source

More information is needed on the structure of the table, etc., but something like:

  select date(timestmp), hour(timestmp) , count(*) from yourtable group by date(timestmp) , hour(timestmp) order by count(*) DESC limit 100;

will give you the desired result.

+2
source

Something like this should work:

 SELECT MAX(PerHr) FROM (SELECT COUNT(*) AS PerHr FROM messages WHERE msg_uid=? GROUP BY msg_time/3600) t
+1
source

I suspect this would be terribly slow, but for an arbitrary historical maximum hour, something like this might work (take me off if I leave, I'm not a MySQL person):

 SELECT base.user, base.time, COUNT(later.time) FROM messages base INNER JOIN messages later ON later.time BETWEEN base.time AND DATE_ADD(base.time, INTERVAL 1 HOUR) AND base.user = later.user WHERE base.user = --{This query will only work for one user} GROUP BY base.user, base.time ORDER BY COUNT(later.time) DESC LIMIT 1
+1
source

More articles:


All Articles