数据补全SQL问题：用Partition By或左连接实现窗口数据补位

核心思路 #

先构建需要覆盖的目标BTT_id全集，将其与业务提交ID（Bsub_id）关联生成所有必要的占位项，再结合真实事务与默认事务数据，通过窗口函数优先选择真实事务，缺失时自动补全默认项。

具体实现代码 #

-- 1. 构建目标BTT_id的基准表（包含需要的8种唯一事务ID）
WITH required_btt AS (
    SELECT UNNEST(ARRAY[1,2,3,4,5,7,9]) AS btt_id
),
-- 2. 生成每个Bsub_id对应的所有目标BTT_id组合
bsub_btt_mapping AS (
    SELECT DISTINCT t.bsub_id, r.btt_id
    FROM your_transaction_table t
    CROSS JOIN required_btt r
),
-- 3. 关联真实事务与默认事务，标记优先级
transaction_with_default AS (
    SELECT 
        m.bsub_id,
        m.btt_id,
        -- 优先取真实事务的字段，无则用默认值
        COALESCE(t.transaction_data, default_transaction.data) AS transaction_data,
        -- 标记优先级：真实事务排1，默认排2
        CASE WHEN t.btt_id IS NOT NULL THEN 1 ELSE 2 END AS priority
    FROM bsub_btt_mapping m
    LEFT JOIN your_transaction_table t 
        ON m.bsub_id = t.bsub_id AND m.btt_id = t.btt_id
    -- 这里假设默认事务数据是固定值或来自默认表，示例用常量
    CROSS JOIN (SELECT '默认事务内容' AS data) AS default_transaction
)
-- 4. 按Bsub_id+BTT_id分组，取优先级最高的第一条记录
SELECT 
    bsub_id,
    btt_id,
    transaction_data
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY bsub_id, btt_id ORDER BY priority) AS rn
    FROM transaction_with_default
) ranked
WHERE rn = 1;

关键说明 #

• required_btt：定义必须包含的8个BTT_id，确保没有遗漏的目标事务类型。
• bsub_btt_mapping：通过交叉连接为每个Bsub_id生成所有目标BTT_id的组合，补全缺失的事务占位。
• transaction_with_default：用COALESCE优先获取真实事务数据，缺失时用默认值；同时用priority标记排序权重，保证真实事务优先。
• 窗口函数ROW_NUMBER()：按bsub_id和btt_id分区，按优先级排序后取第一行，实现“有真实取真实，无则取默认”的逻辑。

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