使用GROUP BY
配合HAVING
子句以及条件聚合来检查区间是否与您要检验的范围重叠。
选项1:当itemid的任何部分与期望范围重叠时,则认为该itemid有效:
SELECT itemid
FROM table_name
GROUP BY itemid
HAVING COUNT(
CASE
WHEN validfrom <= DATE '2024-01-03' + INTERVAL '23:59:59' HOUR TO SECOND
AND ( validto IS NULL
OR validto >= DATE '2024-01-03'
)
THEN 1
END
) = 0
选项2:只有当整个期望范围完全重叠时,才认为itemid有效:
SELECT itemid
FROM table_name
GROUP BY itemid
HAVING COUNT(
CASE
WHEN validfrom <= DATE '2024-01-03'
AND ( validto IS NULL
OR validto >= DATE '2024-01-03' + INTERVAL '23:59:59' HOUR TO SECOND
)
THEN 1
END
) = 0
对于以下示例数据:
CREATE TABLE table_name (
ITEMID INT,
VALIDFROM TIMESTAMP,
VALIDTO TIMESTAMP
) AS
SELECT 1, DATE '2024-01-01', DATE '2024-01-02' + INTERVAL '23:59:59' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 1, DATE '2024-01-04', DATE '2024-01-10' + INTERVAL '23:59:59' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 1, DATE '2024-01-15', null FROM DUAL UNION ALL
SELECT 2, DATE '2023-12-30', DATE '2024-01-02' + INTERVAL '23:59:59' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 3, DATE '2023-12-31', null FROM DUAL UNION ALL
SELECT 4, DATE '2023-12-30', DATE '2024-01-03' + INTERVAL '23:59:59' HOUR TO SECOND FROM DUAL;
两种查询都将输出:
| ITEMID |
|--------|
| 2 |
| 1 |
但如果添加一行额外的数据:
INSERT INTO table_name (ITEMID, VALIDFROM, VALIDTO)
VALUES (5, DATE '2024-01-03' + INTERVAL '1' HOUR, DATE '2024-01-03' + INTERVAL '2' HOUR);
那么:
- 第一个查询将排除那一行,因为它在期望范围内有一个有效的部分范围,并只返回
1,2
;
- 第二个查询将包含那一行,因为没有一个有效范围完全覆盖期望范围,因此会返回
1,2,5
。
对选项2的澄清:如果期望范围被多个连续的部分范围完全覆盖
如果有多个连续的有效范围,且期望范围完全匹配但跨越了两个或更多不连续的有效范围,例如:
INSERT INTO table_name (ITEMID, VALIDFROM, VALIDTO)
SELECT 6, DATE '2024-01-02', DATE '2024-01-03' + INTERVAL '01:59:59' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 6, DATE '2024-01-03' + INTERVAL '2' HOUR, DATE '2024-01-03' + INTERVAL '23:59:59' HOUR TO SECOND FROM DUAL;
此时,第一个查询仍然可以正常工作,如果希望只要有任何部分匹配就认为期望范围匹配。然而,第二个查询在这种边界情况下无法正确处理;这时需要先使用(从Oracle 12版本开始支持的)MATCH_RECOGNIZE
合并连续的范围,然后再应用之前的逻辑:
SELECT itemid
FROM table_name
MATCH_RECOGNIZE(
PARTITION BY itemid
ORDER BY validfrom
MEASURES
FIRST(validfrom) AS validfrom,
LAST(validto) AS validto
PATTERN (consecutive* last_row)
DEFINE
consecutive AS validto + INTERVAL '1' SECOND = NEXT(validfrom)
)
GROUP BY itemid
HAVING COUNT(
CASE
WHEN validfrom <= DATE '2024-01-03'
AND ( validto IS NULL
OR validto >= DATE '2024-01-03' + INTERVAL '23:59:59' HOUR TO SECOND
)
THEN 1
END
) = 0
这将输出 1,2,5
。
在线示例