时间戳

首页 > 解决方案 > 如何识别重叠行

sql - 如何识别重叠行

问题描述

我需要识别重叠的行。下面是表格，我需要列重叠：

| identifier    | status    | startDate     | endDate       | pID   | OVERLAPPING   |
|------------   |---------- |------------   |------------   |-----  |-------------  |
| A             | Approved  | 2020-10-01    | 2020-10-07    | x1    | Yes           |
| A             | Approved  | 2020-10-01    | 2020-10-07    | x2    | No            |
| A             | Approved  | 2020-10-01    | 2020-10-07    | x3    | Yes           |
| A             | Approved  | 2020-10-01    | 2020-10-07    | x4    | No            |
| B             | Approved  | 2020-10-10    | 2020-10-12    | x2    | No            |
| B             | Approved  | 2020-10-10    | 2020-10-12    | x5    | No            |
| C             | Rejected  | 2020-10-05    | 2020-10-06    | x3    | No            |
| C             | Rejected  | 2020-10-05    | 2020-10-06    | x7    | No            |
| C             | Rejected  | 2020-10-05    | 2020-10-06    | x8    | No            |
| C             | Rejected  | 2020-10-05    | 2020-10-06    | x9    | No            |
| D             | Approved  | 2020-10-01    | 2020-10-07    | x5    | No            |
| D             | Approved  | 2020-10-01    | 2020-10-07    | x1    | Yes           |
| E             | Approved  | 2020-10-03    | 2020-10-04    | x3    | Yes           |
  1. 由于 C 处于拒绝状态，因此任何具有 C 标识符的内容都不会被计入查找重叠行。因此即使 C 中的坚韧 x3 与 A 中的 x3 重叠，也不会被视为重叠。
  2. x1 在 A 和 D 中重叠。因此这两行都将被计为重叠。
  3. A 中的 x3 与 E 中的 x3 重叠，因为 E 的开始日期和结束日期在 A 的期间内。
  4. B 和 D 中的 x5 不重叠，因为 B 和 D 的日期不重叠。

如果开始日期和结束日期相同，我可以通过创建一个由开始日期、结束日期和 pID 组合而成的列，然后计算所有行的该字段来获得此信息。如果它大于 1，那么我标记重叠。但这不包括 x3 的场景，其中开始日期和日期不相同但在一段时间内仍然重叠。

标签： sqldateamazon-redshift

解决方案

像这样的东西？

（如果一行与其他行重叠，我使用相关子查询来避免返回多行的连接。）

SELECT
  *,
  (
    SELECT 1
      FROM yourTable AS lookup
     WHERE lookup.identifier <> yourTable.identifier  -- Don't check overlaps with itself
       AND lookup.pID         = yourTable.pID
       AND lookup.startDate  <= yourTable.endDate
       AND lookup.endDate    >= yourTable.startDate
     LIMIT 1
  )
FROM
  yourTable

注意>=and <=，这取决于您使用的是包容性（我希望不是）还是排他性（我希望如此）endDate。

推荐阅读