Question

2494. 合并在同一个大厅重叠的活动

English Version

题目描述

表: HallEvents

+-------------+------+
| Column Name | Type |
+-------------+------+
| hall_id   | int  |
| start_day   | date |
| end_day   | date |
+-------------+------+
该表可能包含重复字段。
该表的每一行表示活动的开始日期和结束日期，以及活动举行的大厅。

编写解决方案，合并在 同一个大厅举行 的所有重叠活动。如果两个活动 至少有一天 相同，那么它们就是重叠的。

以任意顺序返回结果表。

结果格式如下所示。

 

示例 1:

输入: 
HallEvents 表:
+---------+------------+------------+
| hall_id | start_day  | end_day  |
+---------+------------+------------+
| 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_day  | end_day  |
+---------+------------+------------+
| 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 |
+---------+------------+------------+
解释: 有三个大厅。
大厅 1:
- 两个活动 ["2023-01-13", "2023-01-14"] 和 ["2023-01-14", "2023-01-17"] 重叠。我们将它们合并到一个活动中 ["2023-01-13", "2023-01-17"]。
- 活动 ["2023-01-18", "2023-01-25"] 不与任何其他活动重叠，所以我们保持原样。
大厅 2:
- 两个活动 ["2022-12-09", "2022-12-23"] 和 ["2022-12-13", "2022-12-17"] 重叠。我们将它们合并到一个活动中 ["2022-12-09", "2022-12-23"]。
大厅 3:
- 大厅只有一个活动，所以我们返回它。请注意，我们只分别考虑每个大厅的活动。

解法

方法一

# Write your MySQL query statement below
WITH
  S AS (
    SELECT
      hall_id,
      start_day,
      end_day,
      MAX(end_day) OVER (
        PARTITION BY hall_id
        ORDER BY start_day
      ) AS cur_max_end_day
    FROM HallEvents
  ),
  T AS (
    SELECT
      *,
      IF(
        start_day <= LAG(cur_max_end_day) OVER (
          PARTITION BY hall_id
          ORDER BY start_day
        ),
        0,
        1
      ) AS start
    FROM S
  ),
  P AS (
    SELECT
      *,
      SUM(start) OVER (
        PARTITION BY hall_id
        ORDER BY start_day
      ) AS gid
    FROM T
  )
SELECT hall_id, MIN(start_day) AS start_day, MAX(end_day) AS end_day
FROM P
GROUP BY hall_id, gid;