PostgreSQL中的产品聚合

Question

我尝试在PostgreSQL中为产品(*)创建聚合。我的行的字段类型是“双精度”。

所以，我尝试了以下代码：

CREATE AGGREGATE nmul(numeric)
(
   sfunc = numeric_mul,
   stype = numeric
);

当我发起我的查询时，结果为：

ERROR:  function nmul(double precision) does not exist
LINE 4: CAST(nmul("cote") AS INT),

谢谢你

- Thomas K

---

你应该展示出引起错误的查询的SQL语句。 - Craig Ringer

Answer 1

我从一个非常聪明的人那里找到了解决方案，他意识到你可以使用对数来实现这一点（归功于他）：

select exp(sum(ln(x))) from generate_series(1,5) x;
 exp 
-----
 120
(1 row)

- clapas

---

数值方法用于计算对数和指数，每个计算需要进行一些相当简单的算术运算的几次迭代。 - clapas

@ChrisSH 当然不是，但当他想要获得累积乘积时，它同样可以用于负数。 例如，在上述调用中，如果 x < 0，他应该用 z = -x 替换 x，并将结果乘以 (-1)^x。 - clapas

Answer 2

将输入从 double precision (float8) 转换为 numeric，或者定义一个 double precision 版本的聚合函数。

您的聚合函数运行良好：

regress=> CREATE AGGREGATE nmul(numeric)
regress-> (
regress(>    sfunc = numeric_mul,
regress(>    stype = numeric
regress(> );

regress=> SELECT nmul(x) FROM generate_series(1,100) x;
                                                                              nmul                                                                              
----------------------------------------------------------------------------------------------------------------------------------------------------------------
 93326215443944152681699238856266700490715968264381621468592963895217599993229915608941463976156518286253697920827223758251185210916864000000000000000000000000
(1 row)

问题出在您的查询语句：

regress=> SELECT nmul(x::float8) FROM generate_series(1,100) x;                                                                                                                
ERROR:  function nmul(double precision) does not exist                                                                                                                         
LINE 1: SELECT nmul(x::float8) FROM generate_series(1,100) x;                                                                                                                  
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

你可以定义一个 float8 版本的聚合函数（float8 是 double precision 的同义词）：

regress=> CREATE AGGREGATE nmul(double precision)
(
   sfunc = float8mul,
   stype = float8
);

regress=> SELECT nmul(x::float8) FROM generate_series(1,100) x;
         fmul          
-----------------------
 9.33262154439441e+157
(1 row)

如果您想保留值的完整精度，请在汇总之前将其转换为 numeric，例如：

CAST(nmul(CAST("cote" AS numeric)) AS INT)

或者使用PostgreSQL特定的快捷转换方式：

nmul("cote"::numeric)::integer

请注意，当您使用这些产品聚合时，integer 很快会溢出。

regress=> SELECT nmul(x)::integer FROM generate_series(1,12) x;
   nmul    
-----------
 479001600
(1 row)

regress=> SELECT nmul(x)::integer FROM generate_series(1,13) x;
ERROR:  integer out of range
regress=> 

因此，您可能希望坚持使用numeric。

- Craig Ringer