SQL查询-限制查询结果 [英] Sql Query - Limiting query results

问题描述

我很确定我们不能将LIMIT子句用于我想做的事情-因此想确定是否还有其他方法可以实现此目的.

我有一个表，其中捕获了哪个用户访问了哪个商店.每次用户访问商店时，都会在此表中插入一行.

其中一些字段是

• shopping_id(主键)
• store_id
• user_id

现在我想要的是-对于给定的一组商店，找到访问该商店的最大次数的前5名用户.

我可以一次在1个商店中进行以下操作:

这将使我有5位访问store_id = 60的用户达到最大访问次数

我要做的是提供10个store_id的列表，并为每个商店获取访问该商店的最大时间的5位用户 这将不起作用，因为最后的限制"将仅返回5行，而不是每个商店返回5行.

关于如何实现此目标的任何想法.我总是可以编写一个循环并一次通过1个存储，但想知道是否有更好的方法

解决方案

使用两个用户变量并计算相同的连续store_id，您可以将<= 5替换为所需的任何限制

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

根据要求编辑一些说明:

第一个子查询(a)是对数据进行分组和排序的子查询，因此您将拥有以下数据:

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

第二个子查询(b)将用户变量@prev设置为-1，将@count设置为1

然后，我们从子查询(a)中选择所有数据，以验证case中的条件.

• 验证我们之前看到的store_id(@prev)是否与当前store_id不同. 由于第一个@prev等于-1，因此没有与当前store_id匹配的条件，因此我们输入的条件<>为true，那么第二种情况就是使用当前store_id更改值@prev.这是技巧，因此我可以在相同条件下更改两个用户变量@count和@prev.

• 如果先前的store_id等于@prev，则只需增加@count变量即可.

• 我们检查计数是否在所需值之内，以便<= 5

因此，根据我们的测试数据:

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   

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

解决方案

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   

这篇关于SQL查询-限制查询结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！