• 大数据面试SQL（一）：合并日期重叠的活动

    文章目录

    合并日期重叠的活动

    一、题目

    二、分析

    三、SQL实战

    四、样例数据参考

    合并日期重叠的活动

    一、题目

    已知有表记录了每个品牌的活动开始日期和结束日期，每个品牌可以有多个活动。请编写一个SQL查询合并在同一个品牌举行的所有重叠的活动，如果两个活动至少有一天相同，那他们就是重叠的。

    样例数据：

    ​

    目标结果：

    ​

    解释：

    有两个品牌，分别为小米和华为。

    小米:

    • 两个活动["2024-01-13"，"2023-01-20"]和[“2024-01-14"，"2024-01-17"]重叠，我们将它们合并到一个活动中[“2024-01-13"，"2024-01-20"]。

    华为：

    • ["2024-11-09"，"2024-12-07"]不与任何其他活动重叠，所以我们保持原样。

    二、分析

    我们首先按照brand分组，根据start_date、end_date 升序排列，按照start_date 进行了升序排列，所以当前行的start_date一定晚于前一行的start_date，对当前行的start_date 和截止到上一行的最大end_date进行比较，如果当前行的start_date 小于等于截止到前一行最大end_date 代表有交叉，可以合并，否则代表不可合并。判断出是否可以合并之后，具体操作合并就转化成类似连续问题。

    维度评分
    题目难度⭐️⭐️⭐️⭐️⭐️
    题目清晰度⭐️⭐️⭐️⭐️⭐️
    业务常见度⭐️⭐️⭐️⭐️

    三、SQL实战

    1、使用max()函数开窗，获得截止到当前行之前的活动最后日期

    这里我们使用max()，按照brand进行分组，然后根据start_date和end_date进行排序，利用聚合函数开窗，开窗函数内有排序则聚合到当前行的特性，进行处理。其中我们这边需要聚合到当前行的上一行。 

    执行SQL

    1. select
    2.     brand,
    3.     start_date,
    4.     end_date,
    5.     max(end_date) over (partition by brand order by start_date asc,end_date asc rows between unbounded preceding and 1 preceding) as max_end_date
    6. from t1_brand_event;

    2、根据当前行的start_day与max_end_date进行比较，得出是否可以合并标记

    执行SQL 

    1. select brand,
    2.        start_date,
    3.        end_date,
    4.        max_end_date,
    5.        if(start_date <= max_end_date, 0, 1) as is_merge --0:合并，1:不合并
    6. from (select brand,
    7.              start_date,
    8.              end_date,
    9.              max(end_date)
    10.                  over (partition by brand order by start_date asc,end_date asc rows between unbounded preceding and 1 preceding) as max_end_date
    11.       from t1_brand_event) t

    3、连续问题，使用sum()over()进行分组

    执行SQL

    1. select brand,
    2.        start_date,
    3.        end_date,
    4.        max_end_date,
    5.        is_merge,
    6.        sum(is_merge) over (partition by brand order by start_date asc,end_date asc) as group_id
    7. from (select brand,
    8.              start_date,
    9.              end_date,
    10.              max_end_date,
    11.              if(start_date <= max_end_date, 0, 1) as is_merge --0:合并，1:不合并
    12.       from (select brand,
    13.                    start_date,
    14.                    end_date,
    15.                    max(end_date)
    16.                        over (partition by brand order by start_date asc,end_date asc rows between unbounded preceding and 1 preceding) as max_end_date
    17.             from t1_brand_event) t) tt;

    4、完成合并，得到最终结果

    取每个组内的start_day 的最小值作为活动开始日期，end_day的最大值作为活动结束日期，得到最终结果。

    注意分组条件为：brand+group_id

    执行SQL

    1. select brand,
    2.        min(start_date) as start_date,
    3.        max(end_date)   as end_date
    4. from (select brand,
    5.              start_date,
    6.              end_date,
    7.              max_end_date,
    8.              is_merge,
    9.              sum(is_merge) over (partition by brand order by start_date asc,end_date asc) as group_id
    10.       from (select brand,
    11.                    start_date,
    12.                    end_date,
    13.                    max_end_date,
    14.                    if(start_date <= max_end_date, 0, 1) as is_merge --0:合并，1:不合并
    15.             from (select brand,
    16.                          start_date,
    17.                          end_date,
    18.                          max(end_date)
    19.                              over (partition by brand order by start_date asc,end_date asc rows between unbounded preceding and 1 preceding) as max_end_date
    20.                   from t1_brand_event) t) tt) ttt
    21. group by brand, group_id; --注意这里的分组，有group_id

    四、样例数据参考

    1. --建表语句
    2. CREATE TABLE IF NOT EXISTS t1_brand_event (
    3.     brand STRING, --品牌
    4.     start_date STRING, -- 营销活动开始日期
    5.     end_date STRING -- 营销活动结束日期
    6. );
    7. --数据插入
    8. insert into  t1_brand_event(brand, start_date, end_date) values
    9. ('小米','2024-01-13','2024-01-20'),
    10. ('小米','2024-01-14','2024-01-17'),
    11. ('小米','2024-01-14','2024-01-16'),
    12. ('小米','2024-01-18','2024-01-25'),
    13. ('小米','2024-01-20','2024-01-26'),
    14. ('华为','2024-11-09','2024-12-07'),
    15. ('华为','2024-12-09','2024-12-23'),
    16. ('华为','2024-12-13','2024-12-17'),
    17. ('华为','2024-12-20','2024-12-24'),
    18. ('华为','2024-12-25','2024-12-30'),
    19. ('OPPO','2023-12-01','2024-01-30'),
    20. ('OPPO','2023-12-31','2024-06-30');
    • 📢博客主页：https://lansonli.blog.csdn.net
    • 📢欢迎点赞 👍 收藏 ⭐留言 📝 如有错误敬请指正！
    • 📢本文由 Lansonli 原创，首发于 CSDN博客🙉
    • 📢停下休息的时候不要忘了别人还在奔跑，希望大家抓紧时间学习，全力奔赴更美好的生活✨
  • 相关阅读:
    STM32HAL库CRC学习及测试记录
    ansible
    浅析计算机网络体系结构中的专业术语
    PAT乙级真题练习：1001-1004
    【ZYNQ-嵌入式】zynq学习笔记（二）—— GIPO的硬件配置和软件配置
    centos7内存过高排查
    禹晶、肖创柏、廖庆敏《数字图像处理（面向新工科的电工电子信息基础课程系列教材）》Chapter 7插图
    NC61 两数之和
    ForkLift：macOS文件管理器/FTP客户端
    JavaScipt基础（持续更新三）
  • 原文地址：https://blog.csdn.net/xiaoweite1/article/details/140952128