oracle 先排序 在分组

类似实现: 先排序

A    1
A    1
B    2
B    3
A    4

在排序的基础上实现分组相加

A 2
B 5
A 4

 

with tab1 as (
select 'a' id,1 val ,1 ord from dual UNION all
select 'a' id,1 val ,2 ord from dual UNION all
select 'a' id,1 val ,3 ord from dual UNION all
select 'b' id,1 val ,4 ord from dual UNION all
select 'b' id,1 val ,4 ord from dual UNION all
select 'b' id,1 val ,5 ord from dual UNION all
select 'a' id,1 val ,6 ord from dual UNION all
select 'a' id,1 val ,7 ord from dual 
),tab2 as (
select t1.*,decode(lag(t1.id) over (order by t1.ord) , t1.id ,0 ,1) lag from tab1 t1
),tab3 as (
select t1.*,sum(t1.lag) over( order by t1.ord) sm from tab2 t1
) 
-- select t1.*, decode(lag(t1.id) over (order by t1.ord) , t1.id ,0 ,1) lag from tab1 t1;
-- select t1.*,sum(t1.lag) over ( order by t1.ord) sm from tab2 t1;
select t1.id,sum(t1.val) from tab3 t1 group by t1.id,t1.sm order by t1.sm;

本文用到的oracle 函数   lag 获取上下行分析函数  sum over 连续求和分析函数
以及一个很骚的想法，可以放开注释 观察中间表 tab2 的数据