44. 日期交叉问题

题目需求

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

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

期望结果如下（截取部分）：

brand (品牌)	promotion_day_count (优惠天数)
vivo	17
oppo	16
redmi	22
huawei	22

需要用到的表：

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

思路一

实现一

-- 实现一: from 网友
-- 思路: ( 最大活动结束时间 - 最小活动开始时间 + 1 ) 求出总长，然后减去中间没有活动的时间

SELECT brand,
       datediff(max(end_date), min(start_date)) + 1 -
       sum(if(blank_brand_date > 0, blank_brand_date, 0)) promotion_day_count
from (
         SELECT brand,
                start_date,
                end_date,
                datediff(
                        start_date,
                        -- 本次活动开始之前该品牌所有活动中最大的活动结束日期
                        max(end_date)
                            over (partition by brand order by start_date rows BETWEEN UNBOUNDED PRECEDING and 1 PRECEDING)
                    ) - 1 blank_brand_date -- 没有活动的天数
         from promotion_info
     ) t
group by brand;

原理二

实现二

-- 思路: 已知开始日期和结束日期，动态生成此范围内的日期序列。

-- 2） 按照 brand 分组, 统计每个品牌的优惠总天数
select brand,
       size(collect_set(`current_date`)) as promotion_day_count
from (
         -- 1） 已知开始日期和结束日期，动态生成此范围内的日期序列
         select brand,
                date_add(start_date, pos) as `current_date`
         from promotion_info
                  lateral view posexplode(split(space(datediff(end_date, start_date)), ' ')) tmpTable as pos, val
     ) t1
group by brand;

学习链接

【Hive】生成动态日期序列

题目来源

http://practice.atguigu.cn/#/question/44/desc?qType=SQL