Scenariusze użycia tabeli czasowej

Dotyczy do: SQL Server 2016 (13.x) i nowsze wersje Azure SQL DatabaseAzure SQL Managed InstanceSQL database in Microsoft Fabric

Tabele czasowe w wersji systemowej są przydatne w scenariuszach, które wymagają śledzenia historii zmian danych. Zalecamy rozważenie tabel czasowych w następujących przypadkach użycia, aby uzyskać duże korzyści z produktywności.

Inspekcja danych

W tabelach przechowujących informacje o krytycznym znaczeniu można używać czasowego przechowywania wersji systemu, aby śledzić, co się zmieniło i kiedy, oraz wykonywać analizy danych w dowolnym momencie.

Używaj tabel czasowych do planowania scenariuszy audytu danych na wczesnych etapach cyklu rozwoju. Możesz dodać audyt danych do istniejących aplikacji lub rozwiązań, gdy jest to potrzebne.

Na poniższym diagramie przedstawiono tabelę Employee z przykładowymi danymi, w tym bieżącą (oznaczoną kolorem niebieskim) i wersjami wierszy historycznych (oznaczonych szarym kolorem).

Prawa część diagramu wizualizuje wersje wierszy na osi czasowej oraz wiersze, które wybierasz, z różnymi typami zapytań na tablicy czasowej, z klauzulą SYSTEM_TIME lub bez niej.

Diagram przedstawiający pierwszy scenariusz użycia czasowego.

Włączanie przechowywania wersji systemu w nowej tabeli na potrzeby inspekcji danych

Jeśli zidentyfikujesz informacje, które wymagają inspekcji danych, utwórz tabele baz danych jako tabele czasowe w wersji systemowej. Poniższy przykład ilustruje scenariusz z tabelą o nazwie Employee w hipotetycznej bazie danych HR:

CREATE TABLE Employee
(
    [EmployeeID] INT NOT NULL PRIMARY KEY CLUSTERED,
    [Name] NVARCHAR (100) NOT NULL,
    [Position] VARCHAR (100) NOT NULL,
    [Department] VARCHAR (100) NOT NULL,
    [Address] NVARCHAR (1024) NOT NULL,
    [AnnualSalary] DECIMAL (10, 2) NOT NULL,
    [ValidFrom] DATETIME2 (2) GENERATED ALWAYS AS ROW START,
    [ValidTo] DATETIME2 (2) GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));

Różne opcje tworzenia tabeli czasowej wersjonowanej systemem zostały opisane w artykule Utwórz tabelę czasową wersjonowaną przez system.

Włączanie przechowywania wersji systemu w istniejącej tabeli na potrzeby inspekcji danych

Jeśli musisz przeprowadzić inspekcję danych w istniejących bazach danych, użyj ALTER TABLE, aby rozszerzyć tabele nietemporarne, tak aby stały się wersjonowane przez system. Aby uniknąć niezgodnych zmian w aplikacji, dodaj kolumny okresu przy użyciu HIDDEN, jak opisano w Utwórz tabelę czasową wersjonowaną przez system.

Poniższy przykład ilustruje włączanie wersjonowania systemowego na istniejącej tabeli Employee w hipotetycznej bazie danych HR. Umożliwia przechowywanie wersji systemu w tabeli Employee w dwóch krokach. Najpierw nowe kolumny okresu są dodawane jako HIDDEN. Następnie tworzy domyślną tabelę historii.

ALTER TABLE Employee
ADD
    ValidFrom DATETIME2 (2) GENERATED ALWAYS AS ROW START HIDDEN
        CONSTRAINT DF_ValidFrom DEFAULT DATEADD(SECOND, -1, SYSUTCDATETIME()),
    ValidTo DATETIME2 (2) GENERATED ALWAYS AS ROW END HIDDEN
        CONSTRAINT DF_ValidTo DEFAULT '9999.12.31 23:59:59.99',
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);

ALTER TABLE Employee
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Employee_History));

Important

Precyzja typu danych datetime2 musi być taka sama w tabeli źródłowej jak w tabeli historii wersji systemu.

Po uruchomieniu poprzedniego skryptu tabela historii transparentnie zbiera wszystkie zmiany danych. W typowym scenariuszu audytu danych wyszukujesz wszystkie zmiany danych wprowadzone w pojedynczym wierszu w interesującym Cię przedziale czasu. Domyślna tabela historii jest tworzona jako klastrowane B-drzewo przechowujące wiersze, aby efektywnie rozwiązać ten przypadek użycia.

Note

W dokumentacji jest zwykle używany termin B-tree w odniesieniu do indeksów. W indeksach typu rowstore silnik bazy danych implementuje drzewo B+. Nie dotyczy to indeksów magazynu kolumn ani indeksów w tabelach zoptymalizowanych pod kątem pamięci. Aby uzyskać więcej informacji, zobacz architekturę i przewodnik projektowania indeksu SQL Server i Azure SQL.

