DB2环境下分组数据时消除Null值（避免自连接）

针对你提供的数据集转换需求，在DB2中可以通过**窗口函数+CTE（公共表表达式）**的方式实现，完全避免自连接，适合大数据量场景。

假设原表名为 your_table，执行以下SQL： #

WITH grouped_metadata AS (
    SELECT 
        Col1,
        -- 提取当前Col1分组下唯一非Null的Col2值
        MAX(Col2) OVER (PARTITION BY Col1) AS fixed_col2,
        -- 提取当前Col1分组下唯一非Null的Col4值
        MAX(Col4) OVER (PARTITION BY Col1) AS fixed_col4,
        Col3
    FROM your_table
),
distinct_col3 AS (
    -- 提取每个Col1分组下所有非Null的Col3值（去重）
    SELECT DISTINCT Col1, Col3
    FROM grouped_metadata
    WHERE Col3 IS NOT NULL
),
base_records AS (
    -- 提取每个Col1分组对应的Col2/Col4基准值（去重）
    SELECT DISTINCT Col1, fixed_col2, fixed_col4
    FROM grouped_metadata
)
-- 生成Col3有值的目标行
SELECT 
    br.Col1,
    br.fixed_col2,
    dc.Col3,
    br.fixed_col4
FROM base_records br
JOIN distinct_col3 dc ON br.Col1 = dc.Col1
UNION ALL
-- 生成Col3无值但Col2/Col4有值的目标行
SELECT 
    Col1,
    fixed_col2,
    NULL AS Col3,
    fixed_col4
FROM base_records br
WHERE NOT EXISTS (SELECT 1 FROM distinct_col3 dc WHERE dc.Col1 = br.Col1)
AND (fixed_col2 IS NOT NULL OR fixed_col4 IS NOT NULL)
UNION ALL
-- 生成所有列均为Null的目标行
SELECT 
    Col1,
    NULL AS Col2,
    NULL AS Col3,
    NULL AS Col4
FROM your_table
WHERE Col1 NOT IN (SELECT Col1 FROM base_records WHERE fixed_col2 IS NOT NULL OR fixed_col4 IS NOT NULL)
AND Col1 NOT IN (SELECT Col1 FROM distinct_col3)
ORDER BY Col1, Col3;

逻辑说明： #

1. grouped_metadata：通过MAX() OVER (PARTITION BY Col1)窗口函数，为每一行计算出当前Col1分组下Col2/Col4的非Null有效值（同一Col1分组下Col2/Col4仅存在一个非Null值时，MAX会直接提取该值）。
2. distinct_col3：筛选并去重每个Col1分组下的非Null Col3值，作为后续生成多行的依据。
3. base_records：提取每个Col1分组对应的Col2/Col4基准值，避免重复计算。
4. 三个SELECT通过UNION ALL组合：
   • 第一个SELECT将基准行与Col3有效值关联，生成Col3有值的多行（如示例中PK_Col1的三行）；
   • 第二个SELECT处理Col3无有效值但Col2/Col4有值的场景（如示例中PK_Col3的行）；
   • 第三个SELECT保留所有列均为Null的原始行（如示例中PK_Col2的行）。

性能优势： #

全程使用窗口函数和简单子查询，未使用自连接，DB2优化器可以高效利用Col1的索引（如果存在）进行分组计算，适合数据量极大的表。

注意事项： #

• 如果同一Col1分组下Col2/Col4存在多个非Null值，MAX()会取字典序最大的值，若需要其他逻辑可替换为MIN()或自定义聚合函数；
• UNION ALL比UNION性能更高，因为不会做去重校验，这里的三个分支结果无重叠，适合使用。

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