Oracle SQL表与字典表关联时缺失值填充问题求助

实现逻辑 #

要解决关联后维度缺失的问题，核心是先生成全量的维度组合，再关联业务数据补全值，步骤如下：

• 提取需要补全的所有维度：一般是字典表的全量枚举值，加上业务表中需要保留的独立维度（如统计日期、所属区域等）
• 通过CROSS JOIN生成所有维度的笛卡尔积，确保不存在缺失的维度组合
• 用全量维度组合左关联业务表，关联不到的字段默认返回空
• 用NVL()函数将空值替换为符合业务要求的默认值（数值类默认填0，文本类默认填'无'即可）

代码示例 #

假设涉及表结构如下：

1. 字典表dict_product_type：存储全量商品类型，字段type_id（类型ID）、type_name（类型名称）
2. 业务表daily_sales：存储每日各商品类型的销售数据，字段sale_date（销售日期）、type_id（商品类型ID）、sales_vol（销量）

需求是补全每日所有商品类型的销量数据，缺失的销量填0，代码如下：

SELECT
    dt.sale_date,
    d.type_id,
    d.type_name,
    NVL(s.sales_vol, 0) AS sales_vol
FROM
    -- 提取业务表中所有需要统计的日期
    (SELECT DISTINCT sale_date FROM daily_sales) dt
    -- 交叉连接字典表生成 日期+全量商品类型 的所有组合
    CROSS JOIN dict_product_type d
    -- 左关联业务表匹配对应日期对应类型的销量
    LEFT JOIN daily_sales s
        ON dt.sale_date = s.sale_date
        AND d.type_id = s.type_id
ORDER BY dt.sale_date, d.type_id;

适配调整说明 #

• 如果需要同时补全多个维度，只需在CROSS JOIN前增加对应维度的去重子查询即可
• 空值替换规则可根据业务需求调整，比如需要补全的是文本类字段，可改为NVL(s.xxx, '未匹配')
• Oracle 11g及以上版本均支持上述语法，执行效率可通过给关联字段加索引优化

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