有一个讲自定义聚合函数入门非常好的例子:
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/