sql - 如何使用 SQL Server 时态表识别更改的值？

Question

我有一个 SQL Azure 表，并且已启用新的临时表功能(SQL Server 2016 和 SQL Azure v12 的新增功能)。此功能创建另一个表来跟踪主表的所有更改(我在问题底部添加了有关临时表的文档的链接)。您可以使用特殊的查询语言来获取此历史记录。请注意以下查询中的 FOR SYSTEM_TIME ALL:

SELECT 
    ValidFrom
    , ValidTo
    , ShiftId
    , TradeDate
    , StatusID
    , [LastActionDate]
    , [OwnerUserID]
    , [WorkerUserID]
    , [WorkerEmail]
    , [Archived]
FROM [KrisisShifts_ShiftTrade] 
FOR SYSTEM_TIME ALL
WHERE [ShiftID] = 27
ORDER BY ValidTo Desc

结果集如下所示:

ValidFrom                   ValidTo                     ShiftId     TradeDate  StatusID    LastActionDate          OwnerUserID WorkerUserID WorkerEmail                                        Archived
--------------------------- --------------------------- ----------- ---------- ----------- ----------------------- ----------- ------------ -------------------------------------------------- --------
2017-06-21 00:26:44.51      9999-12-31 23:59:59.99      27          2017-01-27 3           2017-01-09 16:23:39.760 45          34           test@hotmail.com                                   1
2017-06-21 00:19:35.57      2017-06-21 00:26:44.51      27          2017-01-27 2           2017-01-09 16:23:39.760 45          34           test@hotmail.com                                   1
2017-06-21 00:19:16.25      2017-06-21 00:19:35.57      27          2017-01-28 3           2017-01-09 16:23:39.760 45          34           test@hotmail.com                                   1

使用SYSTEM_TIME FOR ALL 时态表返回主表(即第一个记录)中的当前记录，其余记录是存储在跟踪表中的该记录的先前版本。 (可以看到validFrom和ValidTo列，显然是当前记录的时间)在这种情况下，保存历史记录的跟踪表称为KrisisShifts_ShiftTrade_History

我想要什么:

我想构建一个查询，仅突出显示每个历史点所做的更改。请注意，第二条记录具有不同的 StatusID，第三条记录具有不同的 TradeDate

我想产生如下结果集(我想我会忽略第一个或当前记录，因为它显然没有被更改):

期望结果:

ShiftId      Column          Value             ValidFrom                   ValidTo
----------  -------------  ------------------- --------------------------- --------------------------
27          StatusId       2                   2017-06-21 00:19:35.57      2017-06-21 00:26:44.51
27          TradeDate      2017-01-28          2017-06-21 00:19:35.57      2017-06-21 00:26:44.51   

我不知道如何实现这一点。或者我愿意接受另一种解决方案。我希望能够快速查看每条记录相对于原始记录的变化。

我尝试对结果进行逆透视以比较它们，但我无法使其正常工作，因为每行的类次 ID 都是相同的。我很想在这里展示更多作品，但我真的陷入困境。

编辑1:

我已经能够使用 lag() 隔离以下查询中仅一列的更改。我可以将此查询与我想要跟踪的每一列的类似查询结合起来，但是，这是一项繁重的工作，并且必须为每个表构建。有没有办法动态地执行此操作，以便自动检测列？

StatusID更改历史查询:(我将记录隔离到shiftId为27只是为了测试)

SELECT 'SHIFT STATUS'  as ColumnName, t1.RecVersion, t1.ShiftID, t1.ValidFrom, t1.ValidTo, t1.StatusId
, (SELECT [Title] FROM [dbo].[KrisisShifts_Status] WHERE [dbo].[KrisisShifts_Status].[StatusID] = t1.StatusId) AS RecStatus
FROM
    (SELECT TOP 100 PERCENT 
        ROW_NUMBER() OVER(PARTITION BY ShiftId ORDER BY ValidTo ASC) AS RecVersion -- reverse sorting the ValidTo date gives "version count" to column changes
        , t2.ValidTo
        , t2.ValidFrom
        , t2.ShiftID
        , t2.StatusId
        , LAG(StatusId,1,0) OVER (ORDER BY ValidTo DESC) AS PrevStatusId
    FROM [KrisisShifts_ShiftTrade] 
    FOR SYSTEM_TIME ALL AS t2

    ORDER BY t2.ValidTo Desc
    ) AS t1
WHERE
    (t1.StatusId <> t1.PrevStatusId)
    AND
    SHIFTID = 27
ORDER BY t1.ValidTo DESC

查询结果:

ColumnName   RecVersion           ShiftID     ValidFrom                   ValidTo                     StatusId    RecStatus
------------ -------------------- ----------- --------------------------- --------------------------- ----------- --------------------------------------------------
SHIFT STATUS 3                    27          2017-06-21 00:26:44.51      2017-06-25 14:09:32.37      3           Confirmed
SHIFT STATUS 2                    27          2017-06-21 00:19:35.57      2017-06-21 00:26:44.51      2           Reserved
SHIFT STATUS 1                    27          2017-06-21 00:19:16.25      2017-06-21 00:19:35.57      3           Confirmed

结束编辑 1:

问题:

有人可以帮助我将临时表结果集中每个 shiftId 的前一个记录中的列中更改的数据隔离出来吗？

提前致谢

编辑＃2:

以下是我想要从此表中“监视更改”的所有列的列表:

[交易日期][状态ID][最后操作日期][允许的RankID][所有者用户ID][所有者电子邮件][所有者位置 ID][所有者等级ID][所有者员工ID][ worker 用户ID][ worker 邮箱][ worker 位置ID][ worker 等级ID][ worker 排ID][ worker 雇员ID][IsPartialShift][细节][用户 ID 上次修改时间][已存档][更新日期]

结束编辑 2:

新标签注意事项:

我为临时表创建了一个新标签，因为没有标签。如果有更多声誉的人想要将其添加到标签的详细信息中，下面有对它们的描述。

MS Docs on Temporal Tables

Answer 1

您还可以使用CROSS APPLY来UNPIVOT。

需要注意的是，ValidFrom 和 ValidTo 指的是行版本本身的有效性，而不一定是列值的有效性。我相信这就是您所要求的，但这可能会令人困惑。

Demo

WITH T
     AS (SELECT ValidFrom,
                ValidTo,
                ShiftId,
                TradeDate,
                StatusID,
                LastActionDate,
                OwnerUserID,
                WorkerUserID,
                WorkerEmail,
                Archived,
                nextTradeDate = LEAD(TradeDate) OVER (PARTITION BY ShiftId ORDER BY ValidFrom),
                nextStatusID = LEAD(StatusID) OVER (PARTITION BY ShiftId ORDER BY ValidFrom),
                nextLastActionDate = LEAD(LastActionDate) OVER (PARTITION BY ShiftId ORDER BY ValidFrom),
                nextOwnerUserID = LEAD(OwnerUserID) OVER (PARTITION BY ShiftId ORDER BY ValidFrom),
                nextWorkerUserID = LEAD(WorkerUserID) OVER (PARTITION BY ShiftId ORDER BY ValidFrom),
                nextWorkerEmail = LEAD(WorkerEmail) OVER (PARTITION BY ShiftId ORDER BY ValidFrom),
                nextArchived = LEAD(Archived) OVER (PARTITION BY ShiftId ORDER BY ValidFrom)
         FROM   KrisisShifts_ShiftTrade)
SELECT ShiftId,
       Colname AS [Column],
       value,
       ValidFrom,
       ValidTo
FROM   T
       CROSS APPLY ( VALUES 
                    ('TradeDate', CAST(TradeDate AS NVARCHAR(4000)), CAST(nextTradeDate AS NVARCHAR(4000))),
                    ('StatusID', CAST(StatusID AS NVARCHAR(4000)), CAST(nextStatusID AS NVARCHAR(4000))),
                    ('LastActionDate', CAST(LastActionDate AS NVARCHAR(4000)), CAST(nextLastActionDate AS NVARCHAR(4000))),
                    ('OwnerUserID', CAST(OwnerUserID AS NVARCHAR(4000)), CAST(nextOwnerUserID AS NVARCHAR(4000))),
                    ('WorkerUserID', CAST(WorkerUserID AS NVARCHAR(4000)), CAST(nextWorkerUserID AS NVARCHAR(4000))),
                    ('WorkerEmail', CAST(WorkerEmail AS NVARCHAR(4000)), CAST(nextWorkerEmail AS NVARCHAR(4000))),
                    ('Archived', CAST(Archived AS NVARCHAR(4000)), CAST(nextArchived AS NVARCHAR(4000)))
                   ) CA(Colname, value, nextvalue)
WHERE  EXISTS(SELECT value
              EXCEPT
              SELECT nextvalue)
       AND ValidTo <> '9999-12-31 23:59:59'
ORDER  BY ShiftId,
          [Column],
          ValidFrom;

如果您确实想要列级别的有效性，您可以使用 ( Demo )

WITH T1 AS
(
SELECT *, 
       ROW_NUMBER() OVER (PARTITION BY ShiftId, colname ORDER BY ValidFrom)  
       - ROW_NUMBER() OVER (PARTITION BY ShiftId, colname, Colvalue ORDER BY ValidFrom)  AS Grp,
       IIF(DENSE_RANK() OVER (PARTITION BY ShiftId, colname ORDER BY Colvalue) + 
        DENSE_RANK() OVER (PARTITION BY ShiftId, colname ORDER BY Colvalue DESC) = 2, 0,1) AS HasChanges
FROM KrisisShifts_ShiftTrade
       CROSS APPLY ( VALUES 
                    ('TradeDate', CAST(TradeDate AS NVARCHAR(4000))),
                    ('StatusID', CAST(StatusID AS NVARCHAR(4000))),
                    ('LastActionDate', CAST(LastActionDate AS NVARCHAR(4000))),
                    ('OwnerUserID', CAST(OwnerUserID AS NVARCHAR(4000))),
                    ('WorkerUserID', CAST(WorkerUserID AS NVARCHAR(4000))),
                    ('WorkerEmail', CAST(WorkerEmail AS NVARCHAR(4000))),
                    ('Archived', CAST(Archived AS NVARCHAR(4000)))
                   ) CA(Colname, Colvalue)
)
SELECT  ShiftId, colname, Colvalue, MIN(ValidFrom) AS ValidFrom, MAX(ValidTo) AS ValidTo
FROM T1
WHERE HasChanges = 1
GROUP BY ShiftId, colname, Colvalue, Grp
ORDER  BY ShiftId,
          colname,
          ValidFrom;