尝试在PostgreSQL中创建聚合函数

Question

我正在尝试创建新的聚合函数，在PostgreSQL中使用代替 sum() 函数。

我开始查看这里的手册。

由于我想创建一个函数，该函数接受一组双精度值，对它们进行求和，然后进行一些额外的计算，因此我首先创建了最终函数：

takes double precision as input and gives double precision as output    
DECLARE
      v double precision;
BEGIN
      IF tax > 256 THEN
            v := 256;
      ELSE
            v := tax;
      END IF;
      RETURN v*0.21/0.79;
END;

然后我想创建一个聚合函数，它接受一个双精度值数组，并输出一个双精度值，供我的前一个函数处理。

CREATE AGGREGATE aggregate_ee_income_tax (float8[]) (
  sfunc = array_agg
 ,stype = float8
 ,initcond = '{}'
 ,finalfunc = eeincometax);

当我运行该命令时，我得到的结果是：

错误：函数array_agg(double precision, double precision[])不存在

我有些困惑了，因为手册列出了array_agg()作为已存在的函数。我做错了什么吗？

另外，当我运行以下命令时：

\da
                     List of aggregate functions
 Schema | Name | Result data type | Argument data types | Description 
--------+------+------------------+---------------------+-------------
(0 rows)

我的安装中没有聚合函数吗？还是只列出用户定义的函数？

基本上我想要理解的是：

1）我能否使用现有的函数对我的数组值求和？

2）如何了解函数的输入和输出数据类型？文档称 array_agg() 函数可以接受任何类型的输入。

3）我的自定义聚合函数有什么问题？

编辑 1

为了提供更多信息和更清晰的图片，我的目标是：

我有一个跨越多个表的巨大查询，大致如下：

SELECT sum(tax) ... from (SUBQUERY) as foo group by id

我想使用自己的聚合函数替换那个sum函数，这样我就不必在后端做额外的计算了——因为它们都可以在数据库层面完成。

编辑2

接受Ants的答案。由于最终解决方案来自评论，因此在此进行引用：

CREATE AGGREGATE aggregate_ee_income_tax (float8)
(
 sfunc = float8pl
,stype = float8
,initcond = '0.0'
,finalfunc = eeincometax
);

- Odif Yltsaeb

---

我从您的描述中不明白为什么您不能只使用yourfinalfunc(sum(tax))来转换最终结果呢？这样可以完全避免创建聚合函数。 - araqnid

因为我所提到的查询是以编程方式组合的，如果我要在另一个函数之上添加一个函数，那么我将不得不在组合查询的过程中进行大量更改。添加一堆条件等等 - 所有这些都会使以后理解代码变得更加困难。因此，更改总和函数会更容易些。 - Odif Yltsaeb

Answer 1

Array agg是一个聚合函数，而不是常规函数，因此不能用作新聚合的状态转换函数。您想要做的是创建一个聚合函数，其状态转换函数与array_agg相同，并具有自定义的最终函数。

不幸的是，array_agg的状态转换函数是基于内部数据类型定义的，因此无法重复使用。幸运的是，在核心中已经存在一个函数可以满足您的需求。

CREATE AGGREGATE aggregate_ee_income_tax (float8)(
    sfunc = array_append,
    stype = float8[],
    initcond = '{}',
    finalfunc = eeincometax);

请注意您混淆了类型，您可能希望将一组浮点数聚合到一个数组中，而不是将一组数组聚合到一个浮点数中。

- Ants Aasma

---

太棒了，这让我更接近我想要的结果了，但是eeincometax finalfunc期望单个float8值而不是数组。而array_append似乎只输出数组。所以我仍然需要在我的finalfunction中进行实际求和，或者有没有核心函数可以输出单个值？ - Odif Yltsaeb

所以你想要作为状态转换的函数需要接受两个浮点数并返回它们的和。巧合的是，这正是 + 运算符所做的。实现 float8 的 + 运算符的函数称为 float8pl。将 sfunc 更改为 float8pl，将 stype 更改为 float8，你就可以了。 - Ants Aasma

不，我想要一个聚合函数，它可以取一堆记录并将它们求和，然后将该总和作为输入传递给我的finalfunc。就像我说的那样 - 我想要一个函数来替换我的查询中的sum()函数。 - Odif Yltsaeb

是的，这就是它的作用。float8pl 作为过渡函数将值相加，最终的值传递给您的函数。另外，我忘了提到您需要将 initcond 更改为 0.0。 - Ants Aasma

太棒了。我成功地完成了所有的操作而没有出现任何错误。但是输出的值似乎是错误的。现在我会开始进行调试 :)。 - Odif Yltsaeb

Answer 2

除了@Ants的优秀建议外：

1.) 您的最终函数可以简化为：

CREATE FUNCTION eeincometax(float8)
  RETURNS float8 LANGUAGE SQL AS
$func$
SELECT (least($1, 256) * 21) / 79
$func$;

2.) 看起来你正在处理金钱？在这种情况下，我强烈建议使用类型numeric(首选)或money。浮点运算通常不够精确。

3.) 聚合的初始条件可以简单地设置为0：

CREATE AGGREGATE aggregate_ee_income_tax(float8)
(
  sfunc     = float8pl
 ,stype     = float8
 ,initcond  = 0
 ,finalfunc = eeincometax
);

4.) 在你的情况下，(least(sum(tax), 256) * 21) / 79 可能比你自定义的聚合函数更快。PostgreSQL 提供的聚合函数是用 C 写的并且经过了性能优化。我建议使用这个。

- Erwin Brandstetter

---

感谢提供额外信息。只是有一个问题 - 由于我的自定义聚合函数依赖于 postgreSQL 自己的 float8pl 函数，而根据您的帖子所理解的，该函数也是用 C 编写并进行了优化 - 我的函数实际上增加了多少真正的开销？ - Odif Yltsaeb

@Zayatzz：最好直接测试一下。使用 EXPLAIN ANALYZE 运行两个变量 - 可以多次运行以排除缓存效应。我对比较感兴趣... - Erwin Brandstetter