介绍自定义PostgreSQL 聚集函数_数据库

对于借贷事务的银行账户，如何获取过去时间最大余额值？对于入住、离店的宾馆，如果获取过去时间最大客人数量？这两个场景都是求最大活动总量。通过求最大活动总量是学习PostgreSQL聚集函数最佳练习，本文带你学习窗口函数、自定义聚集函数。

1. 场景数据

为了简化，我们使用简单表结构仅包括amount和id两个列，id为自增列。

create table entries( 
  id serial primary key, 
  amount double precision not null 
);

我们使用random()和generate_series(1, 1000)插入测试数据。调用随机函数之前使用setseed(0)函数，确保每次产生相同数据。

select setseed(0); 
 
insert into entries(amount) 
select cast(2000 * random() as int) - 1000 
from generate_series(1, 1000);

2. 运行(活动)总数

为了求运行总量，需要对每一行计算运行总量。通过窗口函数很容易实现：

select 
  id, 
  amount, 
  sum(amount) over (order by id asc) as running_total 
from entries 
order by id asc;

返回结果：

id	amount	running_total
1	-658.343927934766	-658.343927934766
2	499.803960323334	-158.539967611432
3	-807.256689295173	-965.796656906605
4	740.930453874171	-224.866203032434
5	154.607012867928	-70.2591901645064
6	571.598516777158	501.339326612651
7	384.388306178153	885.727632790804
8	-262.46746070683	623.260172083974
9	747.808152809739	1371.06832489371
10	490.190196782351	1861.25852167606

表达式sum(amount) over (order by id asc) 可理解为按id升序从第一行到当前行的所有行数据总和。

获取最大运行总数

现在我们已经有了每一行的运行总数，现在通过聚集函数max求最大运行总数应该很简单：

select max(sum(amount) over (order by id asc)) 
from entries;

不幸的是报错了：

ERROR:  aggregate function calls cannot contain window function calls 
LINE 1: select max(sum(amount) over (order by id asc))

我们使用子查询试试：

select max(running_total) 
from ( 
  select sum(amount) over (order by id asc) as running_total 
  from entries 
) t;

返回结果：

max
16435.4466889054

不算太坏，但有两个方面可以优化：查询简化和速度，我们真正想要的是这样:

select greatest_running_total(amount order by id asc) 
from entries;

注意order by id asc在聚集函数里面。因为greatest_running_total函数需要输入参数有一定顺序，因此这各子句对正确结果起到关键作用。

3. 自定义聚集函数

greatest_running_total函数不存在，但 PostgreSQL提供了可以自定义聚集函数功能。我们的示例需要函数接收integer值并返回integer。

要创建聚集函数，首先需要有状态转换函数。该函数被每个输入行调用，传入内部聚集状态和当前行的值。内部聚集状态包含当前运行总数和最大运行总数，所以需要两个integer值的结构。好在 PostgreSQL提供了point类型(包括两个浮点数数值，使用point更方便)。

CREATE OR REPLACE FUNCTION public.grt_sfunc(IN agg_state point,IN el double precision) 
    RETURNS point 
    LANGUAGE 'plpgsql' 
    VOLATILE 
    PARALLEL UNSAFE 
    COST 100 
AS $BODY$ 
declare 
  greatest_sum float8; 
  current_sum float8; 
begin 
  if agg_state is null then 
    return point(el, el); 
  end if; 
 
  current_sum := agg_state[0] + el; 
  if agg_state[1] < current_sum then 
    greatest_sum := current_sum; 
  else 
    greatest_sum := agg_state[1]; 
  end if; 
 
  return point(current_sum, greatest_sum); 
end; 
$BODY$;

point类型agg_state变量有两个元素，索引从0开始。agg_state[0]当前行总和，agg_state[1]是已聚集的总和最大值。通过简单对agg_state[0]和当前行值el求和获得新当前总和，新的最大值是大于原最大值：agg_state[1]。最终返回新的point类型，包括新的当前行总和值和目前为止最大的总和。

我们定义的聚集函数内部状态是point类型，输出是float8类型，还需一个终止函数，接受聚集内部状态并转换为float8类型：

create function grt_finalfunc(agg_state point) 
returns float8 
immutable 
strict 
language plpgsql 
as $$ 
begin 
  return agg_state[1]; 
end; 
$$;

最后我们通过状态转换函数、内部聚集状态类型以及终止函数创建聚集：

drop aggregate if exists greatest_running_total(float8); 
create aggregate greatest_running_total (float8) 
( 
    sfunc = grt_sfunc, 
    stype = point, 
    finalfunc = grt_finalfunc 
);

现在可以试试我们的函数：

select greatest_running_total(amount order by id asc) 
from entries;

greatest_running_total
16435.4466889054

ok，结果一样，成功了。当然性能应该要差点。如果需要更好性能，需要使用C定义状态转换函数。

4. 总结

PostgreSQL为我们提供了许多解决问题的方法。在最大运行总数的例子中，带有子查询和窗口函数的初始解决方案是最好的。然而，有时如果没有自定义聚合，计算可能会非常困难。这时PL/pgSQL实现可能是理想的，但PL/pgSQL的性能可能会有所欠缺。

本文参考链接：https://blog.csdn.net/neweastsun/article/details/111461966