gpt4 book ai didi

mysql - 给定一个用户,选择具有最常见评分的用户

转载 作者:行者123 更新时间:2023-11-29 08:56:58 24 4
gpt4 key购买 nike

假设我有一个评级表:

create table ratings (
user_id int unsigned not null,
post_id int unsigned not null,
rating set('like', 'dislike') not null,
primary key (user_id, post_id)
);

对于id 1的给定用户,我如何选择具有更多共同喜欢的用户?不喜欢的用户有哪些共同点?具有更多评分(喜欢或不喜欢)的用户有什么共同点?我想这些查询会非常相似,但我还无法弄清楚其中的任何一个。我将更新我取得的任何进展。

感谢任何帮助,谢谢!

最佳答案

select
r1.user_id as user1
,r2.user_id as user2
,r1.rating as rating
,count(*) as num_matching_ratings
from
ratings r1
inner join ratings r2
on r1.post_id = r2.post_id
and r1.rating = r2.rating
and r1.user_id <> r2.user_id --don't want to count
--matches with self
where
r1.user_id = 1 -- change this to any user, or use a
-- variable to increase reusebility
and r1.rating = 'like' -- set this to dislike to common dislikes
group by
r1.user_id
,r2.user_id
,r1.rating
having
count(*) > 1 --show only those with more than 1 in common
order by
count(*) desc
/* limit 1 -- uncomment to show just the top match */

通过将表连接在一起,我们可以计算第二个用户对文章进行类似评分的次数。此查询将返回从最常见到最不常见的评估。如果取消注释“limit 1”语句,它将仅返回最常见的匹配项。

关于mysql - 给定一个用户,选择具有最常见评分的用户,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/9708702/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com