Гауссово случайное распределение в Postgresql

У меня есть таблица, скажем, 250 URL-адресов:

create table url (
  id serial,
  url varchar(64)
)

Каждый из этих URL-адресов соответствует веб-сайту. Каждый из сайтов имеет разную популярность. Предположим, что id=125 (тот, что с центром в гауссовой диаграмме) является наиболее популярным, а id=1 или id=250 — наименее популярным.

Я хочу заполнить таблицу «журнал», подобную следующей, со значением URL-адреса среди тех, которые указаны в таблице «url», но принимая во внимание, что разные URL-адреса могут появляться чаще (например, URL-адрес с идентификатором 125 будет самый популярный).

create table log (
  id serial,
  url_id integer
)

Я хочу избежать использования random(), так как он однообразен и не очень "реален".

Как этого можно добиться с помощью Postgresql?


postgresql random statistics gaussian
person SCO    schedule 24.02.2012    source источник
comment
Почему вы предполагаете, что популярность или рейтинг имеет распределение Гаусса?   -  person wildplasser    schedule 24.02.2012
comment
Вы можете рассчитать любое распределение, используя PDF этого распределения, используя RAND (который дает значения от 0 до 1, верно?). Для гауссовского дистрибутива это будет 1/2(1 + erf(x-mu)/sqrt(2sigma^2)) - см. en.wikipedia.org/wiki/Normal_distribution   -  person Roman Luštrik    schedule 24.02.2012
comment
@wildplasser: потому что этот закон кажется довольно хорошим для того, что я пытаюсь смоделировать. Я допускаю, что это мог быть любой другой!   -  person SCO    schedule 24.02.2012

Ответы (5)


arrow_upward
11
arrow_downward

Сумма 12 равномерных распределений в диапазоне [0, 1) является хорошим приближением к распределению Гаусса, ограниченному в диапазоне [0, 12). Затем это можно легко масштабировать, умножая на константу, а затем добавляя/вычитая константу.

select
    random() + 
    random() + 
    random() +
    random() + 
    random() + 
    random() +
    random() + 
    random() + 
    random() +
    random() + 
    random() + 
    random();

http://books.google.com/books?id=EKA-yeX2GVgC&pg=PA185&lpg=PA185&dq=%22sum+of+12+uniform+random+variables%22&source=bl&ots=YfwwE0fBB3&sig=HX9J9Oe6x316kVL8uamDU_GOsn4&hl=en&sa=X&ei=bJLZUur1GozaqwGHm4DQDQ&ved=0CEUQ6AEwAw#v=onepage&q=%22sum%20of%2012%20uniform%20random%20variables%22&f=false

person user2179977    schedule 17.01.2014
comment
Я принял это, потому что нашел это самым простым и элегантным способом, какой бы язык ни использовался. Спасибо всем другим участникам. - person SCO; 21.08.2014

arrow_upward
9
arrow_downward

Я искал способ генерировать числа в соответствии с распределением Гаусса и впервые нашел этот пост. Вот почему я делюсь тем, что я нашел сразу после:

По крайней мере, начиная с PostgreSQL 8.4, существует дополнительный модуль под названием tablefunc (http://www.postgresql.org/docs/current/static/tablefunc.html).

Он предлагает функцию normal_rand(n, mean, stddev), генерирующую n псевдослучайных чисел с использованием распределения Гаусса (поэтому эта функция возвращает набор значений, обычно используемый в предложении FROM). Однако если вы установите n равным 1, его можно использовать как функцию, возвращающую значение, а не набор значений.

Учитывая таблицу nb10, содержащую 10 записей, два следующих запроса возвращают набор из 10 псевдослучайных чисел в соответствии со стандартным распределением Гаусса (среднее значение = 0, стандартное отклонение = 1).

SELECT normal_rand(1, 0, 1) FROM nb10;

и

SELECT * from normal_rand(10, 0, 1);

Я надеюсь, что это может помочь кому-нибудь в будущем... :-)

Чтобы конкретно ответить на ваш вопрос, вы можете использовать что-то вроде:

SELECT floor(random_rand(1, 0, 1) * 250 + 125);

