在pgsql中使用last、first聚合函數 · PHP知識總結

[TOC] > last、first聚合函數可以返聚合之后指定列的最后一個或第一條記錄的值。 > 在如MongoDB的非關系型數據庫中，是支持這兩種聚合函數的。但是mysql、pgsql等這些關系型數據庫中并沒有直接提供last、first聚合函數，如果需要在這些關系型數據庫中實現與last、first函數一樣的效果，可以通過窗口函數或者其他方式實現。在mysql、pgsql中直接執行last、first聚合函數時會顯示報錯 ![](https://img.kancloud.cn/6f/9c/6f9cfc59b44a39b82b013999cfce564c_768x387.png) 首先，創建表結構如下，并隨機生成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字段取最早的一條記錄的值。 # **第一種方式：通過pgsql的array_agg函數來實現** > 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" ``` # **第二種方式：通過pgsql 的窗口函數+join方式來實現** > 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語句，使用窗口函數的字段值都是一樣的 ![](https://img.kancloud.cn/2b/73/2b73cbcd0fcb7595101355da47ae5c47_899x629.png) # **第三種方式：創建內置聚合函數（無外部依賴）** > 此方式為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 ``` # **第四種方式：引入外部聚合擴展（有外部依賴）** [外部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) **結果分析：** 以上語句執行結果都是相同的，如下，可以對比建表時的數據。如rule_id為884、direction為626的數據，取最第一個reliability的值，則是id為9的數據對應的reliability字段值。 ![](https://img.kancloud.cn/19/c6/19c6b46b70f8e7a5407b080d959e44ed_791x426.png)