SQL补全缺失日期地点维度数据 缺失output值填充0方法

核心逻辑很简单：先构造所有日期+所有中心+所有类型的完整行组合，再把你现有查询的结果左连接上去，匹配不到的output值统一填0即可，不需要复杂逻辑。

针对PostgreSQL（支持generate_series）的可直接运行代码 #

假设你当前输出原始结果的查询/表名为your_source_data，代码如下：

WITH source_data AS (
    -- 替换成你自己的原始查询逻辑即可，比如你之前算output的那段SQL
    SELECT o_date, c_loc, type, output FROM your_origin_table
),
-- 生成覆盖数据范围的所有日期
date_series AS (
    SELECT o_date::date
    FROM generate_series(
        (SELECT MIN(o_date) FROM source_data),
        (SELECT MAX(o_date) FROM source_data),
        '1 day'
    ) AS o_date
),
-- 取全部中心点位
all_center AS (
    -- 如果你原始数据里缺某些中心的全量记录，直接改成枚举值：
    -- SELECT unnest(ARRAY['center 1','center 2','center 3','center 4','center 5','center 6','center 7','center 8','center 9','center 10','center 11']) AS c_loc
    SELECT DISTINCT c_loc FROM source_data
),
-- 取全部数据类型
all_type AS (
    -- 固定3种类型可以直接写枚举，更准确：
    -- SELECT unnest(ARRAY['A','B','C']) AS type
    SELECT DISTINCT type FROM source_data
),
-- 生成三个维度的完整笛卡尔积，也就是所有应该存在的行
full_rows AS (
    SELECT ds.o_date, ac.c_loc, at.type
    FROM date_series ds
    CROSS JOIN all_center ac
    CROSS JOIN all_type at
)
-- 关联原始数据，空值填0
SELECT
    fr.o_date,
    fr.c_loc,
    fr.type,
    COALESCE(sd.output, 0) AS output
FROM full_rows fr
LEFT JOIN source_data sd
    ON fr.o_date = sd.o_date
    AND fr.c_loc = sd.c_loc
    AND fr.type = sd.type
ORDER BY fr.o_date, fr.c_loc, fr.type;

关键说明 #

• 如果你确认总共有11个固定中心、3种固定类型，建议直接在all_center和all_type部分用枚举值写死，避免原始数据里某类记录完全缺失时，去重取不到对应维度值，导致补全不全。
• 如果你的日期不是连续自然日（比如只统计交易日、周度日期），把date_series部分的generate_series替换成你需要的日期列表即可，核心是拿到所有需要出现的日期全集。
• COALESCE函数会把左连接后匹配不到的NULL值统一替换为0，完全符合你的填充要求。

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