3,490
社区成员
发帖
与我相关
我的任务
分享
with tab1 as (
select to_date('20181201', 'yyyymmdd') dt, 'a' id, 1 num from dual union all
select to_date('20181202', 'yyyymmdd') dt, 'a' id, 1 num from dual union all
select to_date('20181203', 'yyyymmdd') dt, 'a' id, 1 num from dual union all
select to_date('20181201', 'yyyymmdd') dt, 'b' id, 1 num from dual union all
select to_date('20181203', 'yyyymmdd') dt, 'b' id, 1 num from dual union all
select to_date('20181204', 'yyyymmdd') dt, 'b' id, 1 num from dual
)
, tab2 as (
select t1.*,
decode(t1.dt - 1, lag(t1.dt) over(partition by t1.id order by t1.dt), 0, 1) lg
from tab1 t1
)
, tab3 as (
select t1.*,
sum(t1.lg) over(partition by t1.id order by t1.dt) group_id
from tab2 t1
)
select t1.id,
t1.group_id,
min(t1.dt),
max(t1.dt),
sum(num)
from tab3 t1
group by t1.id, t1.group_id
order by t1.id, t1.group_id
;