日期交叉问题

题目需求

现有各品牌优惠周期表（promotion_info）如下，其记录了每个品牌的每个优惠活动的周期，其中同一品牌的不同优惠活动的周期可能会有交叉。

promotion_id	brand	start_date	end_date
1	oppo	2021-06-05	2021-06-09
2	oppo	2021-06-11	2021-06-21
3	vivo	2021-06-05	2021-06-15

现要求统计每个品牌的优惠总天数，若某个品牌在同一天有多个优惠活动，则只按一天计算。期望结果如下：

brand	promotion_day_count
vivo	17
oppo	16
redmi	22
huawei	22

 数据准备

drop table if exists promotion_info;
create table promotion_info
(
    promotion_id varchar(25) comment '优惠活动id',
    brand        varchar(25) comment '优惠品牌',
    start_date   varchar(25) comment '优惠活动开始日期',
    end_date     varchar(25) comment '优惠活动结束日期'
) comment '各品牌活动周期表'
;

insert into promotion_info
values (1, 'oppo', '2021-06-05', '2021-06-09'),
       (2, 'oppo', '2021-06-11', '2021-06-21'),
       (3, 'vivo', '2021-06-05', '2021-06-15'),
       (4, 'vivo', '2021-06-09', '2021-06-21'),
       (5, 'redmi', '2021-06-05', '2021-06-21'),
       (6, 'redmi', '2021-06-09', '2021-06-15'),
       (7, 'redmi', '2021-06-17', '2021-06-26'),
       (8, 'huawei', '2021-06-05', '2021-06-26'),
       (9, 'huawei', '2021-06-09', '2021-06-15'),
       (10, 'huawei', '2021-06-17', '2021-06-21')
;

思路分析 

分析题意，只要调整区间范围，使得区间不存在交叉，统计每个区间的持续天数，最后对天数求和即可。

第一步：获取截至当前的历史活动的最大结束日期

要找到所有存在交叉的区间，只要判断每个区间和历史区间是否存在交叉即可。而某个区间和历史区间存在交叉，等价于该区间历史活动end_date的最大值大于等于当前活动的start_date。因此，首先需要获取截至当前的历史活动的end_date的最大值。

使用开窗函数，按照brand分区、start_date排序，限定窗口范围为第一行至当前行的前一行，调用max函数计算结束日期end_date的最大值即可。

 第二步：处理交叉区间

对max_end_date列进行判断，max_end_date列存在以下三种情况。

1、max_end_date为空，说明是该品牌的第一次促销活动，start_date不变。

2、max_end_date小于start_date，说明本次活动区间与历史活动无交叉，start_date不变。

3、max_end_date大于start_date，存在区间交叉，将start_date赋值为max_end_date加1。

 

 第三步：最终结果

区间交叉有一种特殊情况，即某个区间被另外的区间包含，此时经过第二步的处理，该区间的start_date会大于end_date，这部分活动区间是可以舍弃的，因为该活动的所有日期都会被其它活动区间统计。因此，这里筛选start_date小于end_date的区间，使用datediff函数计算每个期间的持续天数，最后按照brand分组求和即可。

 完整解题代码

with t1 as (
    select
       brand,
       start_date,
       end_date,
       max(end_date) over (
               partition by brand order by start_date
               rows between unbounded preceding and 1 preceding
           ) max_end_date
    from promotion_info
), t2 as (
    select
        brand, end_date,
        if(
            # max_end_date 为空说明为起始天，start_date> max_end_date说明时间没有交叉
            max_end_date is null or start_date > max_end_date,
            # 时间不交叉的话，开始日期不变
            start_date,
            # 时间交叉，将开始日期修改为max_end_date的后一天，因为前面的天之前都统计了
            date_add(max_end_date, interval 1 day )
            ) start_date
    from t1
), t3 as (
    select
        brand, datediff(end_date, start_date) + 1 diff
    from t2
    where start_date < end_date
)
select
    brand, sum(diff) promotion_day_count
from t3
group by brand
;