Postgresql自定义聚合函数入门案例

有一个讲自定义聚合函数入门非常好的例子：
https://www.cybertec-postgresql.com/en/writing-your-own-aggregation-functions/

但是执行起来不太直观，这里精简下他的CASE，打印出中间变量，语法和过程一目了然。

测试场景

有一张表保存了路程数据，现在表中记录了司机的两单数据，第一单ID=1记录了三段路程，需要合并计算。

价格计算规则是：起步价3.5，每公里2.2（要求可在SQL内更改），最后每单再四舍五入。

CREATE TABLE t_taxi(trip_id int, km numeric);
insert into t_taxi values (1, 3.4);
insert into t_taxi values (1, 5.3);
insert into t_taxi values (1, 2.9);
insert into t_taxi values (2, 9.3);
insert into t_taxi values (2, 1.6);
insert into t_taxi values (2, 4.3);

 trip_id | km  
---------+-----
       1 | 3.4
       1 | 5.3
       1 | 2.9
       2 | 9.3
       2 | 1.6
       2 | 4.3

聚合函数

注意：聚合函数是每组独立计算的，比如按trip_id聚合，那么就会分两组，两组分别内部进行计算。

• taxi_accum：对于同组的每一行，都调用一次（注意这里的后两个参数就是后面执行时，给AGG函数传入的两个参数）
  • 参数一：上次一的结果；
  • 参数二：当前行数据
  • 参数三：执行时传进去的数据（看后面的select是怎么使用的）
• taxi_final：每组计算完了，最后调用一次
• taxi：聚合函数，INITCOND是第一次调用SFUNC给第一个参数的传值，可以不写。

CREATE OR REPLACE FUNCTION taxi_accum (numeric, numeric, numeric)
RETURNS numeric AS
$$
BEGIN
    RAISE NOTICE 'prev:[%] curr:(%) outer:(%) return:(%)', $1, $2, $3, $1 + $2 * $3;
    RETURN $1 + $2 * $3;
END;
$$
LANGUAGE 'plpgsql';


CREATE OR REPLACE FUNCTION taxi_final (numeric)
RETURNS numeric AS
$$
BEGIN
    RAISE NOTICE 'final:(%) return:(%)', $1, round($1 + 5, -1);
    RETURN round($1 + 5, -1);
END;
$$
LANGUAGE 'plpgsql';


CREATE AGGREGATE taxi(numeric, numeric)
(
        INITCOND = 3.50,
        STYPE = numeric,
        SFUNC = taxi_accum,
        FINALFUNC = taxi_final
);

执行过程

执行过程的中间变量全部打印出来了，执行过程一目了然。

• taxi(km, 2.20)的两个参数直接传给sfunc的后两个参数，sfunc的第一个参数记录上一次计算的结果。
• 每组分别计算，每组最后调用finalfunc。

postgres=# SELECT  trip_id, taxi(km, 2.20), 3.50 + sum(km)*2.2 AS manual FROM t_taxi GROUP BY 1;

NOTICE:  prev:[3.50] curr:(3.4) outer:(2.20) return:(10.980)
NOTICE:  prev:[10.980] curr:(5.3) outer:(2.20) return:(22.640)
NOTICE:  prev:[22.640] curr:(2.9) outer:(2.20) return:(29.020)
NOTICE:  prev:[3.50] curr:(9.3) outer:(2.20) return:(23.960)
NOTICE:  prev:[23.960] curr:(1.6) outer:(2.20) return:(27.480)
NOTICE:  prev:[27.480] curr:(4.3) outer:(2.20) return:(36.940)
NOTICE:  final:(36.940) return:(40)
NOTICE:  final:(29.020) return:(30)

 trip_id | taxi | manual 
---------+------+--------
       2 |   40 |  36.94
       1 |   30 |  29.02
(2 rows)

其他

sfunc函数支持使用任意语言，可以使用C语言写一些复杂的聚合函数实现一些兼容性需求。

聚合可以并发执行，请参考这一篇：http://mysql.taobao.org/monthly/2018/02/10/