1

Please login or register to vote for this query.

(click on this box to dismiss)

Calculates the correlation coefficient between all tag pairs. 0 correlation means that the tags are independent from each other. Negative correlation means that having a tag makes unlikely to have the other tag. Positive correlation means that a question tagged with one of them, makes likely to be tagged by the other.

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

DECLARE @TotalQ INT;

SELECT @TotalQ = COUNT(*) FROM Posts WHERE PostTypeId = 1;

WITH Raw AS (
  SELECT
    A.TagId AS TagA,
    B.TagId AS TagB,
    COUNT(*) AS Count
  FROM PostTags A, PostTags B, Tags TagsA, Tags TagsB
  WHERE
    A.PostId = B.PostId
    AND
    A.TagId <> B.TagId
    AND
    A.TagId = TagsA.Id
    AND
    B.TagId = TagsB.Id
    AND
    TagsA.Count >= ##MinTagCount?int:5##
    AND
    TagsB.Count >= ##MinTagCount##
  GROUP BY A.TagId, B.TagId
),
Raw2 AS (
  SELECT
    Raw.TagA,
    A.Count AS CountA,
    Raw.TagB,
    B.Count AS CountB,
    Raw.Count AS CountBoth,
    (1.0 * Raw.Count * @TotalQ - 1.0 * A.Count * B.Count)
      /
      SQRT(1.0
        * A.Count * (@TotalQ - A.Count)
        * B.Count * (@TotalQ - B.Count))
      AS Correlation
  FROM Tags A, Tags B, Raw
  WHERE
    Raw.TagA = A.Id
    AND
    Raw.TagB = B.Id
    AND
    A.TagName < B.TagName
),
Raw3 AS (
  SELECT
    *,
    CASE
      WHEN Correlation < 0 THEN -Correlation
      ELSE Correlation
    END AS AbsCorrelation
  FROM Raw2
)
SELECT TOP ##TopN:int?100##
  A.TagName,
  Raw3.CountA,
  B.TagName,
  Raw3.CountB,
  Raw3.CountBoth,
  Raw3.Correlation
FROM Raw3, Tags A, Tags B
WHERE
  Raw3.TagA = A.Id
  AND
  Raw3.TagB = B.Id
ORDER BY
  CASE
    WHEN ##AbsoluteCorrelation:int?0## <> 0
    THEN
      CASE
        WHEN ##IsIncreasing:int?0## <> 0 THEN Raw3.AbsCorrelation
        ELSE -Raw3.AbsCorrelation
      END
    ELSE
    CASE
      WHEN ##IsIncreasing:int## <> 0 THEN Raw3.Correlation
      ELSE -Raw3.Correlation
    END
  END

fork query

edited feb 24 20

created feb 24 20 peterh - PLEASE DELETE THIS ACCOUNT