如何合并月度用户表并补全缺失月份的历史ZIP记录？

嘿，这个问题我之前帮别人处理过类似的，完全不用折腾一堆繁琐的临时表拼接！下面给你优先提供HiveQL解决方案，同时附上SSMS的SQL方案，两种方案都支持每月轻松更新主表，完美适配30+个月的大规模数据场景。

这个方案利用Hive的窗口函数生成全量用户月度维度，再通过LAST_VALUE()自动填充最近的有效ZIP，逻辑简洁且支持增量更新。

步骤1：生成全量用户-月份维度表 #

首先生成从首个数据月份到当前最新月份的所有月份列表，再和所有用户做笛卡尔积，得到每个用户每个月的基础记录（如果有用户注册时间，还可以过滤掉用户注册前的无效月份）：

WITH all_months AS (
    -- 获取所有已有数据的月份，也可以自动生成当前月
    SELECT DISTINCT Month FROM your_source_table
    UNION ALL
    SELECT date_format(current_date(), 'yyyyMM') AS Month -- 新增当前月
),
all_users AS (
    SELECT DISTINCT UserNumber FROM your_source_table
),
user_month_dim AS (
    SELECT u.UserNumber, m.Month
    FROM all_users u
    CROSS JOIN all_months m
    -- 可选：过滤用户注册前的月份，需有注册时间字段
    -- AND m.Month >= date_format(u.register_date, 'yyyyMM')
)

步骤2：关联原始数据并填充最近有效ZIP #

用LEFT JOIN关联原始数据后，借助LAST_VALUE()结合IGNORE NULLS参数（Hive 2.3+支持），自动抓取每个用户当前月份及之前最近的有效ZIP：

SELECT 
    um.UserNumber,
    um.Month,
    LAST_VALUE(s.ZIP, TRUE) OVER (
        PARTITION BY um.UserNumber 
        ORDER BY um.Month 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS ZIP
FROM user_month_dim um
LEFT JOIN your_source_table s
    ON um.UserNumber = s.UserNumber 
    AND um.Month = s.Month
ORDER BY um.UserNumber, um.Month;

每月增量更新实现 #

可以把主表设为可插入的表，每月仅生成新增月份的维度数据，再和历史数据合并，避免全量重跑：

-- 假设主表名为user_month_zip_master
INSERT INTO user_month_zip_master
SELECT 
    um.UserNumber,
    um.Month,
    LAST_VALUE(s.ZIP, TRUE) OVER (
        PARTITION BY um.UserNumber 
        ORDER BY um.Month 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS ZIP
FROM (
    SELECT u.UserNumber, m.Month
    FROM all_users u
    CROSS JOIN (
        SELECT date_format(current_date(), 'yyyyMM') AS Month
    ) m
    -- 过滤已存在于主表的记录，只插入新增月份数据
    WHERE NOT EXISTS (
        SELECT 1 FROM user_month_zip_master mst
        WHERE mst.UserNumber = u.UserNumber AND mst.Month = m.Month
    )
) um
LEFT JOIN your_source_table s
    ON um.UserNumber = s.UserNumber 
    AND um.Month = s.Month;

SQL Server环境下，我们可以用递归CTE生成月份列表，再根据版本选择合适的方式填充最近有效ZIP。

步骤1：生成全量用户-月份维度表 #

用递归CTE生成从最早数据月份到当前月的所有月份，再和用户做笛卡尔积：

WITH all_months AS (
    -- 递归生成所有月份（格式yyyyMM）
    SELECT MIN(CAST(Month AS INT)) AS Month
    FROM your_source_table
    UNION ALL
    SELECT Month + 1
    FROM all_months
    WHERE Month < CAST(FORMAT(GETDATE(), 'yyyyMM') AS INT)
),
all_users AS (
    SELECT DISTINCT UserNumber FROM your_source_table
),
user_month_dim AS (
    SELECT u.UserNumber, CAST(m.Month AS VARCHAR(6)) AS Month
    FROM all_users u
    CROSS JOIN all_months m
)

步骤2：填充最近有效ZIP #

• SQL Server 2022+版本：支持IGNORE NULLS参数，直接用LAST_VALUE()即可：

SELECT 
    um.UserNumber,
    um.Month,
    LAST_VALUE(s.ZIP) OVER (
        PARTITION BY um.UserNumber 
        ORDER BY um.Month 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        IGNORE NULLS
    ) AS ZIP
FROM user_month_dim um
LEFT JOIN your_source_table s
    ON um.UserNumber = s.UserNumber 
    AND um.Month = s.Month
ORDER BY um.UserNumber, um.Month;

• 低版本SQL Server：可以用OUTER APPLY查询最近的有效记录：

SELECT 
    um.UserNumber,
    um.Month,
    (SELECT TOP 1 ZIP 
     FROM your_source_table s
     WHERE s.UserNumber = um.UserNumber 
       AND s.Month <= um.Month
     ORDER BY s.Month DESC) AS ZIP
FROM user_month_dim um
ORDER BY um.UserNumber, um.Month;

每月增量更新实现 #

同样只需插入当月的新增数据：

INSERT INTO user_month_zip_master (UserNumber, Month, ZIP)
SELECT 
    um.UserNumber,
    um.Month,
    LAST_VALUE(s.ZIP) OVER (
        PARTITION BY um.UserNumber 
        ORDER BY um.Month 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        IGNORE NULLS
    ) AS ZIP
FROM (
    SELECT u.UserNumber, CAST(FORMAT(GETDATE(), 'yyyyMM') AS VARCHAR(6)) AS Month
    FROM all_users u
    WHERE NOT EXISTS (
        SELECT 1 FROM user_month_zip_master mst
        WHERE mst.UserNumber = u.UserNumber AND mst.Month = CAST(FORMAT(GETDATE(), 'yyyyMM') AS VARCHAR(6))
    )
) um
LEFT JOIN your_source_table s
    ON um.UserNumber = s.UserNumber 
    AND um.Month = s.Month;

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