postgresql 自己写的几个聚合函数整理

SELECT DISTINCT(proname) FROM pg_proc WHERE proisagg order by proname 查所有

SELECT * FROM pg_proc WHERE proname like 'agg%' AND proisagg; 查所有agg开头的


CREATE AGGREGATE agg_o3_8h(
BASETYPE = numeric,
SFUNC = sfun,
STYPE = numeric[],
FINALFUNC = sffun_o3_8h
);

CREATE AGGREGATE agg_percent90(
BASETYPE = numeric,
SFUNC = sfun,
STYPE = numeric[],
FINALFUNC = sffun_percent90
);

CREATE AGGREGATE agg_percent95(
BASETYPE = numeric,
SFUNC = sfun,
STYPE = numeric[],
FINALFUNC = sffun_percent95
);

字符串拼接聚合函数:
CREATE AGGREGATE agg_string_contact(anyelement)
(
sfunc = array_append, -- 每行的操作函数,将本行append到数组里
stype = anyarray, -- 聚集后返回数组类型
initcond = '{}' -- 初始化空数组

);

CREATE AGGREGATE group_concat(anyelement)
(
sfunc = array_append, -- 每行的操作函数,将本行append到数组里
stype = anyarray, -- 聚集后返回数组类型
initcond = '{}' -- 初始化空数组

);

posted @ 2019-07-10 18:14  JackGIS  阅读(2713)  评论(0编辑  收藏  举报