К сожалению, с помощью этого запроса можно получить ответ не в диапазоне [0, 249]. Например, вы можете:

  • используйте рекурсивный запрос, который я считаю немного излишним, для отбрасывания значений, не входящих в диапазон [0, 249], или
  • сделайте свой выбор в цикле на ваш основной язык, принимая значение, только если оно находится в диапазоне [0, 249], или

  • используйте оператор по модулю, чтобы оставаться в диапазоне [0, 250[, я думаю, что это лучшее решение, хотя оно немного меняет гауссову кривую. Вот окончательный запрос, который я предлагаю вам использовать (уловки по модулю/+/по модулю заключаются в том, что -x по модулю y с положительным числом x дает отрицательное число в PostgreSQL, что неплохо: p):

    SELECT ((floor(normal_rand(1,0,1)*250 + 125)::int % 250) + 250) % 250 as v;
person Fabian Pijcke    schedule 04.07.2013

arrow_upward
2
arrow_downward

Простой факт заключается в том, что вы хотите создать свою собственную функцию, которая оборачивает rand() во что-то, что явно или неявно обеспечивает гауссово распределение.

У меня нет достаточного статистического опыта, чтобы рассказать вам, как преобразовать равномерное распределение в гауссово, но вам придется написать преобразователь. Примерно так, как указано на http://www.perlmonks.org/?node_id=26889 ( если вам не нравится Perl, вы, вероятно, могли бы переписать это на pl/pgsql или даже на простой SQL).

CREATE OR REPLACE FUNCTION gaussian_rand() RETURNS numeric LANGUAGE PLPERL VOLATILE AS
$$
    my ($u1, $u2);  # uniformly distributed random numbers
    my $w;          # variance, then a weight
    my ($g1, $g2);  # gaussian-distributed numbers

    do {
        $u1 = 2 * rand() - 1;
        $u2 = 2 * rand() - 1;
        $w = $u1*$u1 + $u2*$u2;
    } while ( $w >= 1 );

    $w = sqrt( (-2 * log($w))  / $w );
    $g2 = $u1 * $w;
    $g1 = $u2 * $w;
    # return both if wanted, else just one
    return $g1;

$$;
person Chris Travers    schedule 27.03.2013

arrow_upward
1
arrow_downward

Модуль tablefunc предоставляет случайную функцию с нормальным распределением. Вы можете проверить, установлен ли он, используя:

SELECT normal_rand(1, 0, 1); -- generates 1 single value with mean 0 and a standard deviation of 1

Приведенный выше запрос должен генерировать одно значение в нормальном распределении.

Если он у вас не установлен, попробуйте следующее:

CREATE EXTENSION "tablefunc";

В противном случае вам нужно будет войти в систему как привилегированным пользователем и установите модуль.

person mgoldwasser    schedule 21.11.2017
comment
О, это тоже чрезвычайно интересно и теперь открывает горизонты, например, для сводных таблиц. Большое тебе спасибо ! - person SCO; 26.11.2017

arrow_upward
0
arrow_downward

Вы также можете реализовать это непосредственно во встроенном языке PL/PgSQL.

create or replace
function random_gauss( avg real = 0, stddev real = 1 )
returns real language plpgsql as $$
declare x1 real; x2 real; w real;
begin
  loop
    x1 = 2.0 * random() - 1.0;
    x2 = 2.0 * random() - 1.0;
    w = x1*x1 + x2*x2;
    exit when w < 1.0;
  end loop;
  return avg + x1 * sqrt(-2.0*ln(w)/w) * stddev;
end; $$;

with data as (
  select t, random_gauss(100,15)::integer score from generate_series(1,1000000) t
)
select
  score,
  sum(1),
  repeat('=',sum(1)::integer/500) bar
from data
where score between 60 and 140
group by score
order by 1;

rollback;

Это дает нам что-то вроде этого из выборки из 1 миллиона чисел со средним значением 100 и стандартным отклонением 15.

 score |  sum  |                          bar
-------+-------+--------------------------------------------------------
    60 |   764 | =
    61 |   893 | =
    62 |  1059 | ==
    63 |  1269 | ==
    64 |  1524 | ===
    65 |  1740 | ===
    66 |  1990 | ===
    67 |  2346 | ====
    68 |  2741 | =====
    69 |  3160 | ======
    70 |  3546 | =======
    71 |  4109 | ========
    72 |  4633 | =========
    73 |  5252 | ==========
    74 |  5952 | ===========
    75 |  6536 | =============
    76 |  7429 | ==============
    77 |  8140 | ================
    78 |  9061 | ==================
    79 | 10063 | ====================
    80 | 10844 | =====================
    81 | 11911 | =======================
    82 | 13180 | ==========================
    83 | 13880 | ===========================
    84 | 15111 | ==============================
    85 | 16016 | ================================
    86 | 17310 | ==================================
    87 | 18262 | ====================================
    88 | 19615 | =======================================
    89 | 20400 | ========================================
    90 | 21186 | ==========================================
    91 | 22190 | ============================================
    92 | 23103 | ==============================================
    93 | 24150 | ================================================
    94 | 24327 | ================================================
    95 | 24992 | =================================================
    96 | 25505 | ===================================================
    97 | 25868 | ===================================================
    98 | 26146 | ====================================================
    99 | 26574 | =====================================================
   100 | 27104 | ======================================================
   101 | 26599 | =====================================================
   102 | 26345 | ====================================================
   103 | 25940 | ===================================================
   104 | 25485 | ==================================================
   105 | 25157 | ==================================================
   106 | 24827 | =================================================
   107 | 23844 | ===============================================
   108 | 23262 | ==============================================
   109 | 22211 | ============================================
   110 | 21326 | ==========================================
   111 | 20315 | ========================================
   112 | 19496 | ======================================
   113 | 18026 | ====================================
   114 | 17182 | ==================================
   115 | 16026 | ================================
   116 | 14979 | =============================
   117 | 13959 | ===========================
   118 | 12840 | =========================
   119 | 11718 | =======================
   120 | 11169 | ======================
   121 | 10037 | ====================
   122 |  9273 | ==================
   123 |  8041 | ================
   124 |  7402 | ==============
   125 |  6761 | =============
   126 |  5827 | ===========
   127 |  5257 | ==========
   128 |  4736 | =========
   129 |  4153 | ========
   130 |  3494 | ======
   131 |  3103 | ======
   132 |  2731 | =====
   133 |  2379 | ====
   134 |  2064 | ====
   135 |  1696 | ===
   136 |  1481 | ==
   137 |  1246 | ==
   138 |  1024 | ==
   139 |   910 | =
   140 |   788 | =
person Kjetil S.    schedule 06.03.2020