在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)

Oracle vs PostgreSQL Develop（14） - 分析函式KEEP DENSE_RANK

相關文章