# Oracle数仓中判断时间连续性的几种SQL写法示例

```POLICYNO	STATE	START_DATE	END_DATE
sm1		1	20210201	20210202
sm1		2	20210203	20210203
sm1		3	20210204	20210204
sm1		1	20210205	20210209
sm1     1      20210216       20210219
.........................
```

```with t as--求出来每条数据当天的前一天镜像时间
(select a.policyno,
a.state,
a.snapdate,
lag(a.snapdate) over(partition by a.policyno, a.state order by a.snapdate) as lag_tim
from zyd.temp_0430 a
order by a.policyno, a.snapdate),
t1 as--判断当天镜像时间和前一天的镜像时间+1是否相等，如果相等就置为0否则置为1，新增临时字段lxzt意为：连续状态标志
(select t.*,
case
when t.snapdate = t.lag_tim + 1 then
0
else
1
end as lxzt
from t
order by policyno, snapdate),
t2 as--根据lxzt字段进行sum()over()求和，求出来一个新的用来做分组依据的字段，简称fzyj
(select t1.*, sum(lxzt) over(order by policyno, snapdate) as fzyj from t1)
select policyno,--最后根据policyno,state,fzyj进行分组求最大最小值即为状态连续的开始结束时间
state,
-- fzyj,
min(snapdate) as start_snap,
max(snapdate) as end_snap
from t2
group by policyno, state, fzyj
order by fzyj;
```

```with t as
(select a.policyno, a.state, a.snapdate, b.snapdate as snap2
from zyd.temp_0430 a, zyd.temp_0430 b
where a.policyno = b.policyno(+)
and a.state = b.state(+)
and a.snapdate - 1 = b.snapdate(+)
order by policyno, snapdate),
t1 as
(select t.*,
case
when snap2 is null then
1
else
0
end as lxzt
from t
order by policyno, snapdate),
t2 as
(select t1.*, sum(lxzt) over(order by policyno, snapdate) as fzyj
from t1
order by policyno, snapdate)
select policyno,
state,
fzyj,
min(snapdate) as start_snap,
max(snapdate) as end_snap
from t2
group by policyno, state, fzyj
order by fzyj;
```

```with t as
(select a.policyno,
a.state,
a.snapdate,
lag(a.snapdate) over(partition by a.policyno, a.state order by a.snapdate) as lag_tim
from zyd.temp_0430 a --where policyno="sm1"
order by a.policyno, a.snapdate),
t1 as
(select t.*,
case
when t.snapdate = t.lag_tim + 1 then
0
else
1
end as lxzt
from t
order by policyno, snapdate),
t2 as
(select t1.*,
lpad("->", (level - 1) * 2, "->") || snapdate as 树状结构,
level as 树中层次,
decode(level, 1, 1) 是否根节点,
decode(connect_by_isleaf, 1, 1) 是否叶子节点,
case
when (connect_by_isleaf = 0 and level > 1) then
1
end  是否树杈,
(prior snapdate) as 根值,
connect_by_root snapdate 主根值
from t1
connect by (prior snapdate = snapdate - 1
and prior state = state and
prior policyno = policyno)
order by policyno, snapdate)
select * from t2;
```

```with t as
(select a.policyno,
a.state,
a.snapdate,
lag(a.snapdate) over(partition by a.policyno, a.state order by a.snapdate) as lag_tim
from zyd.temp_0430 a --where policyno="sm1"
order by a.policyno, a.snapdate),
t1 as
(select t.*,
case
when t.snapdate = t.lag_tim + 1 then
0
else
1
end as lxzt
from t
order by policyno, snapdate),
t2 as
(select t1.*,
lpad("->", (level - 1) * 2, "->") || snapdate as 树状结构,
level as 树中层次,
decode(level, 1, 1) 是否根节点,
decode(connect_by_isleaf, 1, 1) 是否叶子节点,
case
when (connect_by_isleaf = 0 and level > 1) then
1
end  是否树杈,
(prior snapdate) as 根值,
connect_by_root snapdate 主根值
from t1
connect by (prior snapdate = snapdate - 1
and prior state = state and
prior policyno = policyno)
order by policyno, snapdate)
select policyno,
state,
min(snapdate) as start_date,
max(snapdate) as end_date
from t2
group by policyno, state, 主根值
order by policyno, state;
```

```with t as
(select a.policyno,
a.state,
a.snapdate,
lag(a.snapdate) over(partition by a.policyno, a.state order by a.snapdate) as lag_tim
from zyd.temp_0430 a --where policyno="sm1"
order by a.policyno, a.snapdate),
t1 as
(select t.*,
case
when t.snapdate = t.lag_tim + 1 then
0
else
1
end as lxzt
from t
order by policyno, snapdate),
t2 as
(select t1.*,
lpad("->", (level - 1) * 2, "->") || snapdate as 树状结构,
level as 树中层次,
decode(level, 1, 1) 是否根节点,
decode(connect_by_isleaf, 1, 1) 是否叶子节点,
case
when (connect_by_isleaf = 0 and level > 1) then
1
end 是否树杈,
(prior snapdate) as 根值,
connect_by_root snapdate 主根值
from t1
connect by (prior snapdate = snapdate - 1 and prior state = state and
prior policyno = policyno)
order by policyno, snapdate)
select policyno, state, 主根值 as start_date, snapdate as end_date
from t2
where 是否叶子节点 = 1
order by policyno, snapdate
```

```with t as
(select a.policyno,
a.state,
a.snapdate,
lag(a.snapdate) over(partition by a.policyno, a.state order by a.snapdate) as lag_tim
--case when lag(a.snapdate) over(partition by a.policyno, a.state order by a.snapdate) is null then snapdate else lag(a.snapdate) over(partition by a.policyno, a.state order by a.snapdate) end as lag_tim
from zyd.temp_0430 a
order by a.policyno, a.snapdate),
t1 as
(select t.*,
case
when t.snapdate = t.lag_tim + 1 then
0
else
1
end as lxzt
from t
order by policyno, snapdate),
t2 as
(select t1.*,
sys_connect_by_path(snapdate, ",") as pt,
level,
connect_by_isleaf as cb
from t1
connect by (prior snapdate = snapdate - 1 and prior state = state and
prior policyno = policyno))
select t2.*,
regexp_substr(pt, "[^,]+", 1, 1) as start_date,
regexp_substr(pt, "[^,]+", 1, regexp_count(pt, ",")) as end_date
from t2
where cb = 1
order by policyno, state;
```