CODEWITHSUNDEEP
sql
michelle
michelle

Reputation: 2807

An sql query to find users with common tags in a defined set

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

Upvotes: 1

Views: 317

Answers (2)

TabbyCool
TabbyCool

Reputation: 2839

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

Upvotes: 1

davek
davek

Reputation: 22925

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

Upvotes: 3

Related Questions