MySQL根据匹配日期将多行合并为一行

Question

我的数据示例:

+----+------------+------------+-----------+--------------+--+
| ID | startdate  |  enddate   |  status   | lengthofstay |  |
+----+------------+------------+-----------+--------------+--+
|  1 | 2018-02-15 | 2018-02-16 | transfer  |            1 |  |
|  1 | 2018-02-16 | 2018-02-22 | discharge |            6 |  |
|  2 | 2018-03-05 | 2018-03-08 | discharge |            3 |  |
|  1 | 2018-11-01 | 2018-11-03 | transfer  |            2 |  |
|  1 | 2018-11-03 | 2018-11-20 | transfer  |           17 |  |
|  1 | 2018-11-20 | 2018-11-21 | discharge |            1 |  |
|  2 | 2019-05-15 | 2019-05-20 | discharge |            5 |  |
+----+------------+------------+-----------+--------------+--+

我想解决两个问题。
首先，我想根据匹配的 startdate 和 enddate 将具有匹配 ID 的行合并为一行，同时还取长度的总和(startdate 和 enddate 之间的 datediff)。
第二个问题，我有重复的 ID，这些 ID 是在以后不同的场合进入系统的，我希望将其作为单独的观察结果保留。

这是我理想的输出结果:

+----+------------+------------+-----------+--------------+
| ID | startdate  |  enddate   |  status   | lengthofstay |
+----+------------+------------+-----------+--------------+
|  1 | 2018-02-15 | 2018-02-22 | discharge |            7 |
|  2 | 2018-03-05 | 2018-03-08 | discharge |            3 |
|  1 | 2018-11-01 | 2018-11-21 | discharge |           20 |
|  2 | 2019-05-15 | 2019-05-20 | discharge |            5 |
+----+------------+------------+-----------+--------------+

我在 MySQL 方面没有很多经验，我不确定这是否可以通过 join、concat 或 group by with rollup 来实现。我知道在不同的场合有重复的 ID 是一个额外的问题，所以我正在考虑根据 startdate 与每个唯一 ID 的最后一个 enddate 相距多远(例如 3 天的余量)来使用另一个标识符，但我没有也知道该怎么做。

我发现了一个类似的问题 here但没有答案。

我很欣赏任何见解!

Answer 1

这是一个缺口和孤岛问题。这是使用 MySQL 8.0 中可用的窗口函数解决它的一种方法:

select
    id,
    min(startdate) startdate,
    max(enddate) enddate,
    last_status status,
    sum(lengthofstay) lengthofstay
from (
    select
        t.*,
        last_value(status) over(partition by id, rn1 - rn2) last_status
    from (
        select
            t.*,
            row_number() over(order by startdate) rn1,
            row_number() over(partition by id order by startdate) rn2
        from mytable t
    ) t
) t
group by
    id,
    last_status,
    rn1 - rn2
order by min(startdate)

该查询通过对两个不同分区上的记录进行排名来工作；等级之间的差异为您提供了它所属的组。然后， last_value()可用于检索每个组中的最后状态。最后一步是聚合。

Demo on DB Fiddle :

身份证 |开始日期 |结束日期 |状态 |停留时间
-: | :--------- | :--------- | :-------- | -----------:
1 | 2018-02-15 | 2018-02-22 |放电| 7
2 | 2018-03-05 | 2018-03-08 |放电| 3
1 | 2018-11-01 | 2018-11-21 |放电| 20
2 | 2019-05-15 | 2019-05-20 |放电| 5