Oracle数据整理
时间：2021-07-01 10:21:17
帮助过：5人阅读
x1
as
(select userid,startdate,
coalesce(
min(
case when type
=-1 then add_months(startdate,
-1)
else startdate
end)
over(partition
by userid
order by vid rows
between 1 following
and unbouded following),startdate
+1)
as minsdate, enddate
as orgenddate,
case when enddate
is null and (lead(type)
over(partition
by userid
order by vid))
=-1 then add_months((lead(startdate)
over(partition
by userid
order by vid)),
-1)
else enddate
end as enddate,type,vid,
max(vid)
over(partition
by userid)
as max_id
from test,
x2 as
(select userid,startdate,minsdate,enddate,type,vid,max_id,
case when (lag(enddate)
over(partition
by userid
order by vid))
<add_months(startdate,
-1)
then 1
when (lag(type)
over(partition
by userid
order by vid))
=1 then null else 1 end as so
from x1),
x3 as
(select userid,vid,max_id,type,
sum(so)
over(partition
by userid
order by vid)
as so,
startdate,minsdate,
case when minsdate
<enddate
and minsdate
>=startdate
then minsdate
else enddate
end as enddate
from x2
where type
=1 and startdate
<=minsdate),
x4 as
(select userid,max_id,
max(vid)
as max_id2,
sum(type)
as type,
min(startdate) keep(dense_rank first
order by vid)
as startdate,
max(enddate) keep(dense_rank last
order by vid)
as enddate
from x3)
select userid,to_char(startdate,
‘yyyymm‘)
||‘--‘||coalesce(to_char(enddate,
‘yyyymm‘),
‘NULL‘)
as rangeSpace
from x4
where (max_id
=max_id2
or startdate
<=enddate)
and type
>-1;
Oracle数据整理
标签：
