按ID分组排序，基于Date1/Date2生成new_date的SQL查询需求

样本数据 #

创建临时表并插入测试数据：

drop table if exists #data
create table #data
(
    Id char(3),
    Date1 date,
    Date2 date
)
insert into #data (Id, Date1, Date2)
values
    ('100', '2017-02-24', null),
    ('100', '2017-03-06', null),
    ('100', '2017-03-21', '2017-04-04'),
    ('100', '2017-06-15', null),
    ('100', '2017-06-16', '2017-06-16'),
    ('100', '2017-11-17', null),
    ('100', '2018-02-02', null),
    ('200', '2017-06-06', '2017-06-11'),
    ('200', '2018-02-02', null),
    ('200', '2018-02-08', null),
    ('200', '2018-02-09', null),
    ('200', '2018-02-14', '2018-02-15'),
    ('200', '2018-03-03', '2018-03-07'),
    ('200', '2018-06-07', '2018-06-14')

需求规则 #

按Id分组、Date1升序排序，基于Date1和Date2生成new_date列，规则如下：

• 若当前Date2的所有前序Date2均为NULL，取小于该Date2的最小Date1；
• 取大于前一个非NULLDate2且对应Date2为NULL的最小Date1；
• 若前一个非NULLDate2与当前Date2之间无Date2为NULL的记录，则取该区间内的Date1。

实现SQL #

WITH grouped_data AS (
    SELECT 
        Id,
        Date1,
        Date2,
        -- 按ID分组，为每个非NULL Date2的记录划分段
        SUM(CASE WHEN Date2 IS NOT NULL THEN 1 ELSE 0 END) OVER (PARTITION BY Id ORDER BY Date1) AS segment_id,
        -- 获取当前位置之前最近的非NULL Date2值
        MAX(Date2) OVER (PARTITION BY Id ORDER BY Date1 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS prev_non_null_date2
    FROM #data
),
segment_summary AS (
    SELECT 
        Id,
        segment_id,
        prev_non_null_date2,
        -- 当前段内的最小Date1（对应规则1、3）
        MIN(Date1) AS min_segment_date1,
        -- 下一段中Date2为NULL的最小Date1（对应规则2）
        (SELECT MIN(Date1) 
         FROM grouped_data gd_next
         WHERE gd_next.Id = gd.Id 
           AND gd_next.segment_id = gd.segment_id + 1 
           AND gd_next.Date2 IS NULL) AS next_segment_min_null_date1
    FROM grouped_data gd
    WHERE gd.Date2 IS NOT NULL
    GROUP BY Id, segment_id, prev_non_null_date2
)
SELECT 
    gd.Id,
    gd.Date1,
    gd.Date2,
    CASE
        -- 规则1：当前是第一个非NULL Date2段，取小于该Date2的最小Date1
        WHEN ss.segment_id = 1 THEN ss.min_segment_date1
        -- 规则2：下一段存在Date2为NULL的记录，取其中最小的Date1
        WHEN ss.next_segment_min_null_date1 IS NOT NULL THEN ss.next_segment_min_null_date1
        -- 规则3：前后非NULL Date2之间无NULL Date2记录，取当前段的最小Date1
        ELSE ss.min_segment_date1
    END AS new_date
FROM grouped_data gd
LEFT JOIN segment_summary ss 
    ON gd.Id = ss.Id 
    AND (gd.Date2 IS NOT NULL OR (gd.Date2 IS NULL AND ss.segment_id = (SELECT MAX(segment_id) FROM grouped_data gd_inner WHERE gd_inner.Id = gd.Id AND gd_inner.Date1 <= gd.Date1)))
ORDER BY gd.Id, gd.Date1;

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