在PostgreSQL中,经常遇到使用Group By进行分类统计的SQL。在前面可以使用一些聚合函数SUM、COUNT、MAX、MIN这些。在一些特殊场景一下,比如有些字段需要取Group By后结果里的第一个或者最后一个。这个时候Pg里没有直接支持的函数,可以通过自定义函数的方式来实现。
创建函数
-- 取第一个的函数
CREATE OR REPLACE FUNCTION first_agg ( anyelement, anyelement )
RETURNS anyelement AS $$
SELECT CASE WHEN $1 IS NULL THEN $2 ELSE $1 END;
$$ LANGUAGE SQL STABLE;
-- 创建聚合函数
CREATE AGGREGATE first (
sfunc = first_agg,
basetype = anyelement,
stype = anyelement
);
-- 取最后一个的函数
CREATE OR REPLACE FUNCTION last_agg ( anyelement, anyelement )
RETURNS anyelement AS $$
SELECT $2;
$$ LANGUAGE SQL STABLE;
-- 添加聚合函数
CREATE AGGREGATE last (
sfunc = last_agg,
basetype = anyelement,
stype = anyelement
);
测试示例
// filter关键字为聚合指定字段时添加过滤,可加可不加,根据需求而定
select rule_id,direction,min(priority) as priority,
first("reliability" ORDER BY "last_time" ASC) as reliability,
last("module_type" ORDER BY "last_time" ASC) as module_type,
last("attack_type") as attack_type,
last("sub_attack_type") as sub_attack_type,
first("last_time") as last_time
FROM alerts GROUP BY rule_id,direction