algorithm - Implementing Wilson Score in SQL -

We have a relatively small table that we want to sort by rating, or by using the appropriate equivalent. I am quite a smart person, but my mathematical fu is not much stronger to understand this:

The formula given above, I have been told that the positive / Negative (thumbs up / thumbs down) Voting system I have never taken a statistics course, and I've done advanced math for 15 years. I do not have any clue that the small fish that is p mean, or what z is indicative of the last fish below.

I would like to know two things:

1. Can this formula be changed to accommodate this 5-star rating system? I found, but the author expresses his doubts about the accuracy of his formula.

2. How can this formula be expressed in the SQL function? Note that I do not need to calculate and sort real time. I can score calculations and can be cached daily.

3. I created some Microsoft SQL Server? Ignoring

To do a 5 star rating system Instead of trying to manipulate Wilson's algorithm, Why do not you see a different algorithm? IMDB uses it for its top 250:

To explain mathematics to Wilson's algorithm, it was posted on the link in your first post below. It's written in Ruby.

  requires 'statistics2' def ci_lower_bound (position, en, power) if n == 0 return 0 end z = Statistics2.pnormaldist (1-power / 2) Phat = 1.0 * Position / N (Phat + z * z / (2 * n) - Z * Math.sqrt ((Phat * (1-Phat) + z * z / (4 * N)) / n)) / (1 + z * Z / n) End  

If you want another instance, then this is in PHP:

Edit: It seems that derivante.com now Not nearby, you can see the original article on archive.org - and I have added the code to the article below

  class rating {public stationary commemoration The RatingAverage ($ Positive, $ total, $ = power. '0.05') {if ($ total == 0) return 0; $ Z = Rating :: Permalink (1- $ power / 2,0,1); $ P = 1.0 * $ positive / $ total; $ S = ($ P $ + z * $ z / ($ 2 total) - $ z * sqrt (($ P * ($ 1p) + $ z * $ z / (4 * $ total)) / $ Total)) / (1 + $ z * $ z / $ total); Return $ s; } Public stable function pnormaldist ($ qn) {$ B = array (1.57,07,96,288, 0.03706 9 87 06, -0.8364353589e -3, -0.2250947176e -3, 0.6841218299e -5, 0.5824238515e -5 , -0.104527497e -5, 0.8360937017e-7, -0.3231081277-8, 0.3657763036-10, 0.6 936233982 -12); If ($ qn & lt; 0.0 || 1.0 & lt; $ qn) return 0.0; If returns ($ qn == 0.5) 0.0; $ W1 = $ qn; If ($ qn> 0.5) $ w1 = 1.0 - $ w1; $ W3 = - Logs (4.0 * $ w1 * (1.0 - $ w1)); $ W1 = $ b [0]; For ($ i = 1; $ i & lt; = 10; $ i ++) $ w1 + = $ b [$ i] * Pau ($ w3, $ i); If ($ qn> 0.5) return sqrt ($ w1 * $ w3); Return - sqrt ($ w1 * $ w3); }}  

To do this in SQL, the SQL already has all the Math functions present in the Library. If I were you, then I would like to do it in your application. Instead of doing so on the fly, update your application every time (hours? Days?) In your database, your application will be very slow.