PostgreSQL中如何为无匹配数据的Union查询补全0值行

问题说明 #

现有查询在处理segment_nr=1这类无cond3数据的行时，会缺失"aaa" 1 "cond3" 0 0格式的记录，需要调整查询逻辑补全此类缺失行。

修改后的查询代码 #

-- 先筛选目标part_id对应的所有segment_nr，避免重复子查询
WITH target_segments AS (
    SELECT segment_nr, part_id FROM table1 WHERE part_id = 'aaa'
),
-- 获取cond1、cond2的原始数据，保留存在的行
cond1_cond2_data AS (
    SELECT
        ts.part_id,
        ts.segment_nr,
        t2.ident,
        t2.val AS val,
        1 AS quantity
    FROM target_segments ts
    JOIN table2 t2 ON ts.segment_nr = t2.segment_nr
    WHERE t2.ident IN ('cond1', 'cond2')
),
-- 生成所有需要的cond3、cond4行框架（每个segment_nr都对应这两个ident）
cond3_cond4_frames AS (
    SELECT
        ts.part_id,
        ts.segment_nr,
        ident
    FROM target_segments ts
    CROSS JOIN (VALUES ('cond3'), ('cond4')) AS idents(ident)
),
-- 计算cond3、cond4的统计数据
cond3_cond4_stats AS (
    SELECT
        ts.part_id,
        t2.segment_nr,
        t2.ident,
        MAX(ABS(t2.val)) AS max_abs_val,
        COUNT(*) AS quantity
    FROM target_segments ts
    JOIN table2 t2 ON ts.segment_nr = t2.segment_nr
    WHERE t2.ident IN ('cond3', 'cond4')
    GROUP BY ts.part_id, t2.segment_nr, t2.ident
)
-- 合并数据，补全缺失的统计行
SELECT * FROM cond1_cond2_data
UNION ALL
SELECT
    ccf.part_id,
    ccf.segment_nr,
    ccf.ident,
    COALESCE(ccs.max_abs_val, 0) AS val,
    COALESCE(ccs.quantity, 0) AS quantity
FROM cond3_cond4_frames ccf
LEFT JOIN cond3_cond4_stats ccs 
    ON ccf.segment_nr = ccs.segment_nr AND ccf.ident = ccs.ident
ORDER BY segment_nr, ident;

核心逻辑解析 #

1. target_segments：一次性筛选出part_id='aaa'对应的所有segment_nr，减少重复查询，提升效率。
2. cond1_cond2_data：保留原需求中cond1、cond2的原始数据，仅存在对应记录时才返回行。
3. cond3_cond4_frames：通过交叉连接生成每个目标segment_nr与cond3、cond4的组合，确保每个segment_nr都有这两个ident的行框架。
4. cond3_cond4_stats：计算cond3、cond4的统计值（最大绝对值、记录数量）。
5. 最终合并：用左连接将行框架与统计数据关联，通过COALESCE函数把缺失的统计值替换为0，补全缺失行后与cond1_cond2_data合并。

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