时间戳

首页 > 解决方案 > 在 SQL 中合并/组合重叠的时间范围

sql - 在 SQL 中合并/组合重叠的时间范围

问题描述

我有一个表格，其中包含每个客户及其位置的几个合同信息。

每个客户可以同时拥有 1-x 个位置。每个位置可以有 x 个合同，但不能重叠。

现在我需要将这些信息聚集到整体时间范围

以图像为例：

上传到 imgur

如表：

源表：

客户ID 位置标识 合同签署 合同开始 合同结束
123 001 '2012-04-02' '2012-09-01' '2014-04-15'
123 001 '2014-12-01' '2015-01-01' '2018-03-31'
123 001 '2019-07-01' '2020-01-01' '2021-09-30'
123 002 '2012-07-01' '2012-09-01' '2014-04-15'
123 002 '2014-12-01' '2015-07-01' '2018-03-31'
123 002 '2018-07-01' '2018-10-01' '2021-09-30'
123 003 '2014-09-01' '2015-07-01' '2018-03-31'

和我想要的结果：

客户ID 合同签署 合同开始 合同结束 days_without_contract
123 '2012-04-02' '2012-09-01' '2014-04-15' 无效的
123 '2014-09-01' '2015-01-01' '2018-03-31' 139
123 '2018-07-01' '2018-07-01' '2021-09-30' 92

标签： sqlsql-servertsql

解决方案

这是一种差距和孤岛问题。您可以使用累积max()来确定是否没有重叠。如果没有，那么这就是一个“孤岛”的开始，聚合完成了剩下的工作：

select customer_id, min(contract_signed),
       min(contract_start), max(contract_end),
       datediff(day, max(contract_end), lag(contract_start) over (order by customer_id)) as days_without_contract
from (select t.*,
             sum(case when prev_contract_end >= contract_start then 0 else 1 end) over (partition by customer_id order by contract_start) as grp
      from (select t.*,
                   max(contract_end) over (partition by customer_id 
                                           order by contract_start, location
                                           rows between unbounded preceding and 1 preceding
                                          ) as prev_contract_end
            from t
           ) t
      ) t
group by customer_id, grp;

推荐阅读