Przeprowadzanie analizy danych

Po włączeniu obsługi wersji systemu za pomocą jednego z poprzednich podejść, inspekcja danych to już kwestia jednego zapytania. Następujące zapytanie wyszukuje wersje wierszy dla rekordów w tabeli Employee, które związane są z EmployeeID = 1000 i były aktywne co najmniej przez część okresu między 1 stycznia 2021 r. a 1 stycznia 2022 r. (włącznie z górną granicą).

SELECT *
FROM Employee FOR SYSTEM_TIME
    BETWEEN '2021-01-01 00:00:00.0000000' AND '2022-01-01 00:00:00.0000000'
WHERE EmployeeID = 1000
ORDER BY ValidFrom;

Zastąp FOR SYSTEM_TIME BETWEEN...ANDFOR SYSTEM_TIME ALL, aby przeanalizować całą historię zmian danych dla tego konkretnego pracownika:

SELECT *
FROM Employee FOR SYSTEM_TIME ALL
WHERE EmployeeID = 1000
ORDER BY ValidFrom;

Aby wyszukać wersje wierszy, które były aktywne tylko w określonym okresie (a nie poza nim), skorzystaj z CONTAINED IN. To zapytanie jest wydajne, ponieważ wykonuje zapytanie tylko w tabeli historii:

SELECT *
FROM Employee FOR SYSTEM_TIME
    CONTAINED IN ('2021-01-01 00:00:00.0000000', '2022-01-01 00:00:00.0000000')
WHERE EmployeeID = 1000
ORDER BY ValidFrom;

Na koniec, w niektórych scenariuszach audytu warto zobaczyć, jak cały stół wyglądał w dowolnym momencie przeszłości:

SELECT *
FROM Employee FOR SYSTEM_TIME
    AS OF '2021-01-01 00:00:00.0000000';

Tabele czasowe w wersji systemowej przechowują wartości kolumn okresów w strefie czasowej UTC, ale może się okazać bardziej wygodne pracowanie w lokalnej strefie czasowej, zarówno podczas filtrowania danych, jak i wyświetlania wyników. Poniższy przykład kodu pokazuje, jak zastosować warunek filtrowania, który jest określony w lokalnej strefie czasowej, a następnie konwertowany na UTC za pomocą AT TIME ZONE:

/* Add offset of the local time zone to current time*/
DECLARE @asOf AS DATETIMEOFFSET = GETDATE() AT TIME ZONE 'Pacific Standard Time';

/* Convert AS OF filter to UTC*/
SET @asOf = DATEADD(HOUR, -9, @asOf) AT TIME ZONE 'UTC';

SELECT EmployeeID,
       [Name],
       Position,
       Department,
       [Address],
       [AnnualSalary],
       ValidFrom AT TIME ZONE 'Pacific Standard Time' AS ValidFromPT,
       ValidTo AT TIME ZONE 'Pacific Standard Time' AS ValidToPT
FROM Employee FOR SYSTEM_TIME AS OF @asOf
WHERE EmployeeId = 1000;

Korzystanie z AT TIME ZONE jest przydatne we wszystkich innych scenariuszach, w których są używane tabele z wersjami systemu.

Warunki filtrowania określone za pomocą klauzul czasowych z FOR SYSTEM_TIME są sargowalne.

Note

Termin SARGable w relacyjnych bazach danych oznacza predykat Search ARGumentable, który może wykorzystywać indeks do przyspieszenia wykonywania zapytania. Aby uzyskać więcej informacji, zobacz Sql Server i Azure SQL index architecture and design guide (Architektura i projektowanie indeksów usługi Azure SQL).

Jeśli wysyłasz zapytanie bezpośrednio do tabeli historii, upewnij się, że warunek filtrowania również jest SARGowalny, określając filtry w postaci <period column> { < | > | =, ... } date_condition AT TIME ZONE 'UTC'.

W przypadku zastosowania AT TIME ZONE do kolumn okresów program SQL Server wykonuje skanowanie tabeli lub indeksu, co może być kosztowne. Unikaj tego typu warunku w zapytaniach:

<period column> AT TIME ZONE '<your time zone>' > {< | > | =, ...} date_condition.

Aby uzyskać więcej informacji, zobacz Zapytaj o dane w tabeli czasowej z wersjonowaniem systemowym.

Analiza punktowa w czasie (podróż w czasie)

Zamiast skupiać się na zmianach w poszczególnych rekordach, scenariusze podróży w czasie pokazują, jak całe zbiory danych zmieniają się w czasie. Czasami podróże w czasie obejmują kilka powiązanych tabel czasowych, z których każda zmienia się w niezależnym tempie, które warto przeanalizować:

  • Trendy dotyczące ważnych wskaźników w danych historycznych i bieżących
  • Dokładna migawka wszystkich danych "od" dowolnego punktu w czasie w przeszłości (wczoraj, miesiąc temu itp.)
  • Różnice między dwoma punktami w czasie zainteresowania (miesiąc temu a trzy miesiące temu, na przykład)

Wiele rzeczywistych scenariuszy wymaga analizy podróży w czasie. Aby zobrazować ten scenariusz użytkowania, przyjrzyjmy się przetwarzaniu transakcji online (OLTP) z automatycznie generowaną historią.

OLTP z automatycznie wygenerowaną historią danych

W systemach przetwarzania transakcji można przeanalizować, jak ważne metryki zmieniają się w czasie. Idealnie byłoby, aby analiza historii nie ograniczała wydajności aplikacji OLTP, gdzie dostęp do najnowszego stanu danych musi odbywać się przy minimalnych opóźnieniach i blokadzie danych. Tabele czasowe w wersji systemowej umożliwiają przezroczyste przechowywanie pełnej historii zmian na potrzeby późniejszej analizy, niezależnie od bieżących danych, przy minimalnym wpływie na główne obciążenie OLTP.

W przypadku obciążeń wymagających intensywnego przetwarzania transakcji w programie SQL Server i usłudze Azure SQL Managed Instance zalecamy używanie tabel czasowych wersjonowanych przez system z tabelami zoptymalizowanymi pod kątem pamięci, które umożliwiają przechowywanie bieżących danych w pamięci oraz pełnej historii zmian na dysku w opłacalny sposób.

W przypadku tabeli historii zalecamy użycie klastrowanego indeksu magazynującego kolumny z następujących powodów:

  • Typowe korzyści z analizy trendów wynikają z wydajności zapytań, jaką zapewnia klastrowany indeks Columnstore.

  • Zadanie czyszczenia danych z tabel pamięciowo zoptymalizowanych działa najlepiej przy dużym obciążeniu OLTP, gdy tabela historii ma indeks klastrowanego magazynu kolumn.

  • Indeks klastrowanego magazynu kolumn zapewnia doskonałą kompresję, szczególnie w scenariuszach, w których nie wszystkie kolumny są zmieniane w tym samym czasie.

Korzystanie z tabel temporalnych z OLTP w pamięci zmniejsza potrzebę przechowywania całego zbioru danych w pamięci i pozwala łatwo rozróżnić dane gorące od zimnych.

Przykłady rzeczywistych scenariuszy, które dobrze pasują do tej kategorii, to między innymi zarządzanie zapasami lub handel walutami.

Poniższy diagram przedstawia uproszczony model danych używany do zarządzania zapasami:

Diagram przedstawiający uproszczony model danych używany do zarządzania zapasami.

Poniższy przykład kodu tworzy ProductInventory w pamięci tabelę czasową wersjonowaną przez system, z klastrowanym indeksem pamięci kolumn w tabeli historii (który zastępuje domyślnie utworzony indeks wiersza):

Note

Upewnij się, że baza danych umożliwia tworzenie tabel zoptymalizowanych pod kątem pamięci. Zobacz Tworzenie tabeli Memory-Optimized i tworzenie natywnie skompilowanej procedury składowanej.

USE TemporalProductInventory;
GO

BEGIN

    --If the table is system-versioned, set SYSTEM_VERSIONING to OFF first
    IF ((SELECT temporal_type
        FROM SYS.TABLES
        WHERE object_id = OBJECT_ID('dbo.ProductInventory', 'U')) = 2)
    BEGIN
        ALTER TABLE [dbo].[ProductInventory]
            SET (SYSTEM_VERSIONING = OFF);
    END

    DROP TABLE IF EXISTS [dbo].[ProductInventory];
    DROP TABLE IF EXISTS [dbo].[ProductInventoryHistory];

END
GO

CREATE TABLE [dbo].[ProductInventory]
(
    ProductId INT NOT NULL,
    LocationID INT NOT NULL,
    Quantity INT NOT NULL CHECK (Quantity >= 0),
    ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
    ValidTo DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
    --Primary key definition
    CONSTRAINT PK_ProductInventory PRIMARY KEY NONCLUSTERED (ProductId, LocationId),
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (
    MEMORY_OPTIMIZED = ON,
    SYSTEM_VERSIONING = ON (
        HISTORY_TABLE = [dbo].[ProductInventoryHistory],
        DATA_CONSISTENCY_CHECK = ON
    )
);

CREATE CLUSTERED COLUMNSTORE INDEX IX_ProductInventoryHistory
    ON [ProductInventoryHistory] WITH (DROP_EXISTING = ON);

W przypadku poprzedniego modelu procedura konserwacji spisu może wyglądać następująco:

CREATE PROCEDURE [dbo].[spUpdateInventory] (
    @productId INT,
    @locationId INT,
    @quantityIncrement INT
)
WITH NATIVE_COMPILATION, SCHEMABINDING
AS
BEGIN ATOMIC
WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'English')
    UPDATE dbo.ProductInventory
        SET Quantity = Quantity + @quantityIncrement
    WHERE ProductId = @productId
        AND LocationId = @locationId;

    -- If zero rows were updated then this is an insert
    -- of the new product for a given location
    IF @@rowcount = 0
    BEGIN
        IF @quantityIncrement < 0
        BEGIN
            SET @quantityIncrement = 0;
        END

        INSERT INTO [dbo].[ProductInventory]
        (
            [ProductId],
            [LocationID],
            [Quantity]
        )
        VALUES (
            @productId,
            @locationId,
            @quantityIncrement
        );
    END
END;

Procedura składowana spUpdateInventory wstawia nowy produkt do magazynu lub aktualizuje ilość produktu dla określonej lokalizacji. Logika biznesowa jest prosta i koncentruje się na utrzymywaniu zawsze dokładnego bieżącego stanu przez zwiększanie lub zmniejszanie wartości pola Quantity za pomocą aktualizacji tabeli, podczas gdy tabele wersjonowane systemowo w sposób przezroczysty dodają do danych wymiar historyczny, jak pokazano na poniższym diagramie.

Diagram przedstawiający użycie czasowe z bieżącym użyciem In-Memory i historycznym użyciem w klastrowym magazynie kolumn.

Teraz możesz efektywnie zapytać o najnowszy stan z natywnie skompilowanego modułu:

CREATE PROCEDURE [dbo].[spQueryInventoryLatestState]
WITH NATIVE_COMPILATION, SCHEMABINDING
AS
BEGIN ATOMIC
WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'English')
    SELECT ProductId,
           LocationID,
           Quantity,
           ValidFrom
    FROM dbo.ProductInventory
    ORDER BY ProductId, LocationId;
END;
GO

EXECUTE [dbo].[spQueryInventoryLatestState];

Analizowanie zmian danych w czasie staje się łatwe za pomocą klauzuli FOR SYSTEM_TIME ALL, jak pokazano w poniższym przykładzie:

DROP VIEW IF EXISTS vw_GetProductInventoryHistory;
GO

CREATE VIEW vw_GetProductInventoryHistory AS
    SELECT ProductId,
           LocationId,
           Quantity,
           ValidFrom,
           ValidTo
    FROM [dbo].[ProductInventory] FOR SYSTEM_TIME ALL;
GO

SELECT *
FROM vw_GetProductInventoryHistory
WHERE ProductId = 2;

Na poniższym diagramie przedstawiono historię danych dla jednego produktu, który można łatwo renderować importując poprzedni widok w dodatku Power Query, usłudze Power BI lub podobnym narzędziu do analizy biznesowej:

Diagram przedstawiający historię danych dla jednego produktu.

W tym scenariuszu można użyć tabel czasowych do przeprowadzenia innych analiz podróży w czasie, takich jak rekonstrukcja stanu ekwipunku AS OF w dowolnym momencie w przeszłości lub porównanie migawek należących do różnych momentów w czasie.

W tym scenariuszu można również rozszerzyć tabele Product i Location do tabel czasowych, umożliwiając późniejszą analizę historii zmian UnitPrice i NumberOfEmployee.

ALTER TABLE Product
ADD ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN
        CONSTRAINT DF_ValidFrom DEFAULT DATEADD(SECOND, -1, SYSUTCDATETIME()),
    ValidTo DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN
        CONSTRAINT DF_ValidTo DEFAULT '9999.12.31 23:59:59.99',
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);

ALTER TABLE Product
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ProductHistory));

ALTER TABLE [Location]
ADD
    ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN
        CONSTRAINT DFValidFrom DEFAULT DATEADD(SECOND, -1, SYSUTCDATETIME()),
    ValidTo DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN
        CONSTRAINT DFValidTo DEFAULT '9999.12.31 23:59:59.99',
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);

ALTER TABLE [Location]
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.LocationHistory));

Ponieważ model danych obejmuje teraz wiele tabel czasowych, najlepszą praktyką AS OF analizy jest stworzenie widoku, który wyodrębnia niezbędne dane z powiązanych tabel i zastosuje FOR SYSTEM_TIME AS OF je do widoku, co znacznie upraszcza rekonstrukcję stanu całego modelu danych:

DROP VIEW IF EXISTS vw_ProductInventoryDetails;
GO

CREATE VIEW vw_ProductInventoryDetails
AS
SELECT PrInv.ProductId,
       PrInv.LocationId,
       p.ProductName,
       l.LocationName,
       PrInv.Quantity,
       p.UnitPrice,
       l.NumberOfEmployees,
       p.ValidFrom AS ProductStartTime,
       p.ValidTo AS ProductEndTime,
       l.ValidFrom AS LocationStartTime,
       l.ValidTo AS LocationEndTime,
       PrInv.ValidFrom AS InventoryStartTime,
       PrInv.ValidTo AS InventoryEndTime
FROM dbo.ProductInventory AS PrInv
    INNER JOIN dbo.Product AS p
        ON PrInv.ProductId = p.ProductID
    INNER JOIN dbo.Location AS l
        ON PrInv.LocationId = l.LocationID;
GO

SELECT *
FROM vw_ProductInventoryDetails
    FOR SYSTEM_TIME AS OF '2022-01-01';

Poniższy zrzut ekranu przedstawia plan wykonania wygenerowany dla zapytania SELECT. Pokazuje to, że aparat bazy danych obsługuje całą złożoność podczas pracy z relacjami czasowymi:

Diagram przedstawiający plan wykonania wygenerowany dla zapytania

Użyj poniższego kodu, aby porównać stan zapasów produktów pomiędzy dwoma punktami w czasie (dzień temu i miesiąc temu):

DECLARE @dayAgo AS DATETIME2 = DATEADD(DAY, -1, SYSUTCDATETIME());
DECLARE @monthAgo AS DATETIME2 = DATEADD(MONTH, -1, SYSUTCDATETIME());

SELECT inventoryDayAgo.ProductId,
       inventoryDayAgo.ProductName,
       inventoryDayAgo.LocationName,
       inventoryDayAgo.Quantity AS QuantityDayAgo,
       inventoryMonthAgo.Quantity AS QuantityMonthAgo,
       inventoryDayAgo.UnitPrice AS UnitPriceDayAgo,
       inventoryMonthAgo.UnitPrice AS UnitPriceMonthAgo
FROM vw_ProductInventoryDetails FOR SYSTEM_TIME AS OF @dayAgo AS inventoryDayAgo
     INNER JOIN vw_ProductInventoryDetails FOR SYSTEM_TIME AS OF @monthAgo AS inventoryMonthAgo
         ON inventoryDayAgo.ProductId = inventoryMonthAgo.ProductId
        AND inventoryDayAgo.LocationId = inventoryMonthAgo.LocationID;

Wykrywanie anomalii

Wykrywanie anomalii, czyli wykrywanie wartości odstających, identyfikuje elementy, które nie spełniają oczekiwanego wzorca lub inne elementy zbioru danych. Możesz użyć systemowych tabel czasowych do wykrywania anomalii występujących okresowo lub nieregularnie, stosując zapytania czasowe do szybkiego lokalizowania określonych wzorców. To, co jest anomalią, zależy od rodzaju zbieranych danych oraz logiki biznesowej.

W poniższym przykładzie przedstawiono uproszczoną logikę wykrywania "skoków" w liczbach sprzedaży. Załóżmy, że pracujesz z tabelą czasową, która zbiera historię zakupionych produktów:

CREATE TABLE [dbo].[Product]
(
    [ProdID] INT NOT NULL PRIMARY KEY CLUSTERED,
    [ProductName] VARCHAR (100) NOT NULL,
    [DailySales] INT NOT NULL,
    [ValidFrom] DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
    [ValidTo] DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME ([ValidFrom], [ValidTo])
)
WITH (
    SYSTEM_VERSIONING = ON (
        HISTORY_TABLE = [dbo].[ProductHistory],
        DATA_CONSISTENCY_CHECK = ON
    )
);

Na poniższym diagramie przedstawiono zakupy w czasie:

Diagram przedstawiający zakupy w czasie.

Przy założeniu, że w zwykłe dni liczba zakupionych produktów ma niewielką wariancję, następujące zapytanie identyfikuje pojedyncze wartości odstające: próbki, których różnice w porównaniu do bezpośrednich sąsiadów są znaczące (dwukrotnie większe), podczas gdy sąsiednie próbki nie różnią się znacząco (mniej niż 20%):

WITH CTE (ProdId, PrevValue, CurrentValue, NextValue, ValidFrom, ValidTo)
AS (SELECT ProdId,
           LAG(DailySales, 1, 1) OVER (PARTITION BY ProdId ORDER BY ValidFrom) AS PrevValue,
           DailySales,
           LEAD(DailySales, 1, 1) OVER (PARTITION BY ProdId ORDER BY ValidFrom) AS NextValue,
           ValidFrom,
           ValidTo
    FROM Product FOR SYSTEM_TIME ALL)
SELECT ProdId,
       PrevValue,
       CurrentValue,
       NextValue,
       ValidFrom,
       ValidTo,
       ABS(PrevValue - NextValue) / CONVERT (FLOAT, (CASE WHEN NextValue > PrevValue THEN PrevValue ELSE NextValue END)) AS PrevToNextDiff,
       ABS(CurrentValue - PrevValue) / CONVERT (FLOAT, (CASE WHEN CurrentValue > PrevValue THEN PrevValue ELSE CurrentValue END)) AS CurrentToPrevDiff,
       ABS(CurrentValue - NextValue) / CONVERT (FLOAT, (CASE WHEN CurrentValue > NextValue THEN NextValue ELSE CurrentValue END)) AS CurrentToNextDiff
FROM CTE
WHERE ABS(PrevValue - NextValue) / (CASE WHEN NextValue > PrevValue THEN PrevValue ELSE NextValue END) < 0.2
      AND ABS(CurrentValue - PrevValue) / (CASE WHEN CurrentValue > PrevValue THEN PrevValue ELSE CurrentValue END) > 2
      AND ABS(CurrentValue - NextValue) / (CASE WHEN CurrentValue > NextValue THEN NextValue ELSE CurrentValue END) > 2;

Note

Ten przykład jest celowo uproszczony. W scenariuszach produkcyjnych prawdopodobnie użyjesz zaawansowanych metod statystycznych, aby zidentyfikować próbki, które nie są zgodne ze wspólnym wzorcem.

Powolne zmienianie wymiarów

Wymiary magazynowania danych zwykle zawierają stosunkowo statyczne dane dotyczące jednostek, takich jak lokalizacje geograficzne, klienci lub produkty. Jednak niektóre scenariusze wymagają śledzenia zmian danych w tabelach wymiarów. Ponieważ zmiany wymiarów zachodzą znacznie rzadziej, w sposób nieprzewidywalny i poza regularnym harmonogramem aktualizacji obowiązującym w tabelach faktów, tego typu tabele wymiarowe nazywane są powoli zmieniającymi się wymiarami (SCD).

Istnieje kilka kategorii powoli zmieniających się wymiarów w zależności od tego, jak historia zmian jest zachowana:

Typ wymiaru Szczegóły
Typ 0 Historia nie jest zachowywana. Atrybuty wymiaru odzwierciedlają oryginalne wartości.
Typ 1 Atrybuty wymiaru odzwierciedlają najnowsze wartości (poprzednie wartości są zastępowane)
Typ 2 Każda wersja członka wymiaru jest reprezentowana jako oddzielny wiersz w tabeli, zwykle z kolumnami, które przedstawiają okres ważności.
Typ 3 Utrzymywanie ograniczonej historii wybranych atrybutów przy użyciu dodatkowych kolumn w tym samym wierszu
Typ 4 Przechowywanie historii w oddzielnej tabeli, podczas gdy oryginalna tabela wymiarów przechowuje najnowsze (bieżące) wersje elementów członkowskich wymiaru

Kiedy wybierasz strategię SCD, odpowiedzialność leży po stronie warstwy ETL (Extract-Transform-Load), aby utrzymać dokładność tabel wymiarów, co zwykle wymaga bardziej złożonego kodu i dodatkowej konserwacji.

Możesz użyć systemowych tabel czasowych, aby znacząco obniżyć złożoność kodu, ponieważ historia danych jest automatycznie zachowywana. Biorąc pod uwagę implementację przy użyciu dwóch tabel, tabele czasowe znajdują się najbliżej typu 4 SCD. Jednak ponieważ zapytania czasowe umożliwiają odwołanie tylko do bieżącej tabeli, można również rozważyć tabele czasowe w środowiskach, w których planujesz używać typu 2 SCD.

Aby przekształcić zwykły wymiar w SCD, możesz utworzyć nowy lub zmodyfikować istniejący tak, aby stał się tabelą czasową z wersjonowaniem systemowym. Jeśli Twoja istniejąca tabela wymiarowa zawiera dane historyczne, stwórz osobną tabelę i przenieś tam dane historyczne, a aktualne (rzeczywiste) wersje wymiarów zachowaj w oryginalnej tabeli wymiarowej. Następnie użyj składni ALTER TABLE, aby przekonwertować tabelę wymiarów na tabelę czasową z wersją systemową ze wstępnie zdefiniowaną tabelą historii.

Poniższy przykład ilustruje proces i zakłada, że tabela DimLocation wymiarowa już zawiera ValidFrom oraz ValidTo jako datetime2 kolumny nieuważnialne, które wypełnia proces ETL:

  • Przenieś wersje zamkniętych wierszy do nowej tabeli historii:

    SELECT *
INTO DimLocationHistory
FROM DimLocation
WHERE ValidTo < '9999-12-31 23:59:59.99';
GO

  • Utwórz klastrowany indeks magazynu kolumnowego, co jest dobrym wyborem w scenariuszach związanych z hurtownią danych:

    CREATE CLUSTERED COLUMNSTORE INDEX IX_DimLocationHistory
    ON DimLocationHistory;

  • Usuń wcześniejsze wersje z DimLocation, które stają się aktualną tabelą w konfiguracji wersjonowania systemu czasowego:

    DELETE FROM DimLocation
WHERE ValidTo < '9999-12-31 23:59:59.99';

  • Dodaj definicję okresu:

    ALTER TABLE DimLocation
    ADD PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);

  • Włącz wersjonowanie systemowe i powiąż tabelę historii z DimLocation:

    ALTER TABLE DimLocation
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DimLocationHistory));

Nie potrzebujesz dodatkowego kodu, aby obsługiwać SCD podczas procesu ładowania hurtowni danych po jego utworzeniu.

Poniższy ilustrator pokazuje, jak można użyć tabel czasowych w podstawowym scenariuszu obejmującym dwa SCD (DimLocation i DimProduct) oraz jedną tabelę faktów.

Diagram przedstawiający sposób używania tabel czasowych w prostym scenariuszu obejmującym 2 dyski SCD (DimLocation i DimProduct) oraz jedną tabelę faktów.

Aby używać poprzednich SCD w raportach, musisz odpowiednio dostosować sposób wykonywania zapytań. Na przykład możesz chcieć obliczyć łączną kwotę sprzedaży i średnią liczbę sprzedanych produktów na mieszkańca w ciągu ostatnich sześciu miesięcy. Obie metryki wymagają korelacji danych z tabeli faktów i wymiarów, które mogły zmienić ich atrybuty ważne dla analizy (DimLocation.NumOfCustomers, DimProduct.UnitPrice).

Następujące zapytanie prawidłowo oblicza wymagane metryki:

DECLARE @now AS DATETIME2 = SYSUTCDATETIME();
DECLARE @sixMonthsAgo AS DATETIME2;

SET @sixMonthsAgo = DATEADD(month, -12, SYSUTCDATETIME());

SELECT DimProduct_History.ProductId,
       DimLocation_History.LocationId,
       SUM(f.Quantity * DimProduct_History.UnitPrice) AS TotalAmount,
       AVG(f.Quantity / DimLocation_History.NumOfCustomers) AS AverageProductsPerCapita
FROM FactProductSales AS f
     /* find corresponding record in SCD history in last 6 months, based on matching fact */
     INNER JOIN DimLocation FOR SYSTEM_TIME BETWEEN @sixMonthsAgo AND @now AS DimLocation_History
         ON DimLocation_History.LocationId = f.LocationId
        AND f.FactDate BETWEEN DimLocation_History.ValidFrom AND DimLocation_History.ValidTo
     /* find corresponding record in SCD history in last 6 months, based on matching fact */
     INNER JOIN DimProduct FOR SYSTEM_TIME BETWEEN @sixMonthsAgo AND @now AS DimProduct_History
         ON DimProduct_History.ProductId = f.ProductId
        AND f.FactDate BETWEEN DimProduct_History.ValidFrom AND DimProduct_History.ValidTo
WHERE f.FactDate BETWEEN @sixMonthsAgo AND @now
GROUP BY DimProduct_History.ProductId, DimLocation_History.LocationId;

Considerations

Stosowanie systemowych tabel czasowych dla SCD jest akceptowalne, jeśli okres ważności obliczany na podstawie czasu transakcji w bazie danych pasuje do logiki Twojej firmy. Jeśli ładujesz dane z dużym opóźnieniem, czas transakcji może być nieakceptowalny.

Domyślnie tabele czasowe w wersji systemowej nie zezwalają na zmianę danych historycznych po załadowaniu (można zmodyfikować historię po ustawieniu SYSTEM_VERSIONING na OFF). Może to być ograniczenie w przypadkach, w których zmiana danych historycznych odbywa się regularnie.

Tabele wersjonowane przez system czasowy generują wersję wierszową przy każdej zmianie kolumny. Jeśli chcesz ograniczyć nowe wersje przy zmianie określonej kolumny, musisz uwzględnić to ograniczenie w logice ETL.

Jeśli spodziewasz się znacznej liczby historycznych wierszy w tabelach SCD, rozważ użycie klastrowanego indeksu columnstore jako głównej opcji przechowywania tabeli historii. Użycie indeksu magazynu kolumn zmniejsza rozmiar tabeli historii i przyspiesza wykonywanie zapytań analitycznych.

Naprawianie uszkodzenia danych na poziomie wiersza

Możesz polegać na danych historycznych w tabelach czasowych z wersjonowaniem systemowym, aby szybko przywrócić poszczególne wiersze do dowolnego z wcześniej zapamiętanych stanów. Ta właściwość tabel czasowych jest przydatna, gdy można zlokalizować wiersze, których dotyczy problem, i/lub gdy znasz czas niepożądanej zmiany danych. Ta wiedza umożliwia wydajną naprawę bez konieczności obsługi kopii zapasowych.

Takie podejście ma kilka zalet:

  • Możesz dokładnie kontrolować zakres naprawy. Rekordy, których nie dotyczy problem, muszą pozostawać w stanie najnowszym, co często jest wymaganiem krytycznym.

  • Operacja jest wydajna, a baza danych pozostaje w trybie online dla wszystkich obciążeń korzystających z danych.

  • Sama operacja naprawy jest wersjonowana. Masz ścieżkę audytową operacji naprawczej, więc możesz analizować, co się wydarzyło później, jeśli zajdzie taka potrzeba.

Możesz zautomatyzować naprawę z względną łatwością. Poniższy przykład kodu pokazuje procedurę przechowywaną, która wykonuje naprawę danych dla tabeli Employee używanej w scenariuszu audytu danych.

DROP PROCEDURE IF EXISTS sp_RepairEmployeeRecord;
GO

CREATE PROCEDURE sp_RepairEmployeeRecord (
    @EmployeeID INT,
    @versionNumber INT = 1
)
AS
WITH History
AS (
    /* Order historical rows by their age in DESC order*/
    SELECT ROW_NUMBER() OVER (PARTITION BY EmployeeID
        ORDER BY [ValidTo] DESC) AS RN,
        *
    FROM Employee FOR SYSTEM_TIME ALL
    WHERE YEAR(ValidTo) < 9999
          AND Employee.EmployeeID = @EmployeeID)

/* Update current row using N-th row version from history
(default is 1, that is, the last version) */
UPDATE Employee
    SET [Position] = h.[Position],
        [Department] = h.Department,
        [Address] = h.[Address],
        AnnualSalary = h.AnnualSalary
FROM Employee AS e
     INNER JOIN History AS h
         ON e.EmployeeID = h.EmployeeID
        AND RN = @versionNumber
WHERE e.EmployeeID = @EmployeeID;

Ta procedura składowana przyjmuje @EmployeeID i @versionNumber jako parametry wejściowe. Domyślnie przywraca stan wiersza do ostatniej wersji z historii (@versionNumber = 1).

Poniższy obraz pokazuje stan rzędu przed i po inwokacji procedury. Czerwony prostokąt oznacza aktualną wersję wiersza, która jest nieprawidłowa, natomiast zielony prostokąt oznacza poprawną wersję z historii.

Zrzut ekranu przedstawiający stan wiersza przed i po wywołaniu procedury. 

EXECUTE sp_RepairEmployeeRecord
    @EmployeeID = 1,
    @versionNumber = 1;

Zrzut ekranu przedstawiający poprawiony wiersz.

Tę procedurę składowaną naprawy można zdefiniować tak, aby akceptowała dokładny znacznik czasu zamiast wersji wiersza. Przywraca wiersz do dowolnej wersji, która była aktywna w określonym momencie czasu (czyli w momencie AS OF).

DROP PROCEDURE IF EXISTS sp_RepairEmployeeRecordAsOf;
GO

CREATE PROCEDURE sp_RepairEmployeeRecordAsOf (
    @EmployeeID INT,
    @asOf DATETIME2
)
AS
/* Update current row to the state that was actual AS OF provided date*/
UPDATE Employee
    SET [Position] = History.[Position],
        [Department] = History.Department,
        [Address] = History.[Address],
        AnnualSalary = History.AnnualSalary
FROM Employee AS e
     INNER JOIN Employee FOR SYSTEM_TIME AS OF @asOf AS History
         ON e.EmployeeID = History.EmployeeID
WHERE e.EmployeeID = @EmployeeID;

W przypadku tego samego przykładu danych na poniższej ilustracji przedstawiono scenariusz naprawy z warunkiem czasu. Wyróżnione są parametry @asOf , wybrany wiersz w historii, który był aktualny w danym momencie, oraz nowa wersja wiersza w bieżącej tabeli po operacji naprawczej:

Zrzut ekranu przedstawiający scenariusz naprawy z warunkiem czasu.

Korekta danych może stać się częścią zautomatyzowanego ładowania danych w systemach magazynowania i raportowania danych. Jeśli nowo zaktualizowana wartość nie jest poprawna, to w wielu scenariuszach przywrócenie poprzedniej wersji z historii jest wystarczającym rozwiązaniem. Na poniższym diagramie pokazano, jak można zautomatyzować ten proces:

Diagram przedstawiający sposób automatyzacji procesu.

Treści powiązane

  • Last updated on