聚合日期范围，不包括重叠日期

Question

我有一个表，存储被借出车辆的记录。数据样本如下：

分类00001000020000200002

Answer 1

您可以使用：

WITH days ( asset_id, loan_start, day_end, loan_end) AS (
SELECT asset_id,
loan_start,
LEAST(TRUNC(loan_start) + INTERVAL '1' DAY, loan_end),
loan_end
FROM   table_name
MATCH_RECOGNIZE (
PARTITION BY asset_id
ORDER     BY loan_start, loan_end
MEASURES
FIRST(loan_start) AS loan_start,
MAX(loan_end)     AS loan_end
ONE ROW PER MATCH
PATTERN (overlaps* next_time)
DEFINE
overlaps  AS MAX(overlaps.loan_end) >= loan_start,
next_time AS MAX(overlaps.loan_end) >= loan_start
OR MAX(overlaps.loan_end) IS NULL
)
UNION ALL
SELECT asset_id,
day_end,
LEAST(TRUNC(day_end) + INTERVAL '1' DAY, loan_end),
loan_end
FROM   days
WHERE  day_end < loan_end
)
SEARCH DEPTH FIRST BY asset_id, loan_start SET order_id
SELECT asset_id,
TO_CHAR(TRUNC(loan_start), 'YYYY-MM-DD') AS day,
SUM(day_end - loan_start) AS amt_unavailable
FROM   days
GROUP BY
asset_id,
TRUNC(loan_start)

对于样本数据：

CREATE TABLE table_name (ASSET_ID, LOAN_START, LOAN_END) AS
SELECT '00001', DATE '2022-01-01' + INTERVAL '14:00' HOUR TO MINUTE, DATE '2022-01-03' + INTERVAL '08:00' HOUR TO MINUTE FROM DUAL UNION ALL
SELECT '00002', DATE '2022-01-15' + INTERVAL '08:30' HOUR TO MINUTE, DATE '2022-01-15' + INTERVAL '10:00' HOUR TO MINUTE FROM DUAL UNION ALL
SELECT '00002', DATE '2022-01-15' + INTERVAL '09:30' HOUR TO MINUTE, DATE '2022-01-15' + INTERVAL '10:30' HOUR TO MINUTE FROM DUAL UNION ALL
SELECT '00002', DATE '2022-01-15' + INTERVAL '12:00' HOUR TO MINUTE, DATE '2022-01-15' + INTERVAL '18:00' HOUR TO MINUTE FROM DUAL;

输出：

1.333333333333333.33333333333333333

ASSET_ID	DAY	AMT_UNAVAILABLE
00001	2022-01-01	。416666666666666661666666666667
00001	2022-01
00001	2022-01-03
00002	2022-015