Google BigQuery中的因子(Factorial in Google BigQuery)

Google BigQuery中的因子(Factorial in Google BigQuery)

我需要计算谷歌BigQuery中的变量的阶乘 - 是否有一个函数呢？ 在这里的文档中我找不到一个：

https://cloud.google.com/bigquery/query-reference#arithmeticoperators

我现在提出的解决方案是计算数字1到100的阶乘，并将其作为表格上传并与该表格结合。 如果你有更好的东西，请告知。

由于上下文可以揭示最佳解，因此在计算随机变量的泊松概率（时间窗口中的事件数）的上下文中使用因子。 请参阅此处的第一个等式： https ： //en.wikipedia.org/wiki/Poisson_distribution

I need to compute the factorial of a variable in Google BigQuery - is there a function for this? I cannot find one in the documentation here:

https://cloud.google.com/bigquery/query-reference#arithmeticoperators

My proposed solution at this point is to compute the factorial for numbers 1 through 100 and upload that as a table and join with that table. If you have something better, please advise.

As context may reveal a best solution, the factorial is used in the context of computing a Poisson probability of a random variable (number of events in a window of time). See the first equation here: https://en.wikipedia.org/wiki/Poisson_distribution

最满意答案

试试以下。 快速而肮脏的例子

select number, factorial FROM js( // input table (select number from (select 4 as number), (select 6 as number), (select 12 as number) ), // input columns number, // output schema "[{name: 'number', type: 'integer'}, {name: 'factorial', type: 'integer'}]", // function "function(r, emit){ function fact(num) { if(num<0) return 0; var fact=1; for(var i=num;i>1;i--) fact*=i; return fact; } var factorial = fact(r.number) emit({number: r.number, factorial: factorial}); }" )

Extending Mikhail's answer to be general and correct for computing the factorial for all number 1 to n, where n < 500, the following solution holds and can be computed efficiently:

select number, factorial FROM js( // input table ( SELECT ROW_NUMBER() OVER() AS number, some_thing_from_the_table FROM [any table with at least LIMIT many entries] LIMIT 100 #Change this to any number to compute factorials from 1 to this number ), // input columns number, // output schema "[{name: 'number', type: 'integer'}, {name: 'factorial', type: 'float'}]", // function "function(r, emit){ function fact(num) { if(num<0) return 0; var fact=1; for(var i=num;i>1;i--) fact*=i; return fact; } #Use toExponential and parseFloat to handle large integers in both Javascript and BigQuery emit({number: r.number, factorial: parseFloat(fact(r.number).toExponential())}); }" )