Oracle分组查询中新增基于日期的最新records列（含聚合）

现有一条Oracle分组查询语句，可返回各process分组的records列平均值（avg）与最大值（max）。需求为在保留原有列的基础上，新增LATEST列，展示各分组中基于dates列最新日期对应的records值。

测试用例SQL #

with x as (select 'A' process, 10 records, sysdate-5 dates from dual union all
       select 'A' process, 20 records, sysdate-4 dates from dual union all
       select 'A' process, 30 records, sysdate-3 dates from dual union all
       select 'B' process, 25 records, sysdate-2 dates from dual union all
       select 'B' process, 15 records, sysdate-1 dates from dual)
select process, 
   avg(records) avgu, 
   max(records) maxu
  from x
 group by process
 order by 1

原查询返回结果 #

预期输出（新增LATEST列） #

解决方案 #

方法一：使用Oracle特有的KEEP聚合子句（推荐） #

利用Oracle原生的KEEP (DENSE_RANK LAST ORDER BY ...)语法，直接在聚合查询中获取分组内最新日期对应的records值，无需额外子查询，效率更高：

with x as (select 'A' process, 10 records, sysdate-5 dates from dual union all
       select 'A' process, 20 records, sysdate-4 dates from dual union all
       select 'A' process, 30 records, sysdate-3 dates from dual union all
       select 'B' process, 25 records, sysdate-2 dates from dual union all
       select 'B' process, 15 records, sysdate-1 dates from dual)
select process,
       avg(records) avgu,
       max(records) maxu,
       -- 获取分组内最新日期对应的records值
       max(records) keep (dense_rank last order by dates) latest
from x
group by process
order by 1;

说明：

• dense_rank last order by dates会在每个process分组内，按dates降序排列后取最新的一组记录（若存在多个相同最新日期的记录，会保留所有符合条件的行）
• max(records)则从这些最新记录中取出records的最大值；如果每组最新日期唯一，用min(records)结果一致

方法二：使用窗口函数标记最新记录 #

先通过row_number()窗口函数为每组内的记录按日期降序排名，再通过聚合筛选出排名第一的记录对应的records值：

with x as (select 'A' process, 10 records, sysdate-5 dates from dual union all
       select 'A' process, 20 records, sysdate-4 dates from dual union all
       select 'A' process, 30 records, sysdate-3 dates from dual union all
       select 'B' process, 25 records, sysdate-2 dates from dual union all
       select 'B' process, 15 records, sysdate-1 dates from dual),
x_ranked as (
    select *,
           row_number() over (partition by process order by dates desc) rn
    from x
)
select process,
       avg(records) avgu,
       max(records) maxu,
       max(case when rn = 1 then records end) latest
from x_ranked
group by process
order by 1;

说明：

• row_number()会为每个process分组内的记录按dates降序分配唯一排名，最新日期的记录排名为1
• 通过case when rn=1 then records end筛选出最新记录的records值，再用max聚合得到结果

内容的提问来源于stack exchange，提问作者Lucian Lazar