基于SQL/Spark SQL按分区及列值更新Days列的技术需求

核心逻辑 #

• 按material和machinenumber分区，按year、month、date排序
• 给非空val的行打分组标记，让连续的空值划归到最近的上一个非空行的分组中
• 非空val对应的Days直接设为0；空值在所属分组内按日期排序生成递增序号（从1开始）

Spark SQL 实现 #

WITH tagged_data AS (
    SELECT 
        material,
        machinenumber,
        year,
        month,
        date,
        val,
        -- 生成分组ID：非空val行累加1，空值继承上一个非空行的ID
        SUM(CASE WHEN val IS NOT NULL THEN 1 ELSE 0 END) 
            OVER (PARTITION BY material, machinenumber ORDER BY year, month, date) AS group_id
    FROM your_table
)
SELECT 
    material,
    machinenumber,
    year,
    month,
    date,
    val,
    CASE 
        WHEN val IS NOT NULL THEN 0
        ELSE ROW_NUMBER() OVER (PARTITION BY material, machinenumber, group_id ORDER BY year, month, date)
    END AS Days
FROM tagged_data
ORDER BY material, machinenumber, year, month, date;

代码说明 #

1. tagged_data阶段：通过窗口累加函数，给每个非空val的行分配递增的group_id，连续空值会共享同一个group_id，实现空值分组
2. 最终查询阶段：非空val直接返回0；空值在material+machinenumber+group_id的子分区内，按日期排序生成行号，实现连续递增计数

标准SQL（Hive/PostgreSQL等）实现 #

WITH tagged_data AS (
    SELECT 
        material,
        machinenumber,
        year,
        month,
        date,
        val,
        -- 用COUNT生成分组ID，效果与SUM一致
        COUNT(CASE WHEN val IS NOT NULL THEN 1 END) 
            OVER (PARTITION BY material, machinenumber ORDER BY year, month, date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS group_id
    FROM your_table
)
SELECT 
    material,
    machinenumber,
    year,
    month,
    date,
    val,
    CASE 
        WHEN val IS NOT NULL THEN 0
        ELSE ROW_NUMBER() OVER (PARTITION BY material, machinenumber, group_id ORDER BY year, month, date)
    END AS Days
FROM tagged_data
ORDER BY material, machinenumber, year, month, date;

注意事项 #

• 确保year、month、date的排序逻辑正确，若有单独的日期字段（如dt），直接用该字段排序更简洁
• 避免按val分区，这是之前结果不符合预期的核心原因，必须以material和machinenumber作为分区键

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