如何按branch_no和crncy_id分组，用后续行值更新空值并清理数据

原始数据 #

branch_no   crncy_id    Tax_1_Account   Tax_1_Balance       Tax_2_Account   Tax_2_Balance
1               3       01-01-211515    0.000000            NULL            NULL
1               3       01-01-230305    726910532.850000    NULL            NULL
1               3       NULL            NULL                01-01-240130    2019759.840000
1               3       01-01-240135    135117.090000       NULL            NULL
1               3       NULL            NULL                01-01-510170    2140859.810000
1               3       01-01-610501    0.000000            NULL            NULL
1               3       01-01-610520    37285.000000        01-01-610520    37285.000000
2               3       01-02-211515    0.000000            NULL            NULL
2               3       01-02-230305    14483229.720000     NULL            NULL
2               3       NULL            NULL                01-02-240130    85462.290000
2               3       01-02-240135    0.000000            NULL            NULL
2               3       NULL            NULL                01-02-510170    0.000000
2               3       01-02-610501    0.000000            NULL            NULL
2               3       01-02-610520    284409497.000000    01-02-610520    284409497.3             
3               3       01-03-211515    0.000000            01-03-211515    0.000000
3               3       01-03-230305    0.000000            01-03-230305    0.000000
3               3       NULL            NULL                01-03-240130    0.000000
3               3       01-03-240135    0.000000            01-03-240135    0.000000
3               3       NULL            NULL                01-03-510170    0.000000
3               3       01-03-610501    0.000000            01-03-610501    0.000000 
3               3       01-03-610520    710.000000          01-03-610520    710.000000

需求 #

• 在相同branch_no和crncy_id的分组内，将Tax_1_Account、Tax_1_Balance、Tax_2_Account、Tax_2_Balance中的NULL值用下一行的对应非NULL值填充
• 分组内最后一行的对应列NULL值保持不变
• 若某行的上述四个字段全为NULL，则删除该行

预期结果 #

branch_no   crncy_id    Tax_1_Account   Tax_1_Balance       Tax_2_Account   Tax_2_Balance
1               3       01-01-211515    0.000000            01-01-240130    2019759.840000
1               3       01-01-230305    726910532.850000    01-01-510170    2140859.810000
1               3       01-01-240135    135117.090000       01-01-610520    37285.000000
1               3       01-01-610501    0.000000            NULL            NULL             
1               3       01-01-610520    37285.000000        NULL            NULL    
2               3       01-02-211515    0.000000            01-02-240130    85462.290000
2               3       01-02-230305    14483229.720000     01-02-510170    0.000000
2               3       01-02-240135    0.000000            01-02-610520    284409497.000000    
2               3       01-02-610501    0.000000            NULL            NULL
2               3       01-02-610520    284409497.000000    NULL            NULL
3               3       01-03-211515    0.000000            01-03-211515    0.000000
3               3       01-03-230305    0.000000            01-03-230305    0.000000
3               3       01-03-240135    0.000000            01-03-240130    0.000000
3               3       01-03-610501    0.000000            01-03-240135    0.000000
3               3       01-03-610520    710.000000          01-03-510170    0.000000
3               3       NULL            NULL                01-03-610501    0.000000 
3               3       NULL            NULL                01-03-610520    710.000000

解决方案（SQL实现） #

以下以PostgreSQL为例，利用窗口函数LAST_VALUE结合IGNORE NULLS实现向下填充逻辑：

WITH ranked_data AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY branch_no, crncy_id ORDER BY (SELECT NULL)) AS row_num
    FROM your_table_name
),
filled_data AS (
    SELECT 
        branch_no,
        crncy_id,
        -- 填充Tax_1_Account：取当前行及之后的第一个非NULL值
        COALESCE(
            Tax_1_Account,
            LAST_VALUE(Tax_1_Account) OVER (
                PARTITION BY branch_no, crncy_id 
                ORDER BY row_num 
                ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
                IGNORE NULLS
            )
        ) AS Tax_1_Account,
        -- 填充Tax_1_Balance
        COALESCE(
            Tax_1_Balance,
            LAST_VALUE(Tax_1_Balance) OVER (
                PARTITION BY branch_no, crncy_id 
                ORDER BY row_num 
                ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
                IGNORE NULLS
            )
        ) AS Tax_1_Balance,
        -- 填充Tax_2_Account
        COALESCE(
            Tax_2_Account,
            LAST_VALUE(Tax_2_Account) OVER (
                PARTITION BY branch_no, crncy_id 
                ORDER BY row_num 
                ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
                IGNORE NULLS
            )
        ) AS Tax_2_Account,
        -- 填充Tax_2_Balance
        COALESCE(
            Tax_2_Balance,
            LAST_VALUE(Tax_2_Balance) OVER (
                PARTITION BY branch_no, crncy_id 
                ORDER BY row_num 
                ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
                IGNORE NULLS
            )
        ) AS Tax_2_Balance,
        row_num,
        COUNT(*) OVER (PARTITION BY branch_no, crncy_id) AS total_rows
    FROM ranked_data
),
cleaned_data AS (
    SELECT 
        branch_no,
        crncy_id,
        -- 最后一行保留原始NULL值
        CASE WHEN row_num = total_rows THEN Tax_1_Account ELSE COALESCE(Tax_1_Account, NULL) END AS Tax_1_Account,
        CASE WHEN row_num = total_rows THEN Tax_1_Balance ELSE COALESCE(Tax_1_Balance, NULL) END AS Tax_1_Balance,
        CASE WHEN row_num = total_rows THEN Tax_2_Account ELSE COALESCE(Tax_2_Account, NULL) END AS Tax_2_Account,
        CASE WHEN row_num = total_rows THEN Tax_2_Balance ELSE COALESCE(Tax_2_Balance, NULL) END AS Tax_2_Balance
    FROM filled_data
    -- 过滤四个字段全为NULL的行
    WHERE NOT (Tax_1_Account IS NULL AND Tax_1_Balance IS NULL AND Tax_2_Account IS NULL AND Tax_2_Balance IS NULL)
)
SELECT branch_no, crncy_id, Tax_1_Account, Tax_1_Balance, Tax_2_Account, Tax_2_Balance
FROM cleaned_data
ORDER BY branch_no, crncy_id, row_num;

逻辑说明 #

1. ranked_data：给每个分组内的行添加序号，保证处理顺序稳定
2. filled_data：通过LAST_VALUE(...) IGNORE NULLS获取当前行之后的第一个非NULL值，用COALESCE替换当前行的NULL
3. cleaned_data：保留分组最后一行的原始NULL值，同时过滤掉四个字段全为空的行
4. 最终结果按分组和行号排序，与预期输出一致

如果使用不支持IGNORE NULLS的数据库（如MySQL），可以通过子查询或自定义函数实现类似逻辑，核心是定位每个NULL值对应的后续第一个非NULL值。

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