在Oracle中聚合函数KEEP DENSE_RANK用于获取在某个列分组的情况下按某个字段排序得到的聚合函数(如MAX/MIN等)值. 现有测试数据,先在account分组的情况下,每个分组按id正序排序(即最大id)的max(credit). ``` -- Oracle drop table t_event; create table t_event(id int,account int,type varchar2(30),credit number,delta_balance number); truncate table t_event; insert into t_event(id,account,type,credit,delta_balance) values(1,1,'created',0,0); insert into t_event(id,account,type,credit,delta_balance) values(2,1,'deposited',null,100); insert into t_event(id,account,type,credit,delta_balance) values(3,1,'withdraw',null,-50); insert into t_event(id,account,type,credit,delta_balance) values(4,1,'credit_set',50,null); insert into t_event(id,account,type,credit,delta_balance) values(5,1,'withdraw',-30,null); insert into t_event(id,account,type,credit,delta_balance) values(6,1,'credit_set',100,null); insert into t_event(id,account,type,credit,delta_balance) values(7,1,'withdraw',null,-100); -- insert into t_event(id,account,type,credit,delta_balance) values(8,2,'credit_set',150,null); insert into t_event(id,account,type,credit,delta_balance) values(9,2,'credit_set',110,null); insert into t_event(id,account,type,credit,delta_balance) values(10,2,'credit_set',20,-100); commit; -- PG drop table if exists t_event; create table t_event(id int,account int,type varchar(30),credit int,delta_balance int); truncate table t_event; insert into t_event(id,account,type,credit,delta_balance) values(1,1,'created',0,0); insert into t_event(id,account,type,credit,delta_balance) values(2,1,'deposited',null,100); insert into t_event(id,account,type,credit,delta_balance) values(3,1,'withdraw',null,-50); insert into t_event(id,account,type,credit,delta_balance) values(4,1,'credit_set',50,null); insert into t_event(id,account,type,credit,delta_balance) values(5,1,'withdraw',-30,null); insert into t_event(id,account,type,credit,delta_balance) values(6,1,'credit_set',100,null); insert into t_event(id,account,type,credit,delta_balance) values(7,1,'withdraw',null,-100); -- insert into t_event(id,account,type,credit,delta_balance) values(8,2,'credit_set',150,null); insert into t_event(id,account,type,credit,delta_balance) values(9,2,'credit_set',110,null); insert into t_event(id,account,type,credit,delta_balance) values(10,2,'credit_set',20,-100); commit; ``` **Oracle** Oracle可使用KEEP DENSE_RANK实现 ``` TEST-orcl@DESKTOP-V430TU3>SELECT 2 account, 3 MAX(credit) 4 KEEP (DENSE_RANK LAST ORDER BY id) AS credit 5 FROM 6 t_event 7 WHERE type = 'credit_set' 8 GROUP BY 9 account; ACCOUNT CREDIT ---------- ---------- 1 100 2 20 ``` **PG** PG没有KEEP DENSE_RANK实现,但可通过数组的比较来实现. ``` [local]:5432 pg12@testdb=# SELECT pg12@testdb-# account, pg12@testdb-# (MAX(ARRAY[id, credit]) FILTER (WHERE type = 'credit_set'))[2] AS credit pg12@testdb-# FROM pg12@testdb-# t_event pg12@testdb-# GROUP BY pg12@testdb-# account pg12@testdb-# ORDER BY account; account | credit ---------+-------- 1 | 100 2 | 20 (2 rows) Time: 1.206 ms ``` 注意(MAX(ARRAY[id, credit]) FILTER (WHERE type = 'credit_set'))[2],把id和credit组成Element作为数组中的元素,由于id为第一个元素,因此在比较数组元素时,会首先比较id值得到最大id值的数组元素,然后取数组元素中的第2个成员的值([2]的含义). **参考资料** [FIRST](https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions056.htm) [MAX() KEEP (DENSE_RANK LAST ORDER BY ) OVER() PARTITION BY()](https://livesql.oracle.com/apex/livesql/file/content_FP499O87G9NJ3CA3JKIJA5XM8.html) [How to Get the First or Last Value in a Group Using Group By in SQL](https://hakibenita.com/sql-group-by-first-last-value)