时间戳

首页 > 解决方案 > oracle SQL 确定以月为单位的连续期间

sql - oracle SQL 确定以月为单位的连续期间

问题描述

早上好，

每 1 个月我都会为我的数据仓库收集数据。现在我想用 oracle sql 确定几个月的连续时间。

如果这个月有中断，我想开始一个新的时期。

我的示例类似于以下示例：

ID  LOAD_DATE  
100 20190101  
100 20190201  
100 20190401  
100 20190501  
100 20190601  
100 20190701  
100 20191001  
100 20191101  
100 20191201  
100 20200101  
200 20190701  
200 20190901  
200 20191101  
200 20191201  
200 20200101  
200 20200201

期望的结果：

ID  From     To  
100 20190101 20190201  
100 20190401 20190701  
100 20191001 20200101  
200 20190701 20190701  
200 20190901 20190901  
200 20191101 20200201

我可以管理基于一年的连续期间。由于年份的变化，我无法按月执行此操作。

请帮忙。我正在使用 oracle sql 开发人员

标签： sqloracleperiod

解决方案

对于实际支持的版本（即 Oracle 12+）：

select * 
from t
match_recognize(
  partition by id
  order by load_date
  measures
     first(LOAD_DATE) as ld,
     LAST(LOAD_DATE) AS lst,
     count(*) as cnt
  pattern (strt next*)
  define
     next as next.load_date=add_months(prev(load_date),1)
)
order by 1,2;

带有示例数据的完整示例：

alter session set nls_date_format='yyyymmdd';
with t (ID,LOAD_DATE) as (
   select 100, to_date('20190101') from dual union all  
   select 100, to_date('20190201') from dual union all  
   select 100, to_date('20190401') from dual union all  
   select 100, to_date('20190501') from dual union all  
   select 100, to_date('20190601') from dual union all  
   select 100, to_date('20190701') from dual union all  
   select 100, to_date('20191001') from dual union all  
   select 100, to_date('20191101') from dual union all  
   select 100, to_date('20191201') from dual union all  
   select 100, to_date('20200101') from dual union all  
   select 200, to_date('20190701') from dual union all  
   select 200, to_date('20190901') from dual union all  
   select 200, to_date('20191101') from dual union all  
   select 200, to_date('20191201') from dual union all  
   select 200, to_date('20200101') from dual union all  
   select 200, to_date('20200201') from dual
)
select * 
from t
match_recognize(
  partition by id
  order by load_date
  measures
     first(LOAD_DATE) as ld,
     LAST(LOAD_DATE) AS lst,
     count(*) as cnt
  pattern (strt next*)
  define
     next as next.load_date=add_months(prev(load_date),1)
)
order by 1,2;

推荐阅读