快速模板建站工具,天津专门做企业网站公司,建设图片展示网站,手机网站支付签约文章目录
合并日期重叠的活动
一、题目
二、分析
三、SQL实战
四、样例数据参考 合并日期重叠的活动
一、题目
已知有表记录了每个品牌的活动开始日期和结束日期#xff0c;每个品牌可以有多个活动。请编写一个SQL查询合并在同一个品牌举行的所有重叠的活动#xff0c…
文章目录
合并日期重叠的活动
一、题目
二、分析
三、SQL实战
四、样例数据参考 合并日期重叠的活动
一、题目
已知有表记录了每个品牌的活动开始日期和结束日期每个品牌可以有多个活动。请编写一个SQL查询合并在同一个品牌举行的所有重叠的活动如果两个活动至少有一天相同那他们就是重叠的。
样例数据 目标结果 解释
有两个品牌分别为小米和华为。
小米:
两个活动[2024-01-132023-01-20]和[“2024-01-142024-01-17]重叠我们将它们合并到一个活动中[“2024-01-132024-01-20]。
华为
[2024-11-092024-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 selectbrand,start_date,end_date,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
from t1_brand_event; 2、根据当前行的start_day与max_end_date进行比较得出是否可以合并标记
执行SQL select brand,start_date,end_date,max_end_date,if(start_date max_end_date, 0, 1) as is_merge --0:合并1:不合并
from (select brand,start_date,end_date,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_datefrom t1_brand_event) t 3、连续问题使用sum()over()进行分组
执行SQL select brand,start_date,end_date,max_end_date,is_merge,sum(is_merge) over (partition by brand order by start_date asc,end_date asc) as group_id
from (select brand,start_date,end_date,max_end_date,if(start_date max_end_date, 0, 1) as is_merge --0:合并1:不合并from (select brand,start_date,end_date,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_datefrom t1_brand_event) t) tt; 4、完成合并得到最终结果
取每个组内的start_day 的最小值作为活动开始日期end_day的最大值作为活动结束日期得到最终结果。
注意分组条件为brandgroup_id
执行SQL select brand,min(start_date) as start_date,max(end_date) as end_date
from (select brand,start_date,end_date,max_end_date,is_merge,sum(is_merge) over (partition by brand order by start_date asc,end_date asc) as group_idfrom (select brand,start_date,end_date,max_end_date,if(start_date max_end_date, 0, 1) as is_merge --0:合并1:不合并from (select brand,start_date,end_date,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_datefrom t1_brand_event) t) tt) ttt
group by brand, group_id; --注意这里的分组有group_id 四、样例数据参考 --建表语句
CREATE TABLE IF NOT EXISTS t1_brand_event (brand STRING, --品牌start_date STRING, -- 营销活动开始日期end_date STRING -- 营销活动结束日期
);
--数据插入
insert into t1_brand_event(brand, start_date, end_date) values
(小米,2024-01-13,2024-01-20),
(小米,2024-01-14,2024-01-17),
(小米,2024-01-14,2024-01-16),
(小米,2024-01-18,2024-01-25),
(小米,2024-01-20,2024-01-26),
(华为,2024-11-09,2024-12-07),
(华为,2024-12-09,2024-12-23),
(华为,2024-12-13,2024-12-17),
(华为,2024-12-20,2024-12-24),
(华为,2024-12-25,2024-12-30),
(OPPO,2023-12-01,2024-01-30),
(OPPO,2023-12-31,2024-06-30); 博客主页https://lansonli.blog.csdn.net欢迎点赞 收藏 ⭐留言 如有错误敬请指正本文由 Lansonli 原创首发于 CSDN博客停下休息的时候不要忘了别人还在奔跑希望大家抓紧时间学习全力奔赴更美好的生活✨
