Tag Similarity by common occurance

0

Please login or register to vote for this query.

(click on this box to dismiss)

Craft CMS Meta

Q&A about the site for administrators, end users, developers and designers for Craft CMS

select count(t1.postId) as count_match,
       1.0*count(t1.postId)/tag1.Count as association_ratio, 
       -- t1.TagId,
       tag1.TagName as search_tag,
       tag1.Count,
       -- t2.TagId,
       tag2.TagName as similar_tag,
       tag2.Count
from PostTags t1
  inner join PostTags t2 on t1.PostId=t2.PostId
  inner join Tags as tag1 on tag1.id = t1.TagId
  inner join Tags as tag2 on tag2.id = t2.TagId
  inner join Posts on t1.PostId = Posts.Id
where t1.TagId <> t2.TagId
    -- t1.TagId<t2.TagId -- prevent double entries (symmetric)
    -- and Posts.score >=2 -- only consider tag combinations from posts having specific score
    and tag1.Count>100 -- only consider tags used ... times
    and tag2.Count>1000 -- only consider tags used ... times
group by t1.TagId, t2.TagId, tag1.TagName, tag2.TagName, tag1.Count, tag2.Count
order by association_ratio desc, count_match desc
-- offset 50000 rows

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results