如何在SQLite中查询指定日期范围内各ID的剩余日期区间

我们可以通过**CTE（公共表表达式）**结合窗口函数LEAD()实现需求，核心思路是先整理每个ID的已占用区间，再找出区间间隙，同时补充查询范围首尾的空闲区间。

完整SQL语句 #

WITH params AS (
    -- 定义查询的日期范围
    SELECT '2023-04-02' AS query_start, '2023-04-30' AS query_end
),
sorted_intervals AS (
    -- 按ID分组，对每个ID的已占用区间按开始日期排序，获取下一个区间的开始日期
    SELECT 
        ID,
        Fromdate,
        Todate,
        LEAD(Fromdate) OVER (PARTITION BY ID ORDER BY Fromdate) AS next_fromdate
    FROM temptable
),
all_gaps AS (
    -- 生成所有空闲区间
    -- 1. 查询起始到第一个已占用区间之前的间隙
    SELECT 
        si.ID,
        p.query_start AS Fromdate,
        DATE(si.Fromdate, '-1 day') AS Todate
    FROM sorted_intervals si
    CROSS JOIN params p
    WHERE si.Fromdate > p.query_start
    AND NOT EXISTS (
        SELECT 1 FROM sorted_intervals si2 
        WHERE si2.ID = si.ID AND si2.Fromdate < si.Fromdate
    )
    
    UNION ALL
    
    -- 2. 已占用区间之间的间隙
    SELECT 
        si.ID,
        DATE(si.Todate, '+1 day') AS Fromdate,
        DATE(si.next_fromdate, '-1 day') AS Todate
    FROM sorted_intervals si
    CROSS JOIN params p
    WHERE si.next_fromdate IS NOT NULL
    AND DATE(si.Todate, '+1 day') <= DATE(si.next_fromdate, '-1 day')
    
    UNION ALL
    
    -- 3. 最后一个已占用区间到查询结束的间隙
    SELECT 
        si.ID,
        DATE(si.Todate, '+1 day') AS Fromdate,
        p.query_end AS Todate
    FROM sorted_intervals si
    CROSS JOIN params p
    WHERE si.next_fromdate IS NULL
    AND DATE(si.Todate, '+1 day') <= p.query_end
)
-- 按ID和起始日期排序输出
SELECT ID, Fromdate, Todate
FROM all_gaps
ORDER BY ID, Fromdate;

语句解释 #

1. params CTE：统一定义查询的起止日期，方便后续修改维护。
2. sorted_intervals CTE：对每个ID的已占用区间按开始日期排序，用LEAD()函数获取当前区间的下一个区间起始日期，用于判断区间间隙。
3. all_gaps CTE：分三部分生成空闲区间：
   • 第一部分：针对每个ID的第一个已占用区间，判断查询起始日到该区间前一天是否存在空闲；
   • 第二部分：遍历每个已占用区间，判断当前区间结束次日到下一个区间起始前一日是否存在间隙；
   • 第三部分：针对每个ID的最后一个已占用区间，判断该区间结束次日到查询结束日是否存在空闲。
4. 最后合并所有空闲区间，按ID和起始日期排序输出。

执行结果 #

代入你的temptable数据后，会得到期望的输出：

ID      Fromdate         Todate
1       2023-04-08       2023-04-30
2       2023-04-02       2023-04-04
2       2023-04-07       2023-04-08
2       2023-04-17       2023-04-30
3       2023-04-02       2023-04-08
3       2023-04-12       2023-04-30

内容的提问来源于stack exchange，提问作者Rathin Sarma