0

Please login or register to vote for this query.

(click on this box to dismiss)

Find tags that have a higher frequency in questions with another given tag.

Preguntas y respuestas sobre el sitio para programadores y profesionales de la informática

-- Correlated tags
-- Find tags that have a higher frequency in questions with another given tag.

WITH Tot_Posts AS
   ( SELECT COUNT(*) as cnt
       FROM Posts
   )
   , Overall_Per_Tag_Freq AS
   ( SELECT p.TagId as tid, (1.*COUNT(*))/(1.*tq.cnt) as freq
       FROM PostTags p
      CROSS JOIN Tot_Posts tq
      GROUP BY p.TagId, tq.cnt
   )
   , Posts_With_Given_Tag AS
   ( SELECT p.PostId as PostId, (1.*(COUNT(*) OVER (PARTITION BY 1))) as cnt
       FROM PostTags p
       JOIN Tags t ON t.Id = p.TagId
      WHERE t.TagName = ##TagName:string##
   )
   , RelFreqs AS
   ( SELECT t.TagName as tname, ((1.*(COUNT(*))/(1.*pwgt.cnt)))/(1.*optq.freq) as relfreq
       FROM Posts_With_Given_Tag pwgt
       JOIN PostTags p ON pwgt.PostId = p.PostId
       JOIN Overall_Per_Tag_Freq optq ON optq.tid = p.TagId
       JOIN Tags t ON t.Id = p.TagId
      WHERE t.TagName <> ##TagName:string##
      GROUP BY t.Id, t.TagName, optq.freq, pwgt.cnt
   )
SELECT rf.tname as name, rf.relfreq as ratio
  FROM RelFreqs rf
 WHERE rf.relfreq > 1.
 ORDER BY rf.relfreq DESC

fork query

edited mar 13 12 anonymous

created nov 9 11 Benoit