# Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

## 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

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

Print | posted on Monday, October 27, 2008 11:14 AM | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Comments have been closed on this topic.