时间戳

首页 > 解决方案 > 我们如何在 SQL 中将连续日期分组为单个日期跨度？

sql - 我们如何在 SQL 中将连续日期分组为单个日期跨度？

问题描述

这是我的数据：

ID 客户代码 开始日期 结束日期
1 122 20200812 20200814
2 122 20200816 20200817
3 122 20200817 20200819
4 122 20200821 20200822
5 122 20200823 20200824

我尝试了以下代码：

select Customercode, min(startdate) as startdate, max(enddate) as enddate       
from (      
      select Customercode,  startdate, enddate      
             sum(rst) over (order by Customercode, DOS) as grp      
      from (        
             select Customercode, startdate, enddate        
                    case when coalesce(lag(enddate) over (partition by Customercode order by Customercode, startdate), startdate) + 1 <> startdate then 1 end rst       
             from   tbl     
           ) t1     
     ) t2       
group by grp, Customercode      
order by startdate

我的结果

ID 客户代码 开始日期 结束日期
1 122 20200812 20200814
2 122 20200816 20200817
3 122 20200817 20200819
4 122 20200821 20200824

所需的输出应该是这样的。请分享你的想法。

ID 客户代码 开始日期 结束日期
1 122 20200812 20200814
2 122 20200816 20200819
3 122 20200821 20200824

标签： sqldatetimegaps-and-islandsdate-arithmetic

解决方案

目前尚不清楚您是否要将开始日期与前一个结束日期相同的记录分组，还是在一天之后。

如果您想在同一日期分组，您可以将查询表述为：

select customercode, min(startdate), max(enddate)
from (
    select t.*,
        sum(case when startdate = lag_enddate then 0 else 1 end)    
            over(partition by customercode order by startdate) as grp
    from (
        select t.*,
            lag(enddate) over(partition by customercode order by startdate) as lag_enddate
        from tbl t
    ) t
) t
group by customercode, grp
order by min(startdate)

您还可以通过修改条件窗口同时允许这两种情况sum()。这需要一点日期算法，其语法因数据库而异。在标准 SQL 中：

        sum(case when startdate <= lag_enddate + interval '1' day then 0 else 1 end)    
            over(partition by customercode order by startdate) as grp

推荐阅读