SQL查询 - 限制查询结果

Question

SQL查询 - 限制查询结果

sqlmysql

7

我非常确定我们不能使用LIMIT子句来完成我想要的操作 - 所以想找到其他方法来实现。

我有一个表，记录了哪个用户访问了哪个商店。每次用户访问商店时，都会向该表中插入一行记录。

其中一些字段如下：

shopping_id（主键）
store_id
user_id

现在我想做的是 - 对于一组给定的商店，找出访问该商店最多的前5个用户。

我可以逐个商店地执行此操作，例如：

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

这将为我提供访问store_id = 60最多的5个用户

我想要做的是提供10个store_ids的列表，并针对每个商店获取访问该商店最多的5个用户

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

这样做是不起作用的，因为结尾处的Limit只返回5行，而不是每个商店的5行。

有什么想法可以实现这一点。我总是可以编写一个循环并逐个传递商店，但想知道是否有更好的方法。

- Gublooo

MySQL没有分析函数-ROW_NUMBER、RANK、DENSE_RANK-通常情况下，你会使用这些函数来解决这类问题。 - OMG Ponies

5个回答

2

重点关注的是您查询数据库的次数。如果您从脚本中多次查询，那么这只是资源的浪费，必须避免。也就是说，您不应该运行循环来通过增加某个值多次运行SQL。在您的情况下，从60到61等等。

解决方案1：创建一个视图以下是解决方案。

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

现在使用：

SELECT * from MyView

这很受限制，因为你不能使它变得动态。如果需要的是60到100而不是60到66呢？

解决方案2：使用存储过程。我不会详细介绍如何编写存储过程，因为现在已经很晚了，我要睡觉 :) 好吧，存储过程必须接受两个值：第一个是初始数字（60），第二个是计数（6）在存储过程内创建一个临时表格（游标）来存储数据，然后从初始数字开始运行循环，直到循环次数达到计数在您的情况下，从60到66 在循环内编写所需的脚本，用循环变量替换60。

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

并将结果附加到临时表（游标）中。

希望这能解决您的问题。很抱歉我不能给你代码。如果您仍然需要，请给我发送一条消息。明天早上我醒来后会给您。

- Rahul Prasad

1

UNION 可能是你正在寻找的。

-- 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

- Yada

1

如果您不打算保存关于用户何时访问店铺或类似信息的数据，那么每次用户访问店铺时，您可以简单地更新表格而不是追加新行。

操作方式类似如下：

INSERT INTO `user_store` (`user_id`, `store_id`, `visits`) VALUES ('USER', 'SHOP', 1)
ON DUPLICATE KEY UPDATE `visits` = `visits` + 1

但我认为这样做行不通，因为user_id和store_id都不是唯一的。您需要添加一个唯一主键，例如：user#store或其他内容。

另一个观点是将此数据（用户在商店中出现的频率）保存在单独的表中，该表包含ID、user_id、store_id、visits，并且每次在现有表中添加新行时都会增加visits。

要获取Top5，然后可以使用以下方法：

SELECT `visits`, `user_id` FROM `user_store_times` WHERE `store_id`=10 ORDER BY `visits` DESC LIMIT 5

- Tobias

谢谢你的ON DUPLICATE KEY UPDATE visits = visits + 1建议。 - Rahul Prasad

0

最简单的方法是为每个商店发出10个单独的查询。如果您使用参数化查询（例如使用PHP中的PDO），这将非常快，因为查询将被部分编译。

如果这仍然证明过于资源密集，则另一种解决方案是在商店表中缓存结果-即添加一个字段，列出每个商店的前5个用户作为简单的逗号分隔列表。这确实意味着您的数据库不会100％规范化，但这不应该是问题。

- DisgruntledGoat

嗯，这是一个有趣的想法——你是在建议——每次用户访问商店时，在那个时间点上进行检查并更新前5名用户列。我考虑过这种方法，但后来决定不采用它，认为为什么要永久存储可以通过一些简单的SQL检索的数据呢。 - Gublooo

@Gubloo：我的意思是你可以运行一个脚本（作为cron作业，或者在午夜后第一个访问的用户），该脚本将执行更新。你所说的可能仍然过于资源密集。不过你是对的 - 如果你可以通过简单的查询获取数据，那肯定更好，但看起来你接受的答案与简单相去甚远 ;) - DisgruntledGoat

尽管那个查询看起来很复杂，但我不确定它需要多少资源。但至少这是一种即时获取结果的简单方法。 - Gublooo

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Patrick · Accepted Answer

使用两个用户变量并计算相同连续的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的内容，因此条件<>是真的，然后进入第二个情况，只是用来改变值@prev为当前的store_id。这就是技巧，使我可以在同一条件下更改两个用户变量@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