Внедрение оценки Wilson в SQL

Ответ 1

Вместо того, чтобы пытаться манипулировать алгоритмом Уилсона, чтобы сделать 5-звездную рейтинговую систему. Почему бы вам не взглянуть на другой алгоритм? Это то, что imdb использует для своих 250: байесовская оценка

Как для объяснения математики в алгоритме Вильсона, ниже была размещена ссылка на ваш первый пост. Он написан на Ruby.

require 'statistics2'

def ci_lower_bound(pos, n, power)
    if n == 0
        return 0
    end
    z = Statistics2.pnormaldist(1-power/2)
    phat = 1.0*pos/n
    (phat + z*z/(2*n) - z * Math.sqrt((phat*(1-phat)+z*z/(4*n))/n))/(1+z*z/n)
end

Если вам нужен еще один пример, вот один из PHP: http://www.derivante.com/2009/09/01/php-content-rating-confidence/

Изменить: Кажется, что производного нет больше. Вы можете увидеть исходную статью на archive.org - https://web.archive.org/web/20121018032822/http://derivante.com/2009/09/01/php-content-rating-confidence/, и я добавил код из следующей статьи.

class Rating
{
  public static function ratingAverage($positive, $total, $power = '0.05')
  {
    if ($total == 0)
      return 0;
    $z = Rating::pnormaldist(1-$power/2,0,1);
    $p = 1.0 * $positive / $total;
    $s = ($p + $z*$z/(2*$total) - $z * sqrt(($p*(1-$p)+$z*$z/(4*$total))/$total))/(1+$z*$z/$total);
    return $s;
  }
  public static function pnormaldist($qn)
  {
    $b = array(
      1.570796288, 0.03706987906, -0.8364353589e-3,
      -0.2250947176e-3, 0.6841218299e-5, 0.5824238515e-5,
      -0.104527497e-5, 0.8360937017e-7, -0.3231081277e-8,
      0.3657763036e-10, 0.6936233982e-12);
    if ($qn < 0.0 || 1.0 < $qn)
      return 0.0;
    if ($qn == 0.5)
      return 0.0;
    $w1 = $qn;
    if ($qn > 0.5)
      $w1 = 1.0 - $w1;
    $w3 = - log(4.0 * $w1 * (1.0 - $w1));
    $w1 = $b[0];
    for ($i = 1;$i <= 10; $i++)
      $w1 += $b[$i] * pow($w3,$i);
    if ($qn > 0.5)
      return sqrt($w1 * $w3);
    return - sqrt($w1 * $w3);
  }
}

Что касается этого в SQL, SQL имеет все эти математические функции уже в этой библиотеке. Если бы я был вами, я бы сделал это в вашем приложении. Сделайте так, чтобы ваше приложение обновляло вашу базу данных так часто (часы??) Вместо того, чтобы делать это "на лету", или ваше приложение станет очень медленным.

Ответ 2

Что касается вашего первого вопроса (приспосабливая формулу к 5-звездной системе), я бы согласился с Полом Креси.

формула преобразования: [3 +/- я stars → я up/down-votes] (3 звезды → 0)

пример: 4 звезды → +1 up-vote, 5 звезд → +2, 1 → -2 и т.д.

Я бы заметил, что вместо нижней границы интервала, который вычисляют как функции ruby, так и php, я бы просто вычислил гораздо более простую среднюю точку wilson:

(x + (z ^ 2)/2)/(n + z ^ 2)

где:
n = Sum (up_votes) + Sum (| down_votes |)
x = (положительные голоса)/n = Сумма (up_votes)/n
z = 1,96 (фиксированное значение)

Ответ 3

Взяв Williams ссылку на php-решение http://www.derivante.com/2009/09/01/php-content-rating-confidence/ и сделав вашу систему такой, чтобы она была просто поведенческой и отрицательной (5 звезд могли быть 2 pos, 1 запуск может быть 2 отрицательным, возможно), тогда было бы довольно легко преобразовать его в T-SQL, но вам было бы намного лучше сделать это в логике на стороне сервера.

Ответ 4

Автор первая ссылка недавно добавила реализацию SQL в свой пост.

Вот он:

SELECT widget_id, ((positive + 1.9208) / (positive + negative) - 
               1.96 * SQRT((positive * negative) / (positive + negative) + 0.9604) / 
                      (positive + negative)) / (1 + 3.8416 / (positive + negative)) 
   AS ci_lower_bound FROM widgets WHERE positive + negative > 0 
   ORDER BY ci_lower_bound DESC;

Можно ли разместить это в 5-звездочной рейтинговой системе вне меня.

Ответ 5

Я загрузил реализацию Oracle PL/SQL в https://github.com/mattgrogan/stats_wilson_score

create or replace function stats_wilson_score(

/*****************************************************************************************************************

Author      : Matthew Grogan
Website     : https://github.com/mattgrogan
Name        : stats_wilson_score.sql 
Description : Oracle PL/SQL function to return the Wilson Score Interval for the given proportion. 
Citation    : Wilson E.B. J Am Stat Assoc 1927, 22, 209-212

Example:
  select 
    round(29 / 250, 4) point_estimate, 
    stats_wilson_score(29, 250, 0.10, 'LCL') lcl, 
    stats_wilson_score(29, 250, 0.10, 'UCL') ucl
  from dual;

******************************************************************************************************************/

  x integer,  -- Number of successes
  m integer,  -- Number of trials
  alpha number default 0.95,  -- Probability of a Type I error
  return_value varchar2 default 'LCL' -- LCL = Lower control limit, UCL = upper control limit
)

return number is

  z float(10);
  phat float(10)  := 0.0;
  lcl float(10)   := 0.0;
  ucl float(10)   := 0.0;

begin

  if m = 0 then
    return(0);
  end if;

  case alpha
    when 0.10 then z := 1.644854;
    when 0.05 then z := 1.959964;
    when 0.01 then z := 2.575829;
    else return(null); -- No Z value for this alpha
  end case;

  phat := x/m;

  lcl := (phat + z*z/(2*m) - z * sqrt( (phat * (1-phat) ) / m + z * z / (4 * (m * m)) ) ) / (1 + z * z / m);
  ucl := (phat + z*z/(2*m) + z * sqrt((phat*(1-phat)+z*z/(4*m))/m))/(1+z*z/m);

  case return_value
    when 'LCL' then return(lcl);
    when 'UCL' then return(ucl);
    else return(null);
  end case;

end;
/
grant execute on stats_wilson_score to public;