用于在定义的集合中查找具有共同标签的用户的 SQL 查询

发布于 2024-11-09 08:25:56 字数 872 浏览 0 评论 0原文

amydeshane  0.180751    games
amydeshane  0.178772    video

我需要一个查询来查找这样的实例，其中用户在一组中拥有多个标签。

例如 where Category in ('games','video','flash')

我希望与该集合相同的标签越多，它们的排名就越高。例如“amydeshane”应该有 matching_terms = 2

有什么想法吗？

现在这是我的查询，但它没有给我需要的结果，因为我希望“amydeshane”排名更高，因为它包含更多匹配的标签，

SELECT        TOP (10) Username, tfidf AS TotalUsed
FROM            UserInfo
WHERE        (Category IN ('video', 'graphics', 'editor', 'games', 'youtube'))
GROUP BY Username, tfidf
HAVING        (COUNT(Username) > 1)
ORDER BY TotalUsed DESC

这些是结果：

kingjames23 0.626885
F_David 0.406635
bjhscomputers   0.401741
jaw6    0.347777
lkw5151604  0.257147
anniemalahus    0.242461
opusfluke   0.240047
pporto  0.235550
amydeshane  0.180751
amydeshane  0.178772

原文

amydeshane  0.180751    games
amydeshane  0.178772    video

I need a query that finds instances like this, where the user has more than 1 tag from a set.

for instance where Category in ('games','video','flash')

I want that the more tags that are in common with the set, the higher they are ranked. for instance 'amydeshane' should have matching_terms = 2

any ideas?

right now this is my query, but it is not giving me the results I need because I want that 'amydeshane' is ranked higher as it contains more tags matching

SELECT        TOP (10) Username, tfidf AS TotalUsed
FROM            UserInfo
WHERE        (Category IN ('video', 'graphics', 'editor', 'games', 'youtube'))
GROUP BY Username, tfidf
HAVING        (COUNT(Username) > 1)
ORDER BY TotalUsed DESC

These are the results:

kingjames23 0.626885
F_David 0.406635
bjhscomputers   0.401741
jaw6    0.347777
lkw5151604  0.257147
anniemalahus    0.242461
opusfluke   0.240047
pporto  0.235550
amydeshane  0.180751
amydeshane  0.178772

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

束缚ｍ 2024-11-16 08:25:56

select username, sum(tfidf) as totalused
from userinfo
where category in(...)
group by username
having count(category) > 1
order by sum(tfidf) desc

select username, sum(tfidf) as totalused
from userinfo
where category in(...)
group by username
having count(category) > 1
order by sum(tfidf) desc

回复收藏 0 原文

触ぅ动初心 2024-11-16 08:25:56

我从你的结果中猜测其他每个用户只有 1 个标签？

我不确定这是否是您想要的，但您可以对用户名字段进行计数并在 Totalused 字段上进行求和...

SELECT TOP (10) Username, COUNT(Username) AS TagCount, SUM(tfidf) AS TotalUsed 
FROM UserInfo 
WHERE (Category IN ('video', 'graphics', 'editor', 'games', 'youtube')) 
GROUP BY Username, tfidf 
HAVING (COUNT(Username) > 1) 
ORDER BY COUNT(Username),TotalUsed DESC

I'm guessing from your results that each of the other users only has 1 tag?

I'm not sure if it's what you want, but you could do a count on the username field and a sum on the totalused field...

SELECT TOP (10) Username, COUNT(Username) AS TagCount, SUM(tfidf) AS TotalUsed 
FROM UserInfo 
WHERE (Category IN ('video', 'graphics', 'editor', 'games', 'youtube')) 
GROUP BY Username, tfidf 
HAVING (COUNT(Username) > 1) 
ORDER BY COUNT(Username),TotalUsed DESC

回复收藏 0 原文

~没有更多了~