Bayesian Estimate and Root Mean Square
Sometimes you face the situation where you have some items ranked, and someone always tries to bump the ranking by voting a "perfect 100" to a particular item.
And when you rank the items with average function or root mean square, that single "perfect 100" vote still bumps the item at top of ranking.
Well, have you looked a Bayesian Estimate?
DECLARE @Sample TABLE
(
userID INT,
vote INT
)
INSERT @Sample
SELECT 3, 40 UNION ALL
SELECT 3, 60 UNION ALL
SELECT 0, 100 UNION ALL
SELECT 1, 100 UNION ALL
SELECT 1, 100 UNION ALL
SELECT 1, 90 UNION ALL
SELECT 1, 100 UNION ALL
SELECT 1, 90 UNION ALL
SELECT 1, 100 UNION ALL
SELECT 1, 90 UNION ALL
SELECT 2, 60 UNION ALL
SELECT 2, 70 UNION ALL
SELECT 2, 100 UNION ALL
SELECT 2, 90 UNION ALL
SELECT 2, 60 UNION ALL
SELECT 2, 20
DECLARE @minVotes INT
SET @minVotes = 30
-- Average
SELECT userID,
AVG(1.0E * vote) AS Average
FROM @Sample
GROUP BY userID
--HAVING COUNT(*) >= @minVotes
ORDER BY userID
-- Root Mean Square
SELECT userID,
SQRT(SUM(Items * thePow) / SUM(Items)) AS [Root Mean Square]
FROM (
SELECT userID,
POWER(vote, 2.0E) AS thePow,
1.0E * COUNT(*) AS Items
FROM @Sample
GROUP BY userID,
vote
) AS d
GROUP BY userID
--HAVING SUM(Items) >= @minVotes
ORDER BY userID
-- Bayesian estimate
SELECT s.userID,
1.0E * COUNT(*) /(1.0E * COUNT(*) + @minVotes) * AVG(s.vote) +(@minVotes /(1.0E * COUNT(*) + @minVotes)) * MIN(c.c) AS [Bayesian estimate]
FROM @Sample AS s
CROSS JOIN (
SELECT AVG(1.0E * vote) AS c
FROM @Sample
) AS c
GROUP BY s.userID
--HAVING COUNT(*) >= @minVotes
ORDER BY s.userID