如何识别重叠行

Question

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

| 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中是重叠的，因此两行都被计算为重叠，A中的
3. x3与E中的x3重叠，E的结束日期在A.
4. x5的周期内，B和D不重叠，因为B和D的日期都不重叠。

如果开始日期和结束日期是相同的，我可以通过创建一个列来获得这个值，该列是开始日期、结束日期和pID的组合，然后对所有行进行此字段的计数。如果它超过1，那么我就是在标记重叠。但这并不包括x3的场景，其中的开始日期和日期不一样，但在一定时间内仍然重叠。

Answer 1

根据你的叙述，这似乎符合你的逻辑：

select *,
   case 
     when status = 'Rejected' then 'No'
       -- previous row overlaps
     when startDate < -- maybe <=
          max(case when status <> 'Rejected' then endDate end)
          over (partition by pID
                order by startDate, endDate desc
                rows between unbounded preceding and 1 preceding)
       -- next row overlaps
       or endDate > -- maybe >=
          min(case when status <> 'Rejected' then startDate end)
          over (partition by pID
                order by startDate, endDate desc
                rows between 1 following and unbounded following)
     then 'Yes'
     else 'No'
   end 
from tab

Answer 2

像这样吗？

(如果一行与多行重叠，则使用关联子查询来避免返回多行的联接。)

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

请注意>=和<=，这取决于您使用的是包容性(我希望不是)还是独占(我希望如此) endDate。

Answer 3

试试这个(我用的是真和假的布尔值.

WITH
input(identifier,status,startDate,endDate,pID,OVERLAPPING) AS (
          SELECT 'A','Approved',DATE '2020-10-01',DATE '2020-10-07','x1','Yes'
UNION ALL SELECT 'A','Approved',DATE '2020-10-01',DATE '2020-10-07','x2','No'
UNION ALL SELECT 'A','Approved',DATE '2020-10-01',DATE '2020-10-07','x3','Yes'
UNION ALL SELECT 'A','Approved',DATE '2020-10-01',DATE '2020-10-07','x4','No'
UNION ALL SELECT 'B','Approved',DATE '2020-10-10',DATE '2020-10-12','x2','No'
UNION ALL SELECT 'B','Approved',DATE '2020-10-10',DATE '2020-10-12','x5','No'
UNION ALL SELECT 'C','Rejected',DATE '2020-10-05',DATE '2020-10-06','x3','No'
UNION ALL SELECT 'C','Rejected',DATE '2020-10-05',DATE '2020-10-06','x7','No'
UNION ALL SELECT 'C','Rejected',DATE '2020-10-05',DATE '2020-10-06','x8','No'
UNION ALL SELECT 'C','Rejected',DATE '2020-10-05',DATE '2020-10-06','x9','No'
UNION ALL SELECT 'D','Approved',DATE '2020-10-01',DATE '2020-10-07','x5','No'
UNION ALL SELECT 'D','Approved',DATE '2020-10-01',DATE '2020-10-07','x1','Yes'
UNION ALL SELECT 'E','Approved',DATE '2020-10-03',DATE '2020-10-04','x3','Yes'
)
SELECT 
  *
, status = 'Approved'
  AND (
     COALESCE(LAG(enddate)    OVER(w) ,'0001-01-01')> startdate
  OR COALESCE(LEAD(startdate) OVER(w) ,'9999-12-31')< enddate
  ) AS overlap
FROM input
WINDOW w AS (PARTITION BY pid ORDER BY startdate)
ORDER BY
  identifier
, startdate
;
-- out identifier|status  |startDate |endDate   |pID|OVERLAPPING|overlap
-- out A         |Approved|2020-10-01|2020-10-07|x3 |Yes        |true
-- out A         |Approved|2020-10-01|2020-10-07|x2 |No         |false
-- out A         |Approved|2020-10-01|2020-10-07|x4 |No         |false
-- out A         |Approved|2020-10-01|2020-10-07|x1 |Yes        |true
-- out B         |Approved|2020-10-10|2020-10-12|x5 |No         |false
-- out B         |Approved|2020-10-10|2020-10-12|x2 |No         |false
-- out C         |Rejected|2020-10-05|2020-10-06|x3 |No         |false
-- out C         |Rejected|2020-10-05|2020-10-06|x8 |No         |false
-- out C         |Rejected|2020-10-05|2020-10-06|x7 |No         |false
-- out C         |Rejected|2020-10-05|2020-10-06|x9 |No         |false
-- out D         |Approved|2020-10-01|2020-10-07|x1 |Yes        |true
-- out D         |Approved|2020-10-01|2020-10-07|x5 |No         |false
-- out E         |Approved|2020-10-03|2020-10-04|x3 |Yes        |true