Sql Query - Limiting query results

Question

I am quite certain we cannot use the LIMIT clause for what I want to do - so wanted to find if there are any other ways we can accomplish this.

I have a table which captures which user visited which store. Every time a user visits a store, a row is inserted into this table.

Some of the fields are

• shopping_id (primary key)
• store_id
• user_id

Now what I want is - for a given set of stores, find the top 5 users who have visited the store max number of times.

I can do this 1 store at a time as:

select store_id,user_id,count(1) as visits 
from shopping 
where store_id = 60
group by user_id,store_id 
order by visits desc Limit 5

This will give me the 5 users who have visited store_id=60 the max times

What I want to do is provide a list of 10 store_ids and for each store fetch the 5 users who have visited that store max times

select store_id,user_id,count(1) as visits 
from shopping 
where store_id in  (60,61,62,63,64,65,66)
group by user_id,store_id 
order by visits desc Limit 5

This will not work as the Limit at the end will return only 5 rows rather than 5 rows for each store.

Any ideas on how I can achieve this. I can always write a loop and pass 1 store at a time but wanted to know if there is a better way

Answer 1

Using two user variable and counting the same consecutive store_id, you can replace <= 5 with whatever limit you want

SELECT a.*
FROM (
 SELECT store_id, user_id, count(1) as visits 
 FROM shopping
 WHERE store_id IN (60,61,62,63,64,65,66)
 GROUP BY store_id, user_id
 ORDER BY store_id, visits desc, user_id
) a,
(SELECT @prev:=-1, @count:=1) b
WHERE
 CASE WHEN @prev<>a.store_id THEN
   CASE WHEN @prev:=a.store_id THEN
    @count:=1
   END
 ELSE
   @count:=@count+1
 END <= 5

Edit as requested some explanation :

The first subquery (a) is the one that group and order the data so you will have data like:

store_id | user_id | visits
---------+---------+-------
 60           1       5
 60           2       3
 60           3       1
 61           2       4
 61           3       2

the second subquery (b) init the user variable @prev with -1 and @count with 1

then we choose all data from the subquery (a) verifying the condition in the case.

• verify that the previous store_id (@prev) we have seen is different from the current store_id. Since the first @prev is equal to -1 there is nothing that match the current store_id so the condition <> is true we enter then is the second case who just serve to change the value @prev with the current store_id. This is the trick so i can change the two user variable @count and @prev in the same condition.

• if the previous store_id is equal to @prev just increment the @count variable.

• we check that the count is within the value we want so the <= 5

So with our test data the:

step | @prev | @count | store_id | user_id | visits
-----+-------+--------+----------+---------+-------
  0      -1      1    
  1      60      1        60          1        5 
  2      60      2        60          2        3
  3      60      3        60          3        1
  4      61      1        61          2        4
  5      61      2        61          3        2   

Answer 2

Major concern over here is number of times you query a database. If you query multiple times from your script. Its simply wastage of resources and must be avoided. That is you must NOT run a loop to run the SQL multiple times by incrementing certain value. In your case 60 to 61 and so on.

Solution 1: Create a view Here is the solution

CREATE VIEW myView AS
select store_id,user_id,count(1) as visits 
from shopping 
where store_id = 60
group by user_id,store_id 
order by visits desc Limit 5
UNION
select store_id,user_id,count(1) as visits 
from shopping 
where store_id = 61
group by user_id,store_id 
order by visits desc Limit 5
UNION
select store_id,user_id,count(1) as visits 
from shopping 
where store_id = 62
group by user_id,store_id
order by visits desc Limit 5 

Now use

SELECT * from MyView

This is limited because you cant make it dynamic. What if you need 60 to 100 instead of 60 to 66.

Solution 2: Use Procedure. I wont go into how to write a procedure cuz its late night and I got to sleep. :) Well, procedure must accept two values 1st inital number (60) and 2nd Count (6) Inside the procedure create a temporary table (cursor) to store data then run a loop from initial number till count times In your case from 60 to 66 Inside the loop write desired script Replacing 60 with a looping variable.

select store_id,user_id,count(1) as visits 
from shopping 
where store_id = 60
group by user_id,store_id 
order by visits desc Limit 5

And append the result in the temporary table (cursor).

Hope this will solve your problem. Sorry I couldn't give you the code. If you still need it plz send me a message. I will give it to you when I wake up next morning.

Answer 3

UNION may be what you are looking for.

-- fist store
(select store_id,user_id,count(1) as visits 
from shopping 
where store_id = 60
group by user_id,store_id 
order by visits desc Limit 5)
UNION ALL
-- second store
(select store_id,user_id,count(1) as visits 
from shopping 
where store_id = 61
group by user_id,store_id 
order by visits desc Limit 5)
...

http://dev.mysql.com/doc/refman/5.0/en/union.html