Oracle按ID分组查询最新非空start_value与end_value及对应日期

现有数据表结构及数据 #

id | start_value | end_value | date
1    null            null      05-APR-23
1    null            5         09-APR-23
1    5               null      15-APR-23
1    null            8         16-APR-23
2    1               null      05-APR-23
2    null            9         09-APR-23
2    9               null      13-APR-23
2    null            -1        16-APR-23

期望输出结果 #

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

解决方案 #

可以通过窗口函数分别筛选每个ID下最新的非空start_value、end_value及对应日期，再关联合并结果：

WITH start_latest AS (
    SELECT 
        id,
        start_value,
        date AS start_date,
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY date DESC) rn
    FROM your_table_name
    WHERE start_value IS NOT NULL
),
end_latest AS (
    SELECT 
        id,
        end_value,
        date AS end_date,
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY date DESC) rn
    FROM your_table_name
    WHERE end_value IS NOT NULL
)
SELECT 
    s.id,
    s.start_value,
    e.end_value,
    s.start_date,
    e.end_date
FROM start_latest s
JOIN end_latest e ON s.id = e.id
WHERE s.rn = 1 AND e.rn = 1;

说明 #

• 两个CTE分别筛选每个ID下非空的start_value、end_value记录，按日期倒序排序后取第一条（最新数据）
• 通过ID关联两个CTE的结果，得到每个ID对应的最新非空值及对应日期
• 需将语句中的your_table_name替换为实际数据表名称

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