sql-server - 如何组合表并从上一个表创建新记录(如果丢失)？

Question

每个月我都会收到一份用户列表及其家庭邮政编码。但是，并非每个用户都提供每个月的邮政编码，因此我的每月表格大小永远不会相同。

我想要做的是创建一个主表，该表从第一个月开始为每个用户创建每个月的记录。然后，如果第一个月的用户没有出现在第二个月，他们仍然应该获得第二个月的记录，并根据上个月分配邮政编码。

例如，我有两个如下所示的表:

UserNumber Month  ZIP
1          201701 12345
2          201701 30032
3          201701 01432
Etc.

UserNumber Month  ZIP
1          201702 12345
3          201702 01433
4          201702 30032
Etc.

您可以看到一些邮政编码将发生变化(用户 3“已移动”)，这是正常的。但是用户 2 没有 201702 的记录。但是我的新主表应该有一条记录，其中使用了 201701 的邮政编码。所以主表应该是这样的:

UserNumber Month  ZIP
1          201701 12345
1          201702 12345
2          201701 30032
2          201702 30032 
3          201701 01432
Etc.

如上所述，用户 2 有一条 201702 的记录，该记录使用与我们记录相同的邮政编码。有时会丢失多个月份，所以我想获取小于当前月份的最新记录。

我尝试根据表相交创建多个临时表，然后将它们附加在一起，效果很好。但对于 30 多个月的数据来说，这将变得非常复杂和乏味，所以我希望有更好的方法。这个主表也必须每月更新。

如有任何建议，我将不胜感激!

目前数据位于 S3 中，我使用 Hive 访问它，因此 HiveQL 解决方案是理想的选择，因此我不必将所有这些数据导入 SSMS，但如果使用 SQL 在 SSMS 中更容易执行此操作，我可以使其工作也是如此。

Answer 1

这样的事情听起来像是可行的，至少在你构建了初始集之后是这样。这确实假设该流程每月更新一次，以便该系列中不会出现间隙:

insert into Master (userid, month, zip)
select
    coalesce(u.userid, m.userid),
    coalesce(u.month, convert(char(6), dateadd(month, 1, m.month + '01'), 112),
    coalesce(u.zip, m.zip)
from ZipUpdate u full outer join Master m
    on m.userid = u.userid and m.month =
        convert(char(6), dateadd(month, -1, u.month + '01'), 112);

如果您想填补空白，您可以从一个空表开始，然后循环运行 30 多次。如果表名是可预测的，那么类似这样的东西就可以生成整个脚本。

declare @sql varchar(8000);
declare @dt date = '20170101';
while @dt < cast('20190901' as date)
begin
    set @sql = 'insert into Master (userid, month, zip)
    select
        coalesce(u.userid, m.userid),
        ''' + convert(char(6), @dt, 112) + ''',
        coalesce(u.zip, m.zip)
    from '
        /* change this expression as needed */
        + 'ZipUpdate'
        + convert(varchar(3), datediff(month, '20170101', @dt) + 1)
        + ' u full outer join Master m
        on m.userid = u.userid and m.month = ''' +
        + convert(char(6), dateadd(month, -1, @dt), 112) + '''
    where u.month = ''' + convert(char(6), @dt, 112) + ''';'

    set @dt = dateadd(month, 1, @dt);
    select @sql;
end