SQL Server 2014 - 当日期不存在时使用以前的值

Question

我昨天问了一个类似的问题，但我对我想要的东西的描述不太好。这会更清楚。

超前/滞后无法满足我的需求。很接近，但还不够。使用 SQL Server 2014 作为客户端，实际服务器基于 SQL 2012 构建。

这是我的代码:创建团队表

CREATE TABLE ##TeamTable
    ([UserID] varchar(50), [CurrentTeam] varchar(5), [ChangeDate] datetime)
;

INSERT INTO ##TeamTable
    ([UserID], [CurrentTeam], [ChangeDate])
VALUES
    ('User1', 'Team1', '6/1/2016'),
    ('User1', 'Team2', '9/1/2016'),
    ('User1', 'Team3', '12/1/2016'),
    ('User2', 'Team1', '4/1/2016'),
    ('User2', 'Team2', '10/1/2016'),
    ('User2', 'Team3', '11/1/2016');

现在要创建我需要加入的数据表

CREATE TABLE ##DataTable
    ([UserID] varchar(50), Month_sk datetime, Media varchar(50), NCO int)
INSERT INTO ##DataTable
    ([UserID] , Month_sk , Media , NCO )
VALUES
    ('User1', '2016-06-01 00:00:00', 'Fax', 100),
    ('User1', '2016-06-01 00:00:00', 'Voice', 120),
    ('User1', '2016-07-01 00:00:00', 'Voice', 90),
    ('User1', '2016-07-01 00:00:00', 'Email', 100),
    ('User1', '2016-08-01 00:00:00', 'Voice', 150),
    ('User1', '2016-08-01 00:00:00', 'Email', 100),
    ('User1', '2016-09-01 00:00:00', 'Voice', 100),
    ('User1', '2016-09-01 00:00:00', 'Email', 120),
    ('User1', '2016-10-01 00:00:00', 'Voice', 90),
    ('User1', '2016-10-01 00:00:00', 'Email', 100),
    ('User1', '2016-11-01 00:00:00', 'Voice', 150),
    ('User1', '2016-11-01 00:00:00', 'Email', 100),
    ('User1', '2016-12-01 00:00:00', 'Voice', 150),
    ('User1', '2016-12-01 00:00:00', 'Email', 100),
    ('User2', '2016-04-01 00:00:00', 'Fax', 100),
    ('User2', '2016-04-01 00:00:00', 'Voice', 120),
    ('User2', '2016-05-01 00:00:00', 'Fax', 100),
    ('User2', '2016-05-01 00:00:00', 'Voice', 120),
    ('User2', '2016-06-01 00:00:00', 'Fax', 100),
    ('User2', '2016-06-01 00:00:00', 'Voice', 120),
    ('User2', '2016-07-01 00:00:00', 'Voice', 90),
    ('User2', '2016-07-01 00:00:00', 'Email', 100),
    ('User2', '2016-08-01 00:00:00', 'Voice', 150),
    ('User2', '2016-08-01 00:00:00', 'Email', 100),
    ('User2', '2016-09-01 00:00:00', 'Voice', 100),
    ('User2', '2016-09-01 00:00:00', 'Email', 120),
    ('User2', '2016-10-01 00:00:00', 'Voice', 90),
    ('User2', '2016-10-01 00:00:00', 'Email', 100),
    ('User2', '2016-11-01 00:00:00', 'Voice', 150),
    ('User2', '2016-11-01 00:00:00', 'Email', 100),
    ('User2', '2016-12-01 00:00:00', 'Voice', 150),
    ('User2', '2016-12-01 00:00:00', 'Email', 100);

这是一个基本的选择来显示正在发生的事情:

SELECT  b.UserID
        ,b.Media
        ,b.NCO
        ,Month_sk
        ,CurrentTeam

FROM    ##DataTable b

LEFT OUTER JOIN ##TeamTable a on b.UserID = a.UserID and b.Month_sk = a.ChangeDate

order by UserID, Month_sk, media

这给了我一个如下所示的结果集:

我需要的是对于有空值的地方，它将拉入以前不为空的团队名称。因此，在 User1 的情况下，7 月和 8 月的 4 个空值将表示 Team1，因为那是他最后加入的团队。 Team2 之后的空值也是如此，应该说 Team2。

超前/滞后很接近或者我没有正确使用它。希望通过所有这些代码，这可以使某人的工作变得更容易。

更新:滞后/超前给出相同的结果。仍然需要填写空值

SELECT  b.UserID
        ,b.Media
        ,b.NCO
        ,Month_sk
        ,CurrentTeam
        ,LAG(CurrentTeam,1, currentteam) OVER(PARTITION BY a.userid, changedate ORDER BY ChangeDate) as Lag

FROM    ##DataTable b

LEFT OUTER JOIN ##TeamTable a on b.UserID = a.UserID and b.Month_sk = a.ChangeDate

order by UserID, Month_sk, media

Answer 1

(将更新说明移至末尾)

我认为最简单的解决方案(概念上)是加入直到 month_sk 的所有月份，然后过滤以仅获取最后一个匹配项。这“感觉”可能效率低下，因此您需要使用实际数据量对其进行测试，如果存在问题，则寻找更好的方法。 (但是“更好的东西”可能涉及物理数据模型的更改......)

所以:

select userid, media, nco, month_sk, currentteam
  from (SELECT b.UserID
             , b.Media
             , b.NCO
             , Month_sk
             , CurrentTeam
             , rank() over(partition by b.userID
                               order by a.changeDate desc) n
        FROM            ##DataTable b
             INNER JOIN ##TeamTable a
                     on b.UserID = a.UserID
                    and b.Month_sk >= a.ChangeDate
       ) x
 where n = 1
 order by UserID, Month_sk, media

请注意，在以前的版本中，我使用了 row_number() over() 而不是 rank() over()...你可以这样做，但如果你那么您必须在分区键中包含 b 表中的任何数据，这些数据可能会在连接期间导致 a 表中的行重复。使用rank可确保所有此类重复项按其应有的方式共享其排名。

更新 - 在我最初写下这篇文章后，我删除了它，因为我认为我误读了你的问题；但当我写一个替代品时，我意识到我一开始可能就做对了。所以这里是，但有一个警告:

这假设您获得 NULL 值的唯一原因是外连接。如果“右手”表有一行，并且其中只有一个列的值为 NULL，那么获取该列的前一个值将需要进一步使用子查询或分析函数。但即便如此，领先/滞后也可能不起作用，因为它们是基于位置的。 (我认为带有 LAST_VALUE 的内容可能更合适，但除非需要，否则会保留其详细信息。)

更新2 - 根据您在下面的评论中对数据模型的描述，我正在更改查询以显示内部联接，因为听起来这会起作用(一旦您扩大联接条件)并且应该更加高效。

更新 3 - 我确实误读了您的示例数据，并且计算 n 的分区表达式错误。假设 b 表中的值是唯一的，则应予以修复。如果没有，它仍然可以修复，但需要更多技巧......