P(tag1 and tag2)/[P(tag1) P(tag2)] for 256 most popular tags, see http://bit.ly/map-of-so
Q&A for linguists, teachers, and students wanting to discuss the finer points of the Latin language
-- 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 WHERE P.OwnerUserId=##UserId## 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 AND T.TagName != 'untagged' AND P.OwnerUserId=##UserId## 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 AND P.OwnerUserId=##UserId## 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