如何用SQL借助参考表补全数据表中的缺失行？

核心思路 #

先通过CROSS JOIN生成所有需要的Name-日期组合（确保每个Name都有2023-01-01至2023-01-03的记录），再关联原始数据表获取已有预算，最后用参考表的对应日期预算填充缺失值，得到完整记录集后计算平均值。

假设表结构 #

• 原始数据表：budget_data，字段：Name（字符型）、date（日期型）、budget（数值型）
• 参考表：reference_budget，字段：date（日期型）、budget（数值型）（包含2023-01-01至2023-01-03的预算数据）

完整SQL语句 #

-- 生成完整的Name-日期组合，补全缺失记录并填充预算
WITH full_date_name AS (
    -- 获取所有唯一的Name
    SELECT DISTINCT Name FROM budget_data
    -- 交叉连接目标日期范围（也可直接从参考表取日期）
    CROSS JOIN (
        SELECT date FROM reference_budget 
        WHERE date BETWEEN '2023-01-01' AND '2023-01-03'
    ) AS required_dates
)
SELECT 
    f.Name,
    f.date,
    -- 优先用原始数据预算，缺失则取参考表对应日期预算
    COALESCE(b.budget, r.budget) AS budget
FROM full_date_name f
LEFT JOIN budget_data b 
    ON f.Name = b.Name AND f.date = b.date
LEFT JOIN reference_budget r 
    ON f.date = r.date;

平均值计算扩展 #

如果需要直接算出每个Name的3天预算平均值，可在上述基础上修改为：

WITH full_date_name AS (
    SELECT DISTINCT Name FROM budget_data
    CROSS JOIN (
        SELECT date FROM reference_budget 
        WHERE date BETWEEN '2023-01-01' AND '2023-01-03'
    ) AS required_dates
)
SELECT 
    f.Name,
    AVG(COALESCE(b.budget, r.budget)) AS avg_3day_budget
FROM full_date_name f
LEFT JOIN budget_data b 
    ON f.Name = b.Name AND f.date = b.date
LEFT JOIN reference_budget r 
    ON f.date = r.date
GROUP BY f.Name;

关键部分说明 #

• full_date_name 临时表：通过DISTINCT Name提取所有用户，再和目标日期做交叉连接，直接生成无缺失的用户-日期框架，解决原始数据缺行问题。
• COALESCE 函数：按优先级取值，原始表有数据则用原始值，没有则用参考表的对应日期预算，完成缺失值补全。

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