在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
MAX() KEEP (DENSE_RANK LAST ORDER BY ) OVER() PARTITION BY()
How to Get the First or Last Value in a Group Using Group By in SQL
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。