此处以时间为例,t1表将作为此次的示例表
WITH t1 as(
SELECT '2024-01-18'::date ts
UNION ALL
SELECT '2024-01-19'::date ts
UNION ALL
SELECT '2024-01-22'::date ts
UNION ALL
SELECT '2024-01-28'::date ts
UNION ALL
SELECT '2024-01-29'::date ts
UNION ALL
SELECT '2024-01-30'::date ts
)
利用***lag()……over()……***对t1进行数据处理,得到t2,代码及效果如下所示:
,
t2 as (
select t1.*,(CASE lag(t1.ts) over(order by t1.ts) WHEN (t1.ts-1) THEN 0 ELSE 1 end) mlag
from t1
)
再利用 ***sum()……over()……***对t2得到的数据进行处理,得到t3,代码及效果如下所示:
,
t3 as (
select t2.*,sum(t2.mlag) over(order by t2.ts) gpindex from t2
)
最后一步,根据gpindex进行分组,就可得到如下效果:
select min(t3.ts)sdate,max(t3.ts) edate from t3 GROUP BY gpindex ORDER BY sdate
分享到此结束