1

Please login or register to vote for this query.

(click on this box to dismiss)

Based on http://data.stackexchange.com/scifi/query/83415. The default parameters are good for scifi.SE. The second results table lists tags that didn't have any connections to other tags.

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

declare @CountAll int = (select count(*) from Posts where PostTypeId = 1)
-- RatioCap: OERatio Cap "Cap the OERatio at this value"
declare @RatioCap float = ##RatioCap:float?1000##

-- create #TagsMerged
select Tags.Id,
       Tags.TagName,
       count(distinct PostTags.PostId) as [Count]
into #TagsMerged
from Tags
left join TagSynonyms ts1 on ts1.TargetTagName = Tags.TagName and ts1.ApprovalDate is not null
left join TagSynonyms ts2 on ts2.SourceTagName = Tags.TagName and ts2.ApprovalDate is not null
left join Tags tstags on tstags.TagName = ts1.SourceTagName
inner join PostTags on PostTags.TagId in (Tags.Id, tstags.Id)
where ts2.SourceTagName is null
group by Tags.Id, Tags.TagName

-- create #PostTagsMerged
select PostTags.PostId, coalesce(tgt.Id, t.Id) as TagId--, min(t.Id) as OriginalTagId
into #PostTagsMerged
from PostTags
inner join Tags t on PostTags.TagId = t.Id
left join TagSynonyms ts on ts.SourceTagName = t.TagName
                         and ts.ApprovalDate is not null
left join Tags tgt on tgt.TagName = ts.TargetTagName
group by PostTags.PostId, coalesce(tgt.Id, t.Id)

-- make the OERatio table
SELECT TOP ##NumTags?10000##
    TagName Tag, Count TagCount
into #TagCounts
FROM #TagsMerged
WHERE TagName != 'untagged' and Count >= ##MinTagCount?100##
ORDER BY TagCount DESC, TagName

SELECT T1.TagName Tag1, T2.TagName Tag2, count(*) TagPairCount
into #TagPairCounts
FROM Posts P
  INNER JOIN #PostTagsMerged PT1 ON PT1.PostId = P.Id
  INNER JOIN #PostTagsMerged PT2 ON PT2.PostId = P.Id 
  INNER JOIN #TagsMerged T1 ON T1.Id = PT1.TagId 
  INNER JOIN #TagsMerged T2 ON T2.Id = PT2.TagId
WHERE T1.TagName < T2.TagName
GROUP BY T1.TagName, T2.TagName

SELECT
  TPC.Tag1 Tag1,
  TPC.Tag2 Tag2,
  CAST(TPC.TagPairCount as FLOAT) * CAST(@CountAll as FLOAT)
  / (CAST(TC1.TagCount as FLOAT) * CAST(TC2.TagCount as FLOAT))
  OERatio, --almost there, just to normalize
  TC1.TagCount Count1,
  TC2.TagCount Count2,
  TPC.TagPairCount Count12
INTO #OETable
FROM #TagPairCounts TPC 
  INNER JOIN #TagCounts TC1 ON TC1.Tag = TPC.Tag1
  INNER JOIN #TagCounts TC2 ON TC2.Tag = TPC.Tag2
WHERE
  CAST(TPC.TagPairCount as FLOAT) * CAST(@CountAll as FLOAT)
  / (CAST(TC1.TagCount as FLOAT) * CAST(TC2.TagCount as FLOAT)) >= ##MinOERatio?1.75##
  AND TPC.TagPairCount >= ##MinTagPairCount?2##

-- show tags that never matched, and a count of tags that did
SELECT TagsSearched, TagsMatched, Count
into #TagsMatched
FROM (SELECT Tag1 as TagsMatched FROM #OETable
      UNION
      SELECT Tag2 FROM #OETable) Matched
RIGHT JOIN
    (SELECT TOP ##NumTags## TagName as TagsSearched, Count
     FROM #TagsMerged
     WHERE Count >= ##MinTagCount##
     ORDER BY Count DESC, TagName) AS Searched
  ON Searched.TagsSearched = Matched.TagsMatched

-- insert loose nodes for any tags that didn't match. Comment this part
-- out if you want all nodes to have connections. This part will cause
-- warnings when importing the data into gephi.
insert into #OETable
select TagsSearched, TagsSearched, 0, Count, Count, 0
from #TagsMatched
where TagsMatched is null

-- show the results
SELECT Tag1, Tag2,
    case when OERatio > @RatioCap then @RatioCap else OERatio end as OERatio,
    Count1, Count2, Count12, @CountAll as CountAll
FROM #OETable
ORDER BY OERatio desc, Count1, Count2, Count12, Tag1, Tag2

-- show count of tags matched
select count(TagsSearched) as 'Tags searched',
       count(TagsMatched) as 'Tags matched',
       (select count(Id) from Tags where Count > 0) as 'All tags',
       format((select count(distinct PostId)
               from #TagsMatched
               inner join #TagsMerged on TagsMatched = #TagsMerged.TagName
               inner join #PostTagsMerged on #TagsMerged.Id = #PostTagsMerged.TagId)
              * 1.0 / @CountAll, 'p')
       as [Questions represented in tags matched],
       -- two subqueries here is probably inefficient
       format((select count(distinct PostId)
               from #TagsMatched
               inner join #TagsMerged on TagsSearched = #TagsMerged.TagName
               inner join #PostTagsMerged on #TagsMerged.Id = #PostTagsMerged.TagId)
              * 1.0 / @CountAll, 'p')
       as [Questions represented in tags searched]
from #TagsMatched

-- show some statistics on the tags matched
select MaxValues.TagName,
    max(OERatio) as [Max OERatio],
    max(PairCount) as [Max PairCount],
    Count as [Tag Count]
from (
   SELECT Tag1 as TagName, max(OERatio) as OERatio, max(Count12) as PairCount
   FROM #OETable
   GROUP BY Tag1
   UNION
   SELECT Tag2, max(OERatio), max(Count12)
   from #OETable
   group by Tag2) MaxValues
left join #TagsMerged on #TagsMerged.TagName = MaxValues.TagName
where OERatio > 0
group by MaxValues.TagName, Count
ORDER BY 2, 3 DESC, MaxValues.TagName

fork query

edited may 10 16 amaranth

created oct 29 12 Piotr Migdal