sql题解--打折日期交叉问题

题目-打折日期交叉问题

现有各品牌优惠周期表（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

思路：

redmi 2021-06-05 2021-06-21
redmi 2021-06-09 2021-06-15
redmi 2021-06-17 2021-06-26

1） 将当前行以前的数据中最大的end_date放置到当前行
redmi 2021-06-05 2021-06-21 null
redmi 2021-06-09 2021-06-15 2021-06-21
redmi 2021-06-17 2021-06-26 2021-06-21

2） 比较start_date 与 移动下来的数据， 如果开始时间大， 则不需要操作， 反之则将移动下来的数据加一替换当前行的开始时间
redmi 2021-06-05 2021-06-21
redmi 2021-06-22 2021-06-15
redmi 2021-06-22 2021-06-26

3） 将每行的结束时间减去开始时间
redmi 16
redmi -7
redmi 4

4） 按照品牌分组，计算每条数据加一后的总和
redmi 22

SELECT
	brand,
    sum(if(subdate>0,subdate+1,0)) as promotion_day_count
FROM
(
    SELECT
    brand,
    datediff(end_date,new_start_date) as subdate
    FROM
    (
        SELECT
            brand,
            start_date,
            end_date,
            if(maxedt is NULL,start_date,if(maxedt<start_date,start_date,date_add(maxedt,1))) as new_start_date
        FROM
        (
        SELECT
            brand,
            start_date,
            end_date,
            max(end_date) over(partition by brand order by start_date rows between UNBOUNDED PRECEDING and 1 PRECEDING) maxedt
            FROM promotion_info 
        )t1
    )t2
)t3
GROUP by brand