HQL面试题练习 —— 合并活动日期

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_date<=last_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_id+group_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_date<=last_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  |
+----------+-------------+-------------+