按ID获取最新非空起止日期间的中间记录

算力阿峰

2026-7-23

原始数据表

id	start_value	end_value	date	value
1	null	null	05-APR-23	2
1	null	5	09-APR-23	null
1	5	null	15-APR-23	null
1	null	null	16-APR-23	4
1	null	null	16-APR-23	-1
1	null	8	16-APR-23	null
2	1	null	05-APR-23	null
2	null	9	09-APR-23	null
2	9	null	13-APR-23	null
2	null	null	13-APR-23	1
2	null	null	14-APR-23	-5
2	null	null	15-APR-23	-3
2	null	null	16-APR-23	-4
2	null	-1	16-APR-23	null

已获取最新起止值及对应日期的SQL

SELECT id, start_value, end_value, start_date, end_date 
FROM   ( 
    SELECT id, 
           LAST_VALUE(start_value) IGNORE NULLS OVER (PARTITION BY id ORDER BY "DATE") AS start_value, 
           LAST_VALUE(end_value) IGNORE NULLS OVER (PARTITION BY id ORDER BY "DATE") AS end_value, 
           LAST_VALUE(CASE WHEN start_value IS NOT NULL THEN "DATE" END) IGNORE NULLS OVER (PARTITION BY id ORDER BY "DATE") AS start_date, 
           LAST_VALUE(CASE WHEN end_value IS NOT NULL THEN "DATE" END) IGNORE NULLS OVER (PARTITION BY id ORDER BY "DATE") AS end_date, 
           ROW_NUMBER() OVER (PARTITION BY id ORDER BY "DATE" DESC) AS rn 
    FROM   table_name 
) 
WHERE  rn = 1

上述SQL执行结果

id	start_value	end_value	start_date	end_date
1	5	8	15-APR-23	16-APR-23
2	9	-1	13-APR-23	16-APR-23

需求说明

编写SQL获取每个ID最新非空起止日期（即上述结果中的start_date和end_date）之间的所有中间记录，包含起止日期当天的记录。

期望结果

id	start_value	end_value	date	value
1	5	null	15-APR-23	null
1	null	null	15-APR-23	4
1	null	null	16-APR-23	-1
1	null	8	16-APR-23	null
2	9	null	13-APR-23	null
2	null	null	13-APR-23	1
2	null	null	14-APR-23	-5
2	null	null	15-APR-23	-3
2	null	null	16-APR-23	-4
2	null	-1	16-APR-23	null

解决方案SQL

可以通过CTE复用已有的最新起止日期逻辑，再关联原始表筛选符合日期范围的记录：

WITH latest_boundaries AS (
    SELECT id, start_value, end_value, start_date, end_date 
    FROM   ( 
        SELECT id, 
               LAST_VALUE(start_value) IGNORE NULLS OVER (PARTITION BY id ORDER BY "DATE") AS start_value, 
               LAST_VALUE(end_value) IGNORE NULLS OVER (PARTITION BY id ORDER BY "DATE") AS end_value, 
               LAST_VALUE(CASE WHEN start_value IS NOT NULL THEN "DATE" END) IGNORE NULLS OVER (PARTITION BY id ORDER BY "DATE") AS start_date, 
               LAST_VALUE(CASE WHEN end_value IS NOT NULL THEN "DATE" END) IGNORE NULLS OVER (PARTITION BY id ORDER BY "DATE") AS end_date, 
               ROW_NUMBER() OVER (PARTITION BY id ORDER BY "DATE" DESC) AS rn 
        FROM   table_name 
    ) 
    WHERE  rn = 1
)
SELECT t.id, t.start_value, t.end_value, t.date, t.value
FROM table_name t
JOIN latest_boundaries lb ON t.id = lb.id
WHERE t.date BETWEEN lb.start_date AND lb.end_date
ORDER BY t.id, t.date;

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