Tag entropy density per month

1

Please login or register to vote for this query.

(click on this box to dismiss)

Smaller value means, that the asked questions were more concentrated around a few technologies. Higher value means, that we had a wider rainbow of technologies at that month. Note, it is a density, i.e. the effect of more or less tags/questions in the month are neutralized.

Stack Overflow

Q&A for professional and enthusiast programmers

-- Stirling-formula:
-- SELECT LOG(SQRT(2*3.14*@value)) + (LOG(@value) - 1) * @value

DECLARE @LogFact TABLE (
  N INT,
  LogFact FLOAT
);

DECLARE @n INT = 0
DECLARE @logSum FLOAT = 0

WHILE @n < 100000
BEGIN
  INSERT INTO @LogFact VALUES(@n, @logSum)
  SET @n = @n + 1
  SET @logSum = @logSum + LOG(@n)
END;

WITH LogFact AS (
  SELECT * FROM @LogFact
),
Raw AS (
  SELECT
    DATEFROMPARTS(
      DATEPART(yyyy, Posts.CreationDate),
      DATEPART(mm, Posts.CreationDate),
      1) AS Month,
    Posts.Id AS QId,
    PostTags.TagId AS TagId
  FROM Posts, PostTags
  WHERE
    Posts.Id = PostTags.PostId
),
Raw2 AS (
  SELECT
    Month,
    QId,
    TagId,
    60 / COUNT(*) OVER (PARTITION BY QId) AS TagWeight
  FROM Raw
),
RawTagPerMonth AS (
  SELECT
    Month,
    TagId AS TagId,
    SUM(TagWeight) / 60 AS WCount
  FROM Raw2
  GROUP BY Month, TagId
),
TagPerMonth AS (
  SELECT
    Month,
    TagId,
    WCount,
    CASE
      WHEN WCount < 100000 THEN LogFact.LogFact
      ELSE LOG(SQRT(2*3.14*WCount)) + (LOG(WCount) - 1) * WCount
    END AS LogFactCount
  FROM RawTagPerMonth
  LEFT JOIN LogFact ON RawTagPermonth.WCount = LogFact.N
),
Raw3 AS (
  SELECT
    Month,
    SUM(WCount) AS TagWCount,
    SUM(LogFactCount) AS LogFactSum
  FROM TagPerMonth
  GROUP BY Month
),
Raw4 AS (
  SELECT
    Month,
    TagWCount,
    CASE
      WHEN TagWCount < 100000 THEN LogFact.LogFact
      ELSE LOG(SQRT(2*3.14*TagWCount)) + (LOG(TagWCount) - 1) * TagWCount
    END AS LogFactTagCount,
    LogFactSum
  FROM Raw3
  LEFT JOIN LogFact ON Raw3.TagWCount = LogFact.N
  WHERE TagWCount > 0
)
SELECT
  Month,
  (LogFactTagCount - LogFactSum) / TagWCount AS EntropyDensity
FROM Raw4
ORDER BY Month;

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results