怎么做qq网站,网站建设要准备什么软件,公司logo设计图片免费,服装设计网页制作素材目录 1 题目2 建表语句3 题解 1 题目 已知有表记录了每个大厅的活动开始日期和结束日期#xff0c;每个大厅可以有多个活动。请编写一个SQL查询合并在同一个大厅举行的所有重叠的活动#xff0c;如果两个活动至少有一天相同#xff0c;那他们就是重叠的#xff0c;请将他们… 目录 1 题目2 建表语句3 题解 1 题目 已知有表记录了每个大厅的活动开始日期和结束日期每个大厅可以有多个活动。请编写一个SQL查询合并在同一个大厅举行的所有重叠的活动如果两个活动至少有一天相同那他们就是重叠的请将他们的交叉的日期合并。
------------------------------------
| hall_id | start_date | end_date |
------------------------------------
| 1 | 2023-01-13 | 2023-01-14 |
| 1 | 2023-01-14 | 2023-01-17 |
| 1 | 2023-01-18 | 2023-01-25 |
| 2 | 2022-12-09 | 2022-12-23 |
| 2 | 2022-12-13 | 2022-12-17 |
| 3 | 2022-12-01 | 2023-01-30 |
------------------------------------结果如下
------------------------------------
| hall_id | start_date | end_date |
------------------------------------
| 1 | 2023-01-13 | 2023-01-17 |
| 1 | 2023-01-18 | 2023-01-25 |
| 2 | 2022-12-09 | 2022-12-23 |
| 3 | 2022-12-01 | 2023-01-30 |
------------------------------------解释两个活动[2823-01-13,2023-01-14]和[“2023-01-14,2023-01-17]重叠我们将它们合并到一个活动中[“2023-01-13,2023-01-17]。[2023-01-18,2023-01-25]不与任何其他活动重叠所以我们保持原样。 2 建表语句 --建表语句
CREATE TABLE IF NOT EXISTS t_hall_event (hall_id STRING, --大厅IDstart_date STRING, -- 营销活动开始日期end_date STRING -- 营销活动结束日期
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ,
STORED AS ORC;
--数据插入
insert into t_hall_event(hall_id, start_date, end_date) values
(1,2023-01-13,2023-01-14),
(1,2023-01-14,2023-01-17),
(1,2023-01-18,2023-01-25),
(2,2022-12-09,2022-12-23),
(2,2022-12-13,2022-12-17),
(3,2022-12-01,2023-01-30);3 题解 我们首先按照 hall_id 分组根据 start_date、end_date 升序排列按照start_date 进行了升序排列所以当前行的start_date一定晚于前一行的start_date我们只需要对当前行的start_date 和上一行的end_date进行比较如果当前行的start_date 小于等于前一行的end_date 代表有交叉可以合并否则代表不可合并。判断出是否可以合并之后具体操作合并就转化成类似连续问题了。
第一步先使用 lag() 函数进行开窗取到上一行的 end_date
selecthall_id,start_date,end_date,lag(end_date) over (partition by hall_id order by start_date,end_date) as last_end_date
from t_hall_event结果如下
----------------------------------------------------
| hall_id | start_date | end_date | last_end_date |
----------------------------------------------------
| 1 | 2023-01-13 | 2023-01-14 | NULL |
| 1 | 2023-01-14 | 2023-01-17 | 2023-01-14 |
| 1 | 2023-01-18 | 2023-01-25 | 2023-01-17 |
| 2 | 2022-12-09 | 2022-12-23 | NULL |
| 2 | 2022-12-13 | 2022-12-17 | 2022-12-23 |
| 3 | 2022-12-01 | 2023-01-30 | NULL |
----------------------------------------------------第二步根据当前行的 start_day 与上一行的 end_day 进行比较得出是否可以合并标记
select hall_id,start_date,end_date,last_end_date,if(start_date last_end_date, 0, 1) as is_merge --0:合并1:不合并
from (select hall_id,start_date,end_date,lag(end_date) over (partition by hall_id order by start_date asc,end_date asc) as last_end_datefrom t_hall_event) t结果如下
---------------------------------------------------------------
| hall_id | start_date | end_date | last_end_date | is_merge |
---------------------------------------------------------------
| 1 | 2023-01-13 | 2023-01-14 | NULL | 1 |
| 1 | 2023-01-14 | 2023-01-17 | 2023-01-14 | 0 |
| 1 | 2023-01-18 | 2023-01-25 | 2023-01-17 | 1 |
| 2 | 2022-12-09 | 2022-12-23 | NULL | 1 |
| 2 | 2022-12-13 | 2022-12-17 | 2022-12-23 | 0 |
| 3 | 2022-12-01 | 2023-01-30 | NULL | 1 |
---------------------------------------------------------------第三步连续问题使用 sum() over() 进行分组
selecthall_id,start_date,end_date,last_end_date,is_merge,sum(is_merge)over(partition by hall_id order by start_date asc,end_date asc) as group_idfrom(selecthall_id,start_date,end_date,last_end_date,if(start_datelast_end_date,0,1) as is_merge --0:合并1:不合并from(selecthall_id,start_date,end_date,lag(end_date)over(partition by hall_id order by start_date asc,end_date asc) as last_end_datefrom t_hall_event)t) tt结果如下
--------------------------------------------------------------------------
| hall_id | start_date | end_date | last_end_date | is_merge | group_id |
--------------------------------------------------------------------------
| 1 | 2023-01-13 | 2023-01-14 | NULL | 1 | 1 |
| 1 | 2023-01-14 | 2023-01-17 | 2023-01-14 | 0 | 1 |
| 1 | 2023-01-18 | 2023-01-25 | 2023-01-17 | 1 | 2 |
| 2 | 2022-12-09 | 2022-12-23 | NULL | 1 | 1 |
| 2 | 2022-12-13 | 2022-12-17 | 2022-12-23 | 0 | 1 |
| 3 | 2022-12-01 | 2023-01-30 | NULL | 1 | 1 |
--------------------------------------------------------------------------第四步取每个组内的 start_day 的最小值作为活动开始日期end_day 的最大值作为活动结束日期得到最终结果。注意分组条件为 hall_idgroup_id。
selecthall_id,min(start_date) as start_date,max(end_date) as end_datefrom(selecthall_id,start_date,end_date,last_end_date,is_merge,sum(is_merge)over(partition by hall_id order by start_date asc,end_date asc) as group_idfrom(selecthall_id,start_date,end_date,last_end_date,if(start_datelast_end_date,0,1) as is_merge --0:合并1:不合并from(selecthall_id,start_date,end_date,lag(end_date)over(partition by hall_id order by start_date asc,end_date asc) as last_end_datefrom t_hall_event)t) tt) tttgroup by hall_id,group_id --注意这里的分组有group_id结果如下
------------------------------------
| hall_id | start_date | end_date |
------------------------------------
| 1 | 2023-01-13 | 2023-01-17 |
| 1 | 2023-01-18 | 2023-01-25 |
| 2 | 2022-12-09 | 2022-12-23 |
| 3 | 2022-12-01 | 2023-01-30 |
------------------------------------
