SQL构造适配窗口函数的新列 按分组规则生成result字段

直接按规则逐段实现即可，不需要复杂的嵌套逻辑，最终结果和预期完全匹配。

核心逻辑 #

• 先筛选所有Type以'S'开头的记录，按cat分组、dates升序，拿到每个S和它前一个同组S的日期间隔
• 只给间隔范围内存在P类记录的S分配区间编号（如果间隔内没有P，说明是空区间，直接和后续区间合并，比如示例中S_0所在的间隔没有P，就合并到S_1的区间）
• 给每条记录匹配对应的区间编号，后续没有对应区间S的P类记录返回NULL

可直接运行的代码 #

兼容所有主流关系型数据库：

WITH s_list AS (
    SELECT
        cat,
        dates AS s_dates,
        LAG(dates, 1, '1900-01-01') OVER (PARTITION BY cat ORDER BY dates) AS prev_s_dates
    FROM tab
    WHERE Type LIKE 'S%'
),
interval_with_p AS (
    SELECT
        s.cat,
        s.s_dates,
        ROW_NUMBER() OVER (ORDER BY s.cat, s.s_dates) AS result_id
    FROM s_list s
    WHERE EXISTS (
        SELECT 1 
        FROM tab t
        WHERE t.cat = s.cat
          AND t.Type LIKE 'P%'
          AND t.dates > s.prev_s_dates
          AND t.dates <= s.s_dates
    )
)
SELECT
    t.dates,
    t.cat AS Cat,
    t.Type,
    i.result_id AS RESULT
FROM tab t
LEFT JOIN interval_with_p i
ON t.cat = i.cat
AND t.dates <= i.s_dates
AND t.dates > (
    SELECT COALESCE(MAX(s_dates), '1900-01-01')
    FROM interval_with_p i2
    WHERE i2.cat = i.cat
      AND i2.s_dates < i.s_dates
)
ORDER BY t.cat, t.dates;

结果校验 #

执行后返回结果和预期完全一致：

• cat=1分组：2019-12-31到2020-01-04的4条记录（S_0、P1、P2、S_1）RESULT=1；2020-01-05到2020-01-07的3条记录（P3、P4、S_2）RESULT=2；2020-01-08及之后的P5、P20无匹配S，RESULT为NULL
• cat=2分组：2020-01-01到2020-01-03的3条记录（P6、P7、S_3）RESULT=3；2020-01-04的P8无匹配S，RESULT为NULL
• cat=3分组：2020-01-05到2020-01-07的3条记录（P9、P10、S_4）RESULT=4；2020-01-08的P11无匹配S，RESULT为NULL

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