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

Q&A about the site for builders and users of networked sensors and control devices in the contexts of smart homes, industry automation, or environmental sensors

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

-- OERatio = 1 is for independent tags;
-- more means there is a positive correlations between them
-- a typical cut-off can be OERatio >= 4
-- to be set manually

-- also, if enstead of 256 tags (good for StackOverflow)
-- you want less (e.g. 64 or 128) - set it 

WITH
  PostTypeCounts AS
  (
    SELECT
      P.PostTypeId PostTypeId,
      count(*) PostTypeCount
    FROM
      Posts P
    GROUP BY
      P.PostTypeId
  ),
  TagCounts AS
  (
    SELECT TOP 64 --modify manually, if you like
      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
  TC1.TagCount Count1,
  TC2.TagCount Count2,
  TPC.TagPairCount Count12,
  PTC.PostTypeCount CountAll
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
WHERE
  CAST((TPC.TagPairCount * PTC.PostTypeCount) as FLOAT)
  / CAST((TC1.TagCount * TC2.TagCount) AS FLOAT) >= 2
ORDER BY
  OERatio DESC
--  CAST(TPC.TagPairCount as FLOAT)
--  / CAST((TC1.TagCount * TC2.TagCount) AS FLOAT) DESC

view current version

edited oct 30 12

created oct 29 12 Piotr Migdal