能否在PARTITION BY语句中嵌套条件实现SQL地域字段缺省补全

解决方案 #

你可以通过分层判断+窗口函数实现需求，修正后的SQL语句如下：

SELECT 
    "row",
    acct_num,
    serial_number,
    site_address,
    CASE 
        -- 优先级1：serial_number非空时，取同serial_number下的有效地域值
        WHEN serial_number != 'NULL' THEN MAX(
            CASE WHEN territory_name NOT IN ('N/A', 'No Territory', 'NULL') 
            THEN territory_name END
        ) OVER (PARTITION BY serial_number)
        -- 优先级2：serial_number为空时，取同acct_num下最近的有效地域值
        ELSE LAST_VALUE(
            CASE WHEN territory_name NOT IN ('N/A', 'No Territory', 'NULL') 
            THEN territory_name END
        ) OVER (
            PARTITION BY acct_num 
            ORDER BY "row" 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        )
    END AS imputed_place
FROM t
ORDER BY "row";

说明 #

1. 你之前的PARTITION BY语法错误是因为缺少了CASE关键字，正确的条件分区写法是PARTITION BY CASE WHEN 条件 THEN 返回值 ELSE 返回值 END
2. 上述逻辑里的有效地域判定规则为排除N/A、No Territory、NULL三种无效值，可根据实际业务调整
3. 示例中所有空值均存储为字符串'NULL'，如果你的实际业务使用SQL标准NULL，需要把判断条件从!= 'NULL'改为IS NOT NULL
4. 如果serial_number为空时需要取同acct_num下所有有效地域的最大值而非最近一条的值，把LAST_VALUE替换为MAX即可

执行上述语句后第12行的imputed_place会返回预期的Northeast。

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