Генерация нормально распределенных серий с использованием BIgQuery

Question

Есть ли способ генерировать нормально распределенные серии в BQ? идеально указав среднее значение и sd распределения. Я нашел способ с использованием полярного метода Марсальи, но он не идеален, так как я не хочу, чтобы полярные координаты распределения, а генерировать массив, который соответствует параметрам, указанным для его нормального распределения. Заранее спасибо.

Answer 1

Этот запрос дает вам евклидовы координаты нормального распределения с центром в 0. Вы можете отрегулировать как среднее (средняя переменная) или sd (переменная дисперсия), так и значения оси x (GENERATE_ARRAY(beginning,end,step)):

CREATE TEMPORARY FUNCTION normal(x FLOAT64)
RETURNS FLOAT64
LANGUAGE js AS """
  var mean=0;
  var variance=1;
  var x0=1/(Math.sqrt(2*Math.PI*variance));
  var x1=-Math.pow(x-mean,2)/(2*Math.pow(variance,2));
  return x0*Math.pow(Math.E,x1);
""";
WITH numbers AS
  (SELECT x FROM UNNEST(GENERATE_ARRAY(-10, 10,0.5)) AS x)
SELECT x, normal(x) as normal
FROM numbers;

Для этого я использовал "Пользовательские функции" [1]. Они используются, когда вы хотите иметь другое выражение SQL или когда вы хотите использовать Java Script (как я сделал).

ПРИМЕЧАНИЕ: я использовал функцию плотности вероятности нормального распределения, если вы хотите использовать другую, вам нужно изменить переменные x0,x1 и return (я написал их отдельно, чтобы было понятнее).

Answer 2

Предыдущие ответы дают функцию распределения вероятностей нормальной с.в. Здесь я изменяю предыдущие ответы, чтобы дать случайное число, сгенерированное с желаемым распределением, в стандартном SQL BQ с использованием метода «полярных координат». Вопрос просит не использовать полярные координаты, что является нечетным запросом, поскольку полярные координаты не используются при генерации нормально распределенного случайного числа.

      CREATE TEMPORARY FUNCTION rnorm ( mu FLOAT64, sigma FLOAT64 ) AS 
(
    (select mu + sigma*(sqrt( 2*abs(
                                    log( RAND())
                                    )
                            )
                       )*cos( 2*ACOS(-1)*RAND())
    )
)
;

select 
num ,
rnorm(-1, 5.3) as RAND_NORM
FROM UNNEST(GENERATE_ARRAY(1, 17) ) AS num

Answer 3

Самый простой способ сделать это в BQ - создать собственную функцию:

      CREATE OR REPLACE FUNCTION 
`your_project.functions.normal_distribution_pdf`
(x ANY TYPE, mu ANY TYPE, sigma ANY TYPE) AS (
(
SELECT 
safe_divide(1,sigma * power(2 * ACOS(-1),0.5)) * exp(-0.5 * power(safe_divide(x-mu,sigma),2))
)
);

Далее вам нужно только применить функцию:

      with inputs as (
SELECT 1 as x, 0 as mu, 1 as sigma
union all 
SELECT 1.5 as x, 1 as mu, 2 as sigma
 union all 
SELECT 2 as x , 2 as mu, 3 as sigma     

)


SELECT x,
       `your_project.functions.normal_distribution_pdf`(x, mu, sigma) as normal_pdf
from
inputs