查找跨记录的日期范围之间的差距

Question

>我正在尝试编写一个查询，在传入两个日期时，我可以在其中找到给定ID的日期范围中的任何间隙。

编辑：我需要知道我的日期范围内是否存在整个差距或部分差距。

我有这种格式的数据：

Example 1:
| ID | START_DATE | END_DATE   |
|----|------------|------------|
| 1  | 01/01/2019 | 30/09/2019 |
| 1  | 01/03/2020 | (null)     |
Example 2:
| ID | START_DATE | END_DATE   |
|----|------------|------------|
| 2  | 01/01/2019 | 30/09/2019 |
| 2  | 01/10/2019 | 01/12/2019 |
| 2  | 02/12/2019 | (null)     |

铌。空结束日期本质上意味着"直到今天仍然有效"。

例如Example 1在 30/09/2019 和 01/03/2020 之间有 152 天的间隔。如果我在05/05/2019 - 01/09/2019范围内查询，则该范围内没有间隙。然而，如果我查看日期范围05/05/2019 - 02/10/2019该范围内只有一个一天的差距。

对于它的价值，我实际上并不在乎有多少天，只是在乎是否有。

我试过做这样的事情，但是当我的约会陷入空白时，它不起作用：

SELECT SUM(START_DATE - PREV_END - 1)
FROM
(
SELECT ID, START_DATE, END_DATE, LAG(END_DATE) OVER (ORDER BY START_DATE) AS PREV_END_DATE
FROM TBL
WHERE ID = X_ID
)
WHERE START_DATE >= Y_FIRST_DATE
AND START_DATE <= Z_SECOND_DATE;

X_ID、Y_FIRST_DATE和Z_SECOND_DATE只是我可能想要传入的任何不同的 ID 或日期范围。

我该怎么做呢？

Answer 1

确定天数的另一种选择可能是通过使用SELECT .. FROM dual CONNECT BY LEVEL <=语法EXIST通过INTERSECT两个集合来消除间隙，一个查找极值参数之间的所有日期，而另一个查找插入到表中的日期作为边界的所有日期：

SELECT CASE WHEN 
SUM( 1 + LEAST(Z_SECOND_DATE,NVL(END_DATE,TRUNC(SYSDATE))) 
- GREATEST(Y_FIRST_DATE,START_DATE) ) = Z_SECOND_DATE - Y_FIRST_DATE + 1 THEN
'NO Gap'
ELSE
'Gap Exists'  
END "gap?"
FROM TBL t
WHERE ID = X_ID
AND EXISTS ( SELECT Y_FIRST_DATE + LEVEL - 1
FROM dual
CONNECT BY LEVEL <= Z_SECOND_DATE - Y_FIRST_DATE + 1 
INTERSECT
SELECT t.START_DATE + LEVEL - 1
FROM dual
CONNECT BY LEVEL <= NVL(t.END_DATE,TRUNC(SYSDATE))- t.START_DATE + 1
)    

根据示例数据，START_DATE值假定为非 null。

演示

Answer 2

这是这里经常出现的岛屿和间隙问题的另一种变体。 我认为这符合 Oracle 的模式匹配功能。 举个例子：

WITH tbl AS
(
SELECT 1 AS ID, to_date('01/01/2019', 'DD/MM/YYYY') AS START_DATE, to_date('30/09/2019', 'DD/MM/YYYY') AS END_DATE FROM DUAL
UNION ALL
SELECT 1 AS ID, to_date('01/03/2020', 'DD/MM/YYYY') AS START_DATE, NULL AS END_DATE FROM DUAL
UNION ALL
SELECT 2 AS ID, to_date('01/01/2019', 'DD/MM/YYYY') AS START_DATE, to_date('30/09/2019', 'DD/MM/YYYY') AS END_DATE FROM DUAL
UNION ALL
SELECT 2 AS ID, to_date('01/10/2019', 'DD/MM/YYYY') AS START_DATE, to_date('01/12/2019', 'DD/MM/YYYY') AS END_DATE FROM DUAL
UNION ALL
SELECT 2 AS ID, to_date('02/12/2019', 'DD/MM/YYYY') AS START_DATE, NULL AS END_DATE FROM DUAL
)
SELECT *
FROM tbl
MATCH_RECOGNIZE(ORDER BY ID, start_date
MEASURES b.id AS ID,
a.end_date+1 AS GAP_START,
b.start_date-1 AS GAP_END
PATTERN (A B+)
DEFINE B AS start_date > PREV(end_date)+1 AND ID = PREV(ID))L;

我知道它看起来很长，但其中大部分都在创建 WITH 子句。 模式匹配允许您定义差距是什么并相应地提取信息。 请注意，为了有间隔，您的开始日期必须大于上一个结束日期 + 1(按 ID 列分组)。

要增强此功能以回答您更新/编辑的问题，只需将以下代码行添加到末尾：

WHERE GREATEST(gap_start, TO_DATE('15/09/2019', 'DD/MM/YYYY' /*Y_FIRST_DATE*/)) <= LEAST(gap_end, to_date('15/10/2019', 'DD/MM/YYYY')/*Z_SECOND_DATE*/)

Answer 3

您可以将要传递的日期范围拆分为日期，然后将其与表中的日期范围进行比较，如下所示：

SELECT
CASE WHEN SUM(CASE WHEN T.ID IS NULL THEN 1 END) > 0 
THEN 'THERE IS GAP'
ELSE 'THERE IS NO GAP'
END AS RESULT_
FROM ( SELECT P_IN_FROM_DATE + LEVEL - 1 AS CUST_DATES
FROM DUAL
CONNECT BY LEVEL <= P_IN_TO_DATE - P_IN_FROM_DATE + 1
) CUST_TBL
LEFT JOIN TBL T 
ON CUST_TBL.CUST_DATES BETWEEN T.START_DATE AND T.END_DATE
OR ( CUST_TBL.CUST_DATES >= T.START_DATE AND T.END_DATE IS NULL )

Answer 4

我建议根据开始日期找到当前记录之前的最长结束日期。

那将是：

select t.*
from (select t.*,
max(end_date) over (order by start_date
rows between unbounded preceding and 1 preceding
) as max_prev_end_date
from tbl t
where start_date <= :input_end_date and
end_date >= :input_start_date
) t
where max_prev_end_date < start_date;