Shulou(Shulou.com)06/01 Report--

In Oracle, aggregate function KEEP DENSE_RANK is used to obtain the aggregate function (such as MAX/MIN) value obtained by sorting a certain field under the condition of grouping a column.

For the existing test data, first in the case of account grouping, each grouping is sorted according to the id positive order (i.e. max(credit) of the largest id).

-- Oracledrop 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;-- PGdrop 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 can be implemented using 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 has no KEEP DENSE_RANK implementation, but can be implemented by comparing arrays.

[local]:5432 pg12@testdb=# SELECTpg12@testdb-# account,pg12@testdb-# (MAX(ARRAY[id, credit]) FILTER (WHERE type = 'credit_set'))[2] AS creditpg12@testdb-# FROMpg12@testdb-# t_eventpg12@testdb-# GROUP BYpg12@testdb-# accountpg12@testdb-# ORDER BY account; account | credit ---------+-------- 1 | 100 2 | 20(2 rows)Time: 1.206 ms

Note that (MAX(ARRAY[id, credit]) FILTER (WHERE type ='credit_set'))[2], the id and credit form Element as elements in the array, because id is the first element, so when comparing array elements, the id value will first be compared to get the array element with the largest id value, and then the value of the second member of the array element (meaning of [2]).

resources

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.