oracle dense,Oracle vs PostgreSQL Develop（14） - 分析函数KEEP DENSE_RANK

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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/6906/viewspace-2654235/，如需转载，请注明出处，否则将追究法律责任。