如何用SQL查询特定发行方单日未被请求覆盖的时间间隙？

问题背景 #

我有一张mht_issuer_revoked_call表，记录了发行方调用服务的日期和时间。作为SQL新手，想要编写查询语句，找出特定发行方在某一天中未被请求覆盖的时间区间。之前尝试按issuerid分区、按startdate排序，用lag函数对比前后记录的startdate和enddate生成新的起止日期，但结果不符合预期，尝试的代码如下：

select r.*,
        case
          when r.startdate > lag(r.enddate) over(partition by r.issuerid order by r.startdate) then r.startdate
          else min(r.startdate) over(partition by r.issuerid order by r.startdate)
        end startdate_new,
        case
          when lag(r.enddate) over(partition by r.issuerid order by r.startdate) is null then r.enddate
          when r.enddate <= lag(r.enddate) over(partition by r.issuerid order by r.startdate) then lag(r.enddate) over(partition by r.issuerid order by r.startdate)
          when r.enddate > lag(r.enddate) over(partition by r.issuerid order by r.startdate)  then r.enddate
        end enddate_new
  from mht_issuer_revoked_call r

解决方案 #

要找出未被请求覆盖的区间，核心是先合并所有重叠或连续的请求区间，再计算这些合并区间与当天边界、区间之间的间隙。以下是具体实现步骤：

1. 筛选目标数据并标记连续区间

先筛选出特定发行方和目标日期的记录，用窗口函数标记哪些记录属于同一个连续/重叠区间：

WITH filtered_data AS (
    SELECT 
        startdate,
        enddate
    FROM mht_issuer_revoked_call
    WHERE 
        issuerid = '目标发行方ID'
        AND CAST(startdate AS DATE) = '目标日期' -- 仅保留当天的请求记录
),
marked_intervals AS (
    SELECT 
        startdate,
        enddate,
        -- 标记当前区间是否与前一个区间断开（前区间的结束时间早于当前区间的开始时间）
        SUM(CASE WHEN startdate > LAG(enddate) OVER (ORDER BY startdate) THEN 1 ELSE 0 END) 
            OVER (ORDER BY startdate) AS group_id
    FROM filtered_data
)

2. 合并重叠/连续区间

基于分组结果，合并每个组内的区间，得到当天所有被请求覆盖的连续大区间：

,merged_intervals AS (
    SELECT 
        MIN(startdate) AS covered_start,
        MAX(enddate) AS covered_end
    FROM marked_intervals
    GROUP BY group_id
    ORDER BY covered_start
),

3. 生成未覆盖的区间

计算当天边界与合并区间之间的间隙，得到最终的未被请求覆盖的时间区间：

date_boundaries AS (
    SELECT 
        CAST('目标日期' AS DATETIME) AS day_start,
        DATEADD(SECOND, -1, DATEADD(DAY, 1, CAST('目标日期' AS DATETIME))) AS day_end
)
-- 处理当天开始到第一个覆盖区间的间隙
SELECT 
    db.day_start AS uncovered_start,
    mi.covered_start AS uncovered_end
FROM date_boundaries db
CROSS JOIN merged_intervals mi
WHERE mi.group_id = 1 AND mi.covered_start > db.day_start

UNION ALL
-- 处理覆盖区间之间的间隙
SELECT 
    mi.covered_end AS uncovered_start,
    next_mi.covered_start AS uncovered_end
FROM merged_intervals mi
JOIN merged_intervals next_mi ON next_mi.group_id = mi.group_id + 1
WHERE mi.covered_end < next_mi.covered_start

UNION ALL
-- 处理最后一个覆盖区间到当天结束的间隙
SELECT 
    mi.covered_end AS uncovered_start,
    db.day_end AS uncovered_end
FROM merged_intervals mi
CROSS JOIN date_boundaries db
WHERE mi.group_id = (SELECT MAX(group_id) FROM marked_intervals)
    AND mi.covered_end < db.day_end;

注意事项 #

• 替换代码中的'目标发行方ID'和'目标日期'为实际业务值
• 若使用的SQL方言不支持DATEADD，可替换为对应语法（比如PostgreSQL用INTERVAL）
• 最终结果会过滤掉所有无效区间（起始时间≥结束时间的情况）

内容的提问来源于stack exchange，提问作者pourya najafrad