Top posts by review entropy

1

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow en español

Preguntas y respuestas para programadores y profesionales de la informática

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 PRCount AS (
  SELECT PostId, ReviewTaskTypeId, COUNT(*) AS Count
  FROM ReviewTasks
  GROUP BY PostId, ReviewTaskTypeId
),
PCount AS (
  SELECT PostId, COUNT(*) AS Count
  FROM ReviewTasks
  GROUP BY PostId
)
SELECT TOP 100
  PCount.PostId AS [Post Link],
  PCount.PostId,
  MIN(PCountLf.LogFact) - SUM(PRCountLf.LogFact) AS Entropy
FROM PCount, PRCount, @LogFact PCountLf, @LogFact PRCountLf
WHERE
  PCount.PostId = PRCount.PostId
  AND
  PCountLf.N = PCount.Count
  AND
  PRCountLf.N = PRCount.Count
GROUP BY PCount.PostId
ORDER BY Entropy DESC, PCount.PostId

Enter Parameters

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