如何用SQL（含HANA SQLScript）查询覆盖指定日期区间的分组

核心思路 #

先过滤掉与目标区间完全不重叠的无效条目，再通过递归CTE对每个分组的有效条目合并重叠/连续区间，最后验证合并后的区间是否能完全覆盖目标日期范围。该方法支持任意数量的分组条目，解决原LAG方案的局限性。

具体SQL实现（适配SAP HANA SQLScript/AMDP） #

-- 定义目标区间变量，可根据需求修改
DECLARE @TARGET_FROM DATE = '2025-02-01';
DECLARE @TARGET_TO DATE = '2025-02-29'; -- 注意：2月无30号，此处修正为合法日期

WITH FilteredEntries AS (
    -- 第一步：筛选每个分组中与目标区间有交集的条目
    SELECT 
        group_id,
        valid_from,
        valid_to
    FROM your_table_name
    WHERE 
        valid_to >= @TARGET_FROM 
        AND valid_from <= @TARGET_TO
),
SortedEntries AS (
    -- 第二步：对每个分组的条目按valid_from排序，添加序号用于递归
    SELECT 
        group_id,
        valid_from,
        valid_to,
        ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY valid_from) AS rn
    FROM FilteredEntries
),
MergedIntervals AS (
    -- 递归CTE的起始：每个分组的第一条条目
    SELECT 
        group_id,
        valid_from AS merged_from,
        valid_to AS merged_to,
        rn
    FROM SortedEntries
    WHERE rn = 1

    UNION ALL

    -- 递归逻辑：合并当前条目与上一个合并后的区间
    SELECT 
        se.group_id,
        -- 合并后的起始取较小值
        LEAST(se.valid_from, mi.merged_from),
        -- 合并后的结束取较大值（如果当前条目与上一个合并区间重叠/连续）
        CASE 
            WHEN se.valid_from <= mi.merged_to + INTERVAL '1' DAY THEN GREATEST(se.valid_to, mi.merged_to)
            ELSE se.valid_to
        END AS merged_to,
        se.rn
    FROM SortedEntries se
    INNER JOIN MergedIntervals mi 
        ON se.group_id = mi.group_id 
        AND se.rn = mi.rn + 1
),
FinalMerged AS (
    -- 每个分组保留最终合并后的最大覆盖区间（因为递归过程中会有中间结果，取每个分组的最后一条递归结果）
    SELECT 
        group_id,
        merged_from,
        merged_to
    FROM MergedIntervals mi
    WHERE rn = (SELECT MAX(rn) FROM MergedIntervals WHERE group_id = mi.group_id)
)
-- 第三步：筛选出能完全覆盖目标区间的分组
SELECT DISTINCT group_id
FROM FinalMerged
WHERE 
    merged_from <= @TARGET_FROM 
    AND merged_to >= @TARGET_TO;

代码说明 #

• FilteredEntries：剔除完全在目标区间外的条目，减少后续计算量。
• SortedEntries：对每个分组的有效条目按起始日期排序，生成序号用于递归遍历。
• MergedIntervals：递归合并区间：
  • 初始层取每个分组的第一条条目作为初始合并区间。
  • 递归层将当前条目与上一个合并区间对比：如果当前条目的起始日期小于等于上一个合并区间的结束日期+1天（视为连续），则合并为一个更大的区间；否则保留为独立区间。
• FinalMerged：提取每个分组最终的合并区间（递归的最后一条结果，即覆盖范围最大的合并区间）。
• 最终查询：验证合并后的区间是否完全包含目标区间，输出符合条件的group_id。

注意事项 #

• 若日期字段存储为字符串（如20250201），需先转换为DATE类型：TO_DATE(valid_from, 'YYYYMMDD')。
• 若分组存在多个不连续但组合后能覆盖目标区间的条目（比如目标区间1-10，分组有1-5和6-10），代码会自动合并为连续区间并识别为符合条件。
• 在AMDP中使用时，可将变量替换为AMDP的输入参数，或直接硬编码目标区间。

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