Skip to main content
 首页 » 数据库

介绍自定义PostgreSQL 聚集函数

2022年07月19日144java哥

对于借贷事务的银行账户,如何获取过去时间最大余额值?对于入住、离店的宾馆,如果获取过去时间最大客人数量?这两个场景都是求最大活动总量。通过求最大活动总量是学习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
阅读延展