基于SQL（Oracle）中预定义的日期范围的组记录

debugcn 发表于 Dev

Maeaex1

是否可以基于非连续日期start_date的行和end_date上一行的预定义日期范围差异（例如30天）对记录进行分组？我想参加各组的min(start_date)和max(end_date)。我在Oracle中尝试了leadandlag和partition by函数，但无法提出正确的解决方案。与我的问题相关的相关但未答复的帖子可以在这里找到。

例如

ROW_NUM        PROJECT_ID       START_DATE       END_DATE          
1              1                2016-01-14       2016-08-15
2              1                2016-08-16       2016-09-10     --- Date diff Row 1&2 = 1 Day
3              1                2016-11-15       2017-01-10     --- Date diff Row 2&3  = 66 Days
4              1                2016-01-17       2017-04-10     --- Date diff Row 3&4 = 7 Days
5              2                2018-04-28       2018-06-01     --- Other Project
6              2                2019-02-01       2019-04-05     --- Diff > 30 Days
7              2                2019-04-08       2019-07-28     --- Diff 3 Days

预期结果：

ROW_NUM        PROJECT_ID       START_DATE       END_DATE          
1              1                2016-01-14       2016-09-10
3              1                2016-11-15       2017-04-10     
5              2                2018-04-28       2018-06-01   
6              2                2019-02-01       2019-07-28

戈登·利诺夫

使用lag()和累计和定义组从何处开始。然后合计：

select project_id, min(start_date), max(end_date)
from (select t.*,
             sum(case when prev_end_date > start_date - interval '30' day then 0 else 1 end) over
                  (partition by project_id order by start_date) as grp
      from (select t.*,
                   lag(end_date) over (partition by project_id order by start_date) as prev_end_date
            from t
           ) t
     ) t
group by project_id, grp;

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。