Observed to expected ratio (o/e) for tag pairs for n most popular tags

2

Please login or register to vote for this query.

(click on this box to dismiss)

P(tag1 and tag2)/[P(tag1) P(tag2)] for 256 most popular tags, see http://bit.ly/map-of-so

German Language Meta

Q&A about the site for speakers of German wanting to discuss the finer points of the language and translation

-- for plots like this one: 
-- http://bit.ly/map-of-so
-- P(tag1 and tag2)/[P(tag1) P(tag2)] is
-- #tag1tag2 #questions / (#tag1 #tag2)

-- DECLARE @MinOERatio int = ##MinOERatio##
-- typically >2; OERatio = 1 is for independent tags;
-- more means there is a positive correlations between them 

WITH
  PostTypeCounts AS
  (
    SELECT
      P.PostTypeId PostTypeId,
      count(*) PostTypeCount
    FROM
      Posts P
    GROUP BY
      P.PostTypeId
  ),
  TagCounts AS
  (
    SELECT TOP 256
      T.TagName Tag,
      count(*) TagCount
    FROM Posts P
      INNER JOIN PostTags PT ON PT.PostId = P.Id
      INNER JOIN Tags T ON T.Id = PT.TagId
    WHERE
      P.PostTypeId = 1 -- only questions
    GROUP BY
      T.TagName
    --ORDER BY
    --    TagCount DESC
  ),
  TagPairCounts AS
  (
    SELECT
      T1.TagName Tag1,
      T2.TagName Tag2,
      count(*) TagPairCount
    FROM Posts P
      INNER JOIN PostTags PT1 ON PT1.PostId = P.Id
      INNER JOIN PostTags PT2 ON PT2.PostId = P.Id 
      INNER JOIN Tags T1 ON T1.Id = PT1.TagId 
      INNER JOIN Tags T2 ON T2.Id = PT2.TagId
    WHERE
      T1.TagName < T2.TagName
    GROUP BY
      T1.TagName,
      T2.TagName
    --ORDER BY
    --    TagPairCount DESC
    )
SELECT TOP 100
  TPC.Tag1 Tag1,
  TPC.Tag2 Tag2,
  CAST((TPC.TagPairCount * PTC.PostTypeCount) as FLOAT)
  / CAST((TC1.TagCount * TC2.TagCount) AS FLOAT)
  OERatio --almost there, just to normalize
FROM TagPairCounts TPC 
  INNER JOIN TagCounts TC1 ON TC1.Tag = TPC.Tag1
  INNER JOIN TagCounts TC2 ON TC2.Tag = TPC.Tag2
  INNER JOIN PostTypeCounts PTC ON PTC.PostTypeId = 1
-- HAVING
--  OERatio >= 4
ORDER BY
  OERatio DESC
--  CAST(TPC.TagPairCount as FLOAT)
--  / CAST((TC1.TagCount * TC2.TagCount) AS FLOAT) DESC

Enter Parameters

Options:
Switch to main site
loading Hold tight while we fetch your results