如何按host_id,binary_id,service分组后识别值不同的列并输出结果

问题描述 #

现有数据集（示例）：

host_id binary_id service binary_date region binary_path ......
1       2         hello   05-06-2012  US     /bin/usr
1       2         hello   08-09-2022  US     /bin/usr
2       3         another 08-09-2026  UK     /bin/java

需按host_id,binary_id,service分组，将组内取值完全一致的列置为null，仅保留组内存在差异列的原始值。预期结果如下：

host_id binary_id service binary_date region binary_path ......
1       2         hello   05-06-2012  null   null
1       2         hello   08-09-2022  null   null

解决方案（SQL实现） #

核心逻辑是通过窗口函数判断每列在分组内的取值唯一性，以此决定保留原始值还是置为null：

SELECT
    host_id,
    binary_id,
    service,
    -- 处理binary_date：组内值不同则保留，否则置空
    CASE WHEN COUNT(DISTINCT binary_date) OVER (PARTITION BY host_id, binary_id, service) > 1 THEN binary_date ELSE NULL END AS binary_date,
    -- 处理region列
    CASE WHEN COUNT(DISTINCT region) OVER (PARTITION BY host_id, binary_id, service) > 1 THEN region ELSE NULL END AS region,
    -- 处理binary_path列
    CASE WHEN COUNT(DISTINCT binary_path) OVER (PARTITION BY host_id, binary_id, service) > 1 THEN binary_path ELSE NULL END AS binary_path,
    -- 其余40+列按上述逻辑依次添加
    ......
FROM your_table_name;

补充说明 #

• 针对50列的场景，只需将所有非分组列套用上述CASE逻辑即可
• 如果列包含NULL值，COUNT(DISTINCT)会自动忽略空值；若需要把NULL也视为一种取值判断差异，可改用COUNT(DISTINCT COALESCE(列名, '专属标记值'))来统一处理空值
• 该方法适用于支持窗口函数的SQL数据库（如MySQL 8.0+、PostgreSQL、SQL Server等）

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