MySQL中填充record_id间隙并回填最近可用值的技术问询

需要补全record_id的间隙，缺失值用最近的可用值填充；若无可用值，则使用首个可用值。

预期结果 #

尝试的代码 #

第一段代码（查找缺失序列） #

with table_1 as ( 
    select 'A' as category ,1 as record_id ,0.01 as value 
    union all select 'A', 2, 0.23 
    union all select 'A', 5, 0.15 
    union all select 'A', 6, 0.20 
    union all select 'A', 7, 0.08 
    union all select 'B', 2, 1.00 
    union all select 'B', 3, 0.75 
    union all select 'B', 6, 0.93 
    union all select 'B', 7, 0.87 
),seq_num as( 
    select 1 as record_id 
    union all select 2 
    union all select 3 
    union all select 4 
    union all select 5 
    union all select 6 
    union all select 7
),missing_seq as ( 
    select t1.record_id from seq_num t1 where t1.record_id not in (select t2.record_id from table_1 t2 where t2.category = 'A') 
    union all 
    select t1.record_id from seq_num t1 where t1.record_id not in (select t2.record_id from table_1 t2 where t2.category = 'B') 
) 
select * from missing_seq

第二段代码（递归CTE尝试） #

with table_1 as ( 
    select 'A' as category ,1 as record_id ,0.01 as value 
    union all select 'A', 2, 0.23 
    union all select 'A', 5, 0.15 
    union all select 'A', 6, 0.20 
    union all select 'A', 7, 0.08 
    union all select 'B', 2, 1.00 
    union all select 'B', 3, 0.75 
    union all select 'B', 6, 0.93 
    union all select 'B', 7, 0.87 
), data as ( 
    select t.*, lead(record_id) over(partition by category order by record_id) lead_record_id 
    from table_1 t 
    union all 
    select category, 1, null, min(record_id) from table_1 group by category having min(record_id) > 1 
), rcte as ( 
    select category, record_id, value, lead_record_id from data 
    union all 
    select category, record_id + 1, value, lead_record_id from data where record_id + 1 < lead_record_id 
) 
select category, record_id, value from rcte order by category, record_id;

正确实现方案 #

核心思路是先为每个category生成完整的record_id序列，再通过窗口函数向前填充最近的可用值，同时处理开头缺失的情况。

WITH table_1 AS (
    SELECT 'A' AS category, 1 AS record_id, 0.01 AS value
    UNION ALL SELECT 'A', 2, 0.23
    UNION ALL SELECT 'A', 5, 0.15
    UNION ALL SELECT 'A', 6, 0.20
    UNION ALL SELECT 'A', 7, 0.08
    UNION ALL SELECT 'B', 2, 1.00
    UNION ALL SELECT 'B', 3, 0.75
    UNION ALL SELECT 'B', 6, 0.93
    UNION ALL SELECT 'B', 7, 0.87
),
-- 生成每个分类的完整record_id序列
full_seq AS (
    SELECT 
        c.category,
        s.record_id
    FROM (SELECT DISTINCT category FROM table_1) c
    CROSS JOIN (
        SELECT record_id FROM GENERATE_SERIES(1, (SELECT MAX(record_id) FROM table_1)) AS s(record_id)
    ) s
),
-- 关联原始数据，保留缺失值的NULL标记
combined AS (
    SELECT 
        f.category,
        f.record_id,
        t.value
    FROM full_seq f
    LEFT JOIN table_1 t ON f.category = t.category AND f.record_id = t.record_id
),
-- 向前填充最近可用值，自动处理开头缺失场景
filled AS (
    SELECT 
        category,
        record_id,
        LAST_VALUE(value IGNORE NULLS) OVER (
            PARTITION BY category 
            ORDER BY record_id 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS value
    FROM combined
)
SELECT * FROM filled ORDER BY category, record_id;

关键说明 #

• full_seq通过笛卡尔积生成每个分类的完整record_id序列，确保没有间隙；
• LAST_VALUE(value IGNORE NULLS)窗口函数会自动向前填充最近的非空值，对于开头无可用值的情况（如B类的record_id=1），会取该分类第一个出现的非空值，符合需求。

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