基于AssetName动态填充AssetCategoryName空值 - PySpark

核心思路 #

利用分组聚合或窗口函数，为每个AssetName提取其对应的唯一非空AssetCategoryName，再将空值替换为该值。以下是主流数据库的实现方案：

SQL Server 实现 #

使用窗口函数MAX()（因每个AssetName对应唯一非空分类，MAX会精准取到该值）：

SELECT 
    AssetName,
    COALESCE(AssetCategoryName, MAX(AssetCategoryName) OVER (PARTITION BY AssetName)) AS AssetCategoryName
FROM YourTableName;

MySQL 实现 #

8.0+ 版本（支持窗口函数）

SELECT 
    AssetName,
    COALESCE(AssetCategoryName, MAX(AssetCategoryName) OVER (PARTITION BY AssetName)) AS AssetCategoryName
FROM YourTableName;

5.x 版本（无窗口函数）

用关联子查询预存每个AssetName的有效分类：

SELECT 
    t1.AssetName,
    COALESCE(t1.AssetCategoryName, t2.AssetCategoryName) AS AssetCategoryName
FROM YourTableName t1
LEFT JOIN (
    SELECT AssetName, MAX(AssetCategoryName) AS AssetCategoryName
    FROM YourTableName
    WHERE AssetCategoryName IS NOT NULL
    GROUP BY AssetName
) t2 ON t1.AssetName = t2.AssetName;

PostgreSQL 实现 #

方案1：MAX窗口函数

SELECT 
    AssetName,
    COALESCE(AssetCategoryName, MAX(AssetCategoryName) OVER (PARTITION BY AssetName)) AS AssetCategoryName
FROM YourTableName;

方案2：FIRST_VALUE窗口函数

强制优先取非空值：

SELECT 
    AssetName,
    COALESCE(AssetCategoryName, FIRST_VALUE(AssetCategoryName) OVER (
        PARTITION BY AssetName 
        ORDER BY AssetCategoryName IS NULL
    )) AS AssetCategoryName
FROM YourTableName;

前置验证 #

先排查是否存在无有效分类的AssetName（这类空值无法通过动态填充解决）：

SELECT AssetName
FROM YourTableName
GROUP BY AssetName
HAVING MAX(AssetCategoryName) IS NULL;

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