对于借贷事务的银行账户，如何获取过去时间最大余额值？对于入住、离店的宾馆，如果获取过去时间最大客人数量？这两个场景都是求最大活动总量。通过求最大活动总量是学习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的性能可能会有所欠缺。