MYSQL Trending Query

Question

I have a table called log that logs recently used items the columns are as follows:

u_id(a unique number)
item_id (an item id number)
access_time(a server timestamp)

I would like to create a query that finds the trend of the most accessed items over a period of time. For example what are the top 10 items that were accessed the most in the last 60 minutes (server time).

How can I create such a query?

score 2 · Answer 1 · answered Oct 12 '15 at 22:25

2

SELECT item_id, COUNT(*) FROM log 
WHERE TIMESTAMPDIFF(MINUTE, access_time, NOW()) <= 60
GROUP BY item_id
ORDER BY COUNT(item_id) DESC
LIMIT 0,10

answered Oct 12 '15 at 22:25

A.O.

3,733
6
30
49

score 2 · Answer 2 · answered Oct 12 '15 at 22:26

I'd just count the items the items accessed in the last hour, order by the number of accesses, and take the top 10:

SELECT   item_id, COUNT(*)
FROM     log
WHERE    access_time >= DATE_SUB(CURDATE(), INTERVAL 1 HOUR)
GROUP BY item_id
ORDER BY 2 DESC
LIMIT    10

MYSQL Trending Query

2 Answers2