Системные версионные (временные) таблицы в представлении

Question

У меня есть несколько объединенных таблиц "системной версии", например Person, PhoneNumber и EmailAddress. У Person будет одновременно только один PhoneNumber и один EmailAddress.

PhoneNumber и EmailAddress обычно не обновляются вне транзакции, которая обновляет все 3 сразу. (Но они могут обновляться независимо, но только не в обычном сценарии). Например, если я изменю номер телефона, то все 3 записи будут обновлены в одной транзакции, что даст им одинаковое "время начала" в истории. Таблица.

Допустим, я вставил человека, а затем изменил его имя, адрес электронной почты и номер телефона в 2 транзакциях:

DECLARE @Id TABLE(ID INT)
DECLARE @PersonId INT

-- Initial insert
BEGIN TRANSACTION
    INSERT INTO Person (Name) OUTPUT inserted.PersonId INTO @Id VALUES ('Homer') 
    SELECT @PersonId = Id FROM @Id
    INSERT INTO EmailAddress (Address, PersonId) VALUES ('homer@fake', @PersonId)
    INSERT INTO PhoneNumber (Number, PersonId) VALUES ('999', @PersonId)
COMMIT TRANSACTION

-- Update 
WAITFOR DELAY '00:00:02'

BEGIN TRANSACTION
    UPDATE Person SET Name = 'Kwyjibo' WHERE PersonID = @PersonId
    UPDATE EmailAddress SET Address = 'kwyjibo@fake'  WHERE PersonID = @PersonId
    UPDATE PhoneNumber SET Number = '000'  WHERE PersonID = @PersonId
COMMIT TRANSACTION

Теперь я выбираю из представления (просто внутреннее соединение таблиц), используя временный запрос:

SELECT * FROM vwPerson FOR SYSTEM_TIME ALL 
WHERE PersonId = @PersonId
ORDER BY SysStartTime DESC

И мне возвращают строку для каждой комбинации редактирования!

Как я могу запросить это представление (если это вообще возможно), чтобы вернуть только 1 строку для обновлений, которые были сделаны в той же транзакции?
Я мог бы добавить предложение WHERE, чтобы соответствовать всем SysStartTimes, однако это исключило бы те случаи, когда таблица обновлялась независимо от других 2.

Answer 1

Из-за независимых обновлений вам сначала нужно "восстановить" временную шкалу, на которую вы можете объединить данные. Вот "набросок" этого, очевидно, что ваши фактические определения таблиц так не проверены:

;WITH AllTimes as (
     SELECT PersonId,SysStartTime as ATime FROM Person
     UNION
     SELECT PersonId,SysEndTime FROM Person
     UNION
     SELECT PersonId,SysStartTime FROM EmailAddress
     UNION
     SELECT PersonId,SysEndTime FROM EmailAddress
     UNION
     SELECT PersonId,SysStartTime FROM PhoneNumber
     UNION
     SELECT PersonId,SysEndTime FROM PhoneNumber
), Ordered as (
     SELECT
        PersonId, ATime, ROW_NUMBER() OVER (PARTITION BY PersonId ORDER BY Atime) rn
     FROM
        AllTimes
), Intervals as (
    SELECT
       p1.PersonId,
       o1.ATime as StartTime,
       o2.ATime as EndTime
    FROM
       Ordered o1
          inner join
       Ordered o2
          on
              o1.PersonId = o2.PersonId and
              o1.rn = o2.rn - 1
)
SELECT
    * --TODO - Columns
FROM
   Intervals i
      inner join
   Person p
      on
          i.PersonId = p.PersonId and
          i.StartTime < p.SysEndTime and
          p.SysStartTime < i.EndTime
      inner join
   Email e
      on
          i.PersonId = e.PersonId and
          i.StartTime < e.SysEndTime and
          e.SysStartTime < i.EndTime
      inner join
   PhoneNumber pn
      on
          i.PersonId = pn.PersonId and
          i.StartTime < pn.SysEndTime and
          pn.SysStartTime < i.EndTime

С соответствующими фильтрами, если вам нужны данные только одного человека, мы надеемся, что оптимизатор сработает. Могут быть дополнительные фильтры для объединений, которые я также пропустил.

Надеюсь, вы сможете увидеть, как 3 CTE строят временную шкалу. Мы используем в своих интересах UNION устранение дубликатов в первом.