mysql - SQL 按日期分组冲突

Question

我有一个包含 start_date 和 end_date 列的表。我们需要做的是选择所有内容并按每个 Object_ID 的日期冲突对它们进行分组。

日期冲突是指一行的开始日期和/或结束日期穿过另一行。例如，这里有一些冲突的例子:

第 1 行的日期为第 1 到第 5，第 2 行的日期为第 2 到第 3。

第 1 行的日期为第 2 到第 5，第 2 行的日期为第 1 到第 3。

第 1 行的日期为第 2 到第 5 个，第 2 行的日期为第 3 到第 6 个。

第 1 行的日期为第 2 到第 5，第 2 行的日期为第 1 到第 7。

例如，如果我们有一些示例数据(为简单起见，假设数字只是一个月中的几天):

id | object_id | start_date | end_date
1  | 1         | 1          | 5
2  | 1         | 2          | 4
3  | 1         | 6          | 8
4  | 2         | 2          | 3

我希望看到的是:

object_id | start_date | end_date | numconflicts
1         | <na>       | <na>     | 2
1         | 6          | 8        | 0 or null
2         | 2          | 3        | 0 or null

对于第二个测试用例，这是一些示例数据:

id | object_id | start_date | end_date
1  | 1         | 1          | 5
2  | 1         | 2          | 4
3  | 1         | 6          | 8
4  | 2         | 2          | 3
5  | 2         | 4          | 5
6  | 1         | 2          | 3
7  | 1         | 10         | 12
8  | 1         | 11         | 13

对于第二个测试用例，我希望看到的输出是:

object_id | start_date | end_date | numconflicts
1         | <na>       | <na>     | 3
1         | 6          | 8        | 0 or null
2         | 2          | 3        | 0 or null
2         | 4          | 5        | 0 or null
1         | <na>       | <na>     | 2

是的，我需要一些方法来区分第一组和第二组(第一行和最后一行)，但我还没有完全弄明白。目标是查看此列表，然后当您单击一组冲突时，您可以查看该组中的所有冲突。

我的第一个想法是尝试一些 GROUP BY CASE ... 子句，但我只是把头缠在自己身上。

我调用mysql的语言是php。因此，如果有人知道 php-loop 解决方案而不是大型 mysql 查询，我会洗耳恭听。

提前致谢。

编辑:添加到主键中以减少困惑。

编辑:添加到测试用例 2 中以提供更多推理。

Answer 1

此查询查找重复项的数量:

select od1.object_id, od1.start_date, od1.end_date, sum(od2.id is not null) as dups
from object_date od1
left join object_date od2
    on od2.object_id = od1.object_id
    and od2.end_date >= od1.start_date
    and od2.start_date <= od1.end_date
    and od2.id != od1.id
group by 1,2,3;

您可以将此查询用作查询的基础，从而准确地为您提供所要求的内容(输出见下文)。

select
  object_id,
  case dups when 0 then start_date else '<na>' end as start_date,
  case dups when 0 then end_date else '<na>' end as end_date,
  sum(dups) as dups
from (
  select od1.object_id, od1.start_date, od1.end_date, sum(od2.id is not null) as dups
  from object_date od1
  left join object_date od2
    on od2.object_id = od1.object_id
    and od2.end_date >= od1.start_date
    and od2.start_date <= od1.end_date
    and od2.id != od1.id
  group by 1,2,3) x
group by 1,2,3;

请注意，我使用了 id 列来区分行。但是，您可以将 id 不匹配的测试替换为对每一列的比较，即将 od2.id != od1.id 替换为其他所有列不相等的测试，但这需要一个唯一的在所有其他列上建立索引是有意义的，无论如何拥有一个 id 列是个好主意。

这是使用您的数据进行的测试:

create table object_date (
    id int primary key auto_increment,
    object_id int,
    start_date int,
    end_date int
);
insert into object_date (object_id, start_date, end_date) 
    values (1,1,5),(1,2,4),(1,6,8),(2,2,3);

针对此样本数据运行时第一个查询的输出:

+-----------+------------+----------+------+
| object_id | start_date | end_date | dups |
+-----------+------------+----------+------+
|         1 |          1 |        5 |    1 |
|         1 |          2 |        4 |    1 |
|         1 |          6 |        8 |    0 |
|         2 |          2 |        3 |    0 |
+-----------+------------+----------+------+

针对此示例数据运行时的第二个查询的输出:

+-----------+------------+----------+------+
| object_id | start_date | end_date | dups |
+-----------+------------+----------+------+
|         1 | 6          | 8        |    0 |
|         1 | <na>       | <na>     |    2 |
|         2 | 2          | 3        |    0 |
+-----------+------------+----------+------+