SQL查询：获取表中指定block行的非空邻近日期值

现有如下测试数据：

select * into temp3
from (values
    ('306B', 'Erin, Fromans',1,'2024-06-03 00:00:00.000', NULL),
    ('306B', 'Erin, Fromans',2,NULL, '2024-07-12 00:00:00.000'),
    ('306B', 'Erin, Fromans',4,NULL, NULL),
    ('306B', 'Erin, Fromans',5,NULL, NULL),
    ('306B', 'Erin, Fromans',6,NULL, '2024-11-15 00:00:00.000'),
    ('0208A','Guerrazzi, Kiana',4,'2024-08-26 00:00:00.000', NULL),
    ('0208A','Guerrazzi, Kiana',5,NULL, NULL),
    ('0208A','Guerrazzi, Kiana',6,NULL, '2024-11-15 00:00:00.000'),
    ('108B', 'Obeid, Tarek',2,'2024-10-07 00:00:00.000',NULL),
    ('108B', 'Obeid, Tarek',3,NULL, '2024-11-15 00:00:00.000'),
    ('416A', 'Castellano, Isabella',1,'2024-06-03 00:00:00.000', NULL),
    ('416A', 'Castellano, Isabella',2,NULL, '2024-07-12 00:00:00.000'),
    ('416A', 'Castellano, Isabella',4,'2024-08-26 00:00:00.000', NULL),
    ('416A', 'Castellano, Isabella',5,NULL, NULL),
    ('416A', 'Castellano, Isabella',6,NULL, '2024-11-15 00:00:00.000')
) as c(apt,name,block,indate,outdate)

尝试过以下代码，但无法正确获取indate和outdate：

select t.apt,t.name,t.block,
    case when t.indate is not null then t.indate else (
             select min(t1.indate) 
             from temp3 t1 
             where t1.apt = t.apt and t1.name=t.name 
        ) end indate,
    case when t.outdate is not null then t.outdate else (
            select max(t1.outdate)
            from temp3 t1 
            where t1.apt = t.apt and t1.name=t.name
        ) end outdate
from temp3 t
where block=5

具体需求：

• 当indate为null时，需查找同apt、name下block值小于当前行的记录中，最近的（最大的block值）非空indate
• 当outdate为null时，需查找同apt、name下block值大于当前行的记录中，最近的（最小的block值）非空outdate

期望结果：

以下两种方案均可实现需求，可根据场景选择：

方案一：关联子查询（精准匹配需求逻辑） #

SELECT 
    t.apt,
    t.name,
    t.block,
    -- 获取当前block之前最近的非空indate
    COALESCE(t.indate, (
        SELECT TOP 1 t1.indate
        FROM temp3 t1
        WHERE t1.apt = t.apt 
          AND t1.name = t.name 
          AND t1.block < t.block 
          AND t1.indate IS NOT NULL
        ORDER BY t1.block DESC
    )) AS indate,
    -- 获取当前block之后最近的非空outdate
    COALESCE(t.outdate, (
        SELECT TOP 1 t1.outdate
        FROM temp3 t1
        WHERE t1.apt = t.apt 
          AND t1.name = t.name 
          AND t1.block > t.block 
          AND t1.outdate IS NOT NULL
        ORDER BY t1.block ASC
    )) AS outdate
FROM temp3 t
WHERE t.block = 5;

逻辑说明： #

• 针对indate：子查询筛选同用户、block更小且indate非空的记录，按block降序取第一条，即最近的前一个非空值
• 针对outdate：子查询筛选同用户、block更大且outdate非空的记录，按block升序取第一条，即最近的后一个非空值
• COALESCE函数优先使用当前行的非空值，为空时再取子查询结果

方案二：窗口函数（高效批量处理） #

如果需要处理所有block的缺失值，窗口函数的方式更高效：

WITH ranked_data AS (
    SELECT 
        apt,
        name,
        block,
        indate,
        outdate,
        -- 向前填充最近的非空indate
        LAST_VALUE(indate) OVER (
            PARTITION BY apt, name 
            ORDER BY block 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
            ) AS filled_indate,
        -- 向后填充最近的非空outdate
        FIRST_VALUE(outdate) OVER (
            PARTITION BY apt, name 
            ORDER BY block 
            ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
            ) AS filled_outdate
    FROM temp3
)
SELECT 
    apt,
    name,
    block,
    -- 过滤填充过程中残留的null值
    MAX(filled_indate) OVER (
        PARTITION BY apt, name, block
        ) AS indate,
    MAX(filled_outdate) OVER (
        PARTITION BY apt, name, block
        ) AS outdate
FROM ranked_data
WHERE block = 5;

逻辑说明： #

• 用LAST_VALUE按block升序，在用户分组内向前传递最近的非空indate
• 用FIRST_VALUE按block升序，在用户分组内向后传递最近的非空outdate
• 最后用MAX过滤掉填充过程中可能残留的null值（仅针对当前block行）

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