[TOC] > last、first聚合函数可以返聚合之后指定列的最后一个或第一条记录的值。 > 在如MongoDB的非关系型数据库中，是支持这两种聚合函数的。但是mysql、pgsql等这些关系型数据库中并没有直接提供last、first聚合函数，如果需要在这些关系型数据库中实现与last、first函数一样的效果，可以通过窗口函数或者其他方式实现。 在mysql、pgsql中直接执行last、first聚合函数时会显示报错  首先，创建表结构如下，并随机生成20条数据，其中last_time时间递增。 ``` postgres=# select id,rule_id,direction,priority,reliability,module_type,attack_type,sub_attack_type,last_time from alerts order by id postgres-# \g id | rule_id | direction | priority | reliability | module_type | attack_type | sub_attack_type | last_time ----+---------+-----------+----------+-------------+-------------+-------------+-----------------+------------ 1 | 319 | 209 | 8 | 3 | 86 | 18 | 92 | 1614048983 2 | 319 | 209 | 9 | 6 | 54 | 72 | 79 | 1614048984 3 | 49 | 709 | 3 | 3 | 21 | 3 | 68 | 1614048985 4 | 144 | 508 | 3 | 5 | 37 | 11 | 89 | 1614048986 5 | 585 | 488 | 8 | 3 | 44 | 49 | 63 | 1614048987 6 | 675 | 396 | 5 | 5 | 32 | 27 | 36 | 1614048988 7 | 419 | 209 | 9 | 7 | 29 | 33 | 92 | 1614048989 8 | 903 | 877 | 3 | 6 | 35 | 79 | 81 | 1614048990 9 | 884 | 626 | 5 | 9 | 81 | 35 | 9 | 1614048991 10 | 916 | 574 | 9 | 9 | 71 | 54 | 43 | 1614048992 11 | 884 | 626 | 1 | 2 | 54 | 66 | 80 | 1614048993 12 | 35 | 332 | 8 | 2 | 57 | 24 | 57 | 1614048994 13 | 884 | 626 | 8 | 3 | 37 | 29 | 26 | 1614048995 14 | 825 | 294 | 4 | 8 | 54 | 5 | 42 | 1614048996 15 | 67 | 430 | 2 | 5 | 36 | 5 | 91 | 1614048997 16 | 409 | 134 | 4 | 4 | 78 | 65 | 51 | 1614048998 17 | 962 | 692 | 3 | 1 | 25 | 93 | 4 | 1614048999 18 | 765 | 807 | 4 | 6 | 78 | 83 | 77 | 1614049000 19 | 619 | 512 | 4 | 4 | 45 | 38 | 80 | 1614049001 20 | 619 | 512 | 8 | 1 | 60 | 75 | 45 | 1614049002 (20 rows) ``` 需求：按照rule_id，direction字段聚合，priority字段取最小值，module_type、attack_type、sub_attack_type字段取聚合之后最后一条记录的值，reliability字段取最早的一条记录的值。 # <span style="font-size:15px">**第一种方式：通过pgsql的array_agg函数来实现** </span> > pgsql 的ARRAY\_AGG函数可以将多个值合并到一个数组中，相当于MongoDB的addToSet，这里不做赘述。 > 利用array_agg函数，根据指定字段进行倒序或者升序排序之后，再取第一个值便可以实现。 ``` // filter关键字为聚合指定字段时添加过滤，可加可不加，根据需求而定 SELECT "rule_id","direction", min("priority") as "priority", (array_agg("reliability" ORDER BY "last_time" ASC) FILTER (WHERE attack_ip = '1.1.1.1'))[1] as "reliability", (array_agg("module_type" ORDER BY "last_time" DESC) FILTER (WHERE attack_ip = '1.1.1.1'))[1] as "module_type", (array_agg("attack_type" ORDER BY "last_time" DESC) FILTER (WHERE attack_ip = '1.1.1.1'))[1] as "attack_type", (array_agg("sub_attack_type" ORDER BY "last_time" DESC) FILTER (WHERE attack_ip = '1.1.1.1'))[1] as "sub_attack_type", (array_agg("last_time" ORDER BY "last_time" DESC) FILTER (WHERE attack_ip = '1.1.1.1'))[1] as "last_time" FROM alerts where attack_ip = '1.1.1.1' GROUP BY "rule_id","direction" ``` # <span style="font-size:15px">**第二种方式：通过pgsql 的窗口函数+join方式来实现** </span> > pgsql提供了first_value、last_value的窗口函数，可以在查询时返回取分组内排序后，截止到当前行的第一个值或者最后一个值，详见 [pgsql窗口函数解析](http://docs.linchunyu.top/2280090) ``` // 先使用first_value、last_value窗口函数，获取指定列的第一个值或者最后一个值之后，再join关联聚合 // 因为外层是聚合，所以必须有聚合函数才可以获取到对应的字段。 // 此时字段已经取得第一个或者最后一个值，因为外层的select获取该字段时，采用min、max都是一样的。 SELECT b.rule_id,b.direction, min(priority) as priority,min(b.reliability) as reliability, min(b.module_type) as module_type, min(b.attack_type) as attack_type,min(b.sub_attack_type) as sub_attack_type, min(b.last_time) as last_time from alerts a INNER JOIN ( SELECT id,rule_id,direction, FIRST_VALUE(reliability)over(partition by rule_id,direction order by last_time asc) as reliability, FIRST_VALUE(module_type)over(partition by rule_id,direction order by last_time desc) as module_type, FIRST_VALUE(attack_type)over(partition by rule_id,direction order by last_time desc) as attack_type, FIRST_VALUE(sub_attack_type)over(partition by rule_id,direction order by last_time desc) as sub_attack_type, FIRST_VALUE(last_time)over(partition by rule_id,direction order by last_time desc) as last_time from alerts ) b on a.id = b.id GROUP BY b.rule_id,b.direction ``` 如图，仅执行join内的select语句，使用窗口函数的字段值都是一样的  # <span style="font-size:15px">**第三种方式：创建内置聚合函数（无外部依赖）** </span> > 此方式为SQL语言实现，没有外部依赖关系。 > 直接在数据库执行以下语句即可，创建完之后可以直接使用last、first函数聚合，[WIKI](https://wiki.postgresql.org/wiki/First/last_(aggregate)) ``` -- Create a function that always returns the first non-NULL item CREATE OR REPLACE FUNCTION public.first_agg ( anyelement, anyelement ) RETURNS anyelement AS $$ SELECT CASE WHEN $1 IS NULL THEN $2 ELSE $1 END; $$ LANGUAGE SQL STABLE; -- And then wrap an aggreagate around it CREATE AGGREGATE public.first ( sfunc = public.first_agg, basetype = anyelement, stype = anyelement ); -- Create a function that always returns the last non-NULL item CREATE OR REPLACE FUNCTION public.last_agg ( anyelement, anyelement ) RETURNS anyelement AS $$ SELECT $2; $$ LANGUAGE SQL STABLE; -- And then wrap an aggreagate around it CREATE AGGREGATE public.last ( sfunc = public.last_agg, basetype = anyelement, stype = anyelement ); ``` ``` // 此方式可以直接使用last、first聚合函数，对指定字段进行取第一条/最后一条的操作，同时支持根据指定字段进行排序 // 如reliability字段，实际上是聚合之后，根据last_time升序排序之后取第一条 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" ORDER BY "last_time" ASC) as attack_type, last("sub_attack_type" ORDER BY "last_time" ASC) as sub_attack_type, last("last_time" ORDER BY "last_time" ASC) as last_time FROM alerts GROUP BY rule_id,direction ``` # <span style="font-size:15px">**第四种方式：引入外部聚合扩展（有外部依赖）** </span> [外部last_first聚合扩展库下载地址](https://debian.pkgs.org/sid/debian-main-amd64/postgresql-13-first-last-agg_0.1.4-4-gd63ea3b-3+b1_amd64.deb.html) 详见 [pgsql安装扩展](http://docs.linchunyu.top/2280100) <br> **结果分析：** 以上语句执行结果都是相同的，如下，可以对比建表时的数据。 如rule_id为884、direction为626的数据，取最第一个reliability的值，则是id为9的数据对应的reliability字段值。