SQL递归查询实现time字段空值填充的需求与问题求助

针对esz分区内、rank升序的time空值填充需求，由于空值可能连续出现，普通LAG/LEAD函数无法覆盖所有场景，这里用递归CTE实现逐行填充，完全匹配给定规则：

核心思路 #

通过递归CTE从已知非空值出发，按rank顺序逐行填充空值：

• 对于非开头的空值，从前往后用前一行填充值+1，同时确保不超过后续最近非空值
• 对于开头的空值（rank=1且time为空），从后往前用后一行填充值-1，确保符合边界要求
• 用窗口函数提前获取每个行前后最近的非空值作为填充边界，保证符合规则3、4

完整SQL示例（PostgreSQL） #

WITH RECURSIVE filled_time AS (
    -- 初始步骤：获取所有行，同时标记每个行前后最近的非空time
    SELECT 
        esz,
        rank,
        time,
        -- 当前行之后最近的非空time
        FIRST_VALUE(time) OVER (PARTITION BY esz ORDER BY rank DESC) AS next_non_null_time,
        -- 当前行之前最近的非空time
        LAST_VALUE(time) OVER (PARTITION BY esz ORDER BY rank ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS prev_non_null_time
    FROM original_table
    UNION ALL
    -- 递归填充：逐行处理空值
    SELECT 
        f.esz,
        f.rank,
        CASE
            -- 规则2：rank=1且time为空，取后值-1，差值不足则取较小值
            WHEN f.rank = 1 AND f.time IS NULL THEN 
                LEAST(f.next_non_null_time - 1, f.next_non_null_time)
            -- 规则1：非开头空值，取前值+1，不超过后值（规则4），差值不足取较小值（规则3）
            WHEN f.time IS NULL THEN
                LEAST(
                    (SELECT time FROM filled_time WHERE esz = f.esz AND rank = f.rank - 1) + 1,
                    f.next_non_null_time
                )
            ELSE f.time
        END AS time,
        f.next_non_null_time,
        f.prev_non_null_time
    FROM filled_time f
    WHERE f.time IS NULL
)
-- 去重并取最终填充结果
SELECT DISTINCT ON (esz, rank)
    esz,
    rank,
    time
FROM filled_time
ORDER BY esz, rank;

MySQL适配版本 #

MySQL不支持DISTINCT ON，改用ROW_NUMBER()筛选最终结果：

WITH RECURSIVE filled_time AS (
    SELECT 
        esz,
        rank,
        time,
        FIRST_VALUE(time) OVER (PARTITION BY esz ORDER BY rank DESC) AS next_non_null_time,
        LAST_VALUE(time) OVER (PARTITION BY esz ORDER BY rank ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS prev_non_null_time
    FROM original_table
    UNION ALL
    SELECT 
        f.esz,
        f.rank,
        CASE
            WHEN f.rank = 1 AND f.time IS NULL THEN 
                LEAST(f.next_non_null_time - 1, f.next_non_null_time)
            WHEN f.time IS NULL THEN
                LEAST(
                    (SELECT time FROM filled_time WHERE esz = f.esz AND rank = f.rank - 1) + 1,
                    f.next_non_null_time
                )
            ELSE f.time
        END AS time,
        f.next_non_null_time,
        f.prev_non_null_time
    FROM filled_time f
    WHERE f.time IS NULL
)
SELECT esz, rank, time
FROM (
    SELECT 
        esz, rank, time,
        ROW_NUMBER() OVER (PARTITION BY esz, rank ORDER BY (CASE WHEN time IS NOT NULL THEN 1 ELSE 0 END) DESC) AS rn
    FROM filled_time
) t
WHERE rn = 1
ORDER BY esz, rank;

规则匹配说明 #

1. 规则1：递归时优先取前一行填充值+1，通过LEAST确保不超过后续非空值，符合"尽可能接近前值"的要求
2. 规则2：rank=1的空值取后值-1，差值不足时取较小值（如后值为5则填充4，后值为0则填充0）
3. 规则3：LEAST函数自动处理差值不足的情况，取前后值中的较小值
4. 规则4：通过LEAST限制填充值不大于后值，前值+1本身保证不小于前值；开头空值的LEAST保证不大于后值，且因分组至少有一个非空值，不会出现无边界的情况

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