Adatok lekérdezése rendszerverziójú időtáblában

Vonatkozik a következőkre: SQL Server 2016 (13.x) és későbbi verziók Azure SQL DatabaseAzure SQL Managed InstanceSQL database in Microsoft Fabric

Az időbeli táblában a legfrissebb (jelenlegi) adatállapot megkapásához ugyanúgy kérdünk le, mint egy nem időbeli táblát. Ha a PERIOD oszlopok nem rejtve vannak, az értékek egy SELECT * lekérdezésben jelennek meg. Ha a(z) PERIOD oszlopokat HIDDENként adja meg, az értékeik nem jelennek meg a(z) SELECT * lekérdezésben. Ha az PERIOD oszlopok el vannak rejtve, hivatkozz rájuk kifejezetten a SELECT záradékban, hogy visszaadd az értékeiket.

Az időalapú elemzéshez használjuk a FOR SYSTEM_TIME négy időspecifikus alklauzulatot tartalmazó záradékot az aktuális és a történeti táblák közötti lekérdezésre. További információ ezekről a feltételekről: Temporális táblák és FROM feltétel, valamint a JOIN, APPLY, PIVOT

  • AS OF <date_time>
  • FROM <start_date_time> TO <end_date_time>
  • BETWEEN <start_date_time> AND <end_date_time>
  • CONTAINED IN (<start_date_time>, <end_date_time>)
  • ALL

Minden táblára külön-külön meg lehet határozni FOR SYSTEM_TIME egy lekérdezésben. Használd a szokásos táblázatkifejezésekben, táblázatértékű függvényekben és tárolt eljárásokban. Amikor egy táblaaliast temporális táblával használsz, illeszd be a FOR SYSTEM_TIME záradékot a temporális tábla neve és az alias közé (lásd: Lekérdezés egy adott időpontra a AS OF alzáradék használatával című szakasz második példája).

Adott időre vonatkozó lekérdezés a AS OF alklám használatával

Használd az AS OF alfejezetet az adatok állapotának rekonstruálására, ahogyan az bármely adott időben a múltban volt. Az adatokat rekonstruálhatod a datetime2 típus pontosságával, amelyet az PERIOD oszlopdefiníciókban megadtál.

Használd az AS OF állandó literálisokat vagy változókat tartalmazó alklauszát az időfeltétel dinamikusan meghatározásához. Az ön által megadott értékeket UTC időként értelmezik.

Ez az első példa a AS OF tábla dbo.Department állapotát adja vissza egy adott múltbeli időpontban.

-- State of entire table AS OF specific date in the past
SELECT [DeptID],
       [DeptName],
       [ValidFrom],
       [ValidTo]
FROM [dbo].[Department] FOR SYSTEM_TIME
    AS OF '2021-09-01 T10:00:00.7230011';

Ez a második példa két időpont közötti értékeket hasonlít össze a sorok egy részhalmazához.

DECLARE @ADayAgo AS DATETIME2;

SET @ADayAgo = DATEADD(DAY, -1, SYSUTCDATETIME());

-- Comparison between two points in time for subset of rows
SELECT D_1_Ago.DeptID,
       d.DeptID,
       D_1_Ago.DeptName,
       d.DeptName,
       D_1_Ago.ValidFrom,
       d.ValidFrom,
       D_1_Ago.ValidTo,
       d.ValidTo
FROM dbo.Department FOR SYSTEM_TIME
    AS OF @ADayAgo AS D_1_Ago
    INNER JOIN Department AS d
         ON D_1_Ago.DeptID = d.DeptID
        AND D_1_Ago.DeptID BETWEEN 1 AND 5;

Nézetek használata AS OF részklám használatával időbeli lekérdezésekben

A nézetek hasznosak, ha összetett, adott időpontra vonatkozó elemzésre van szükség. Egy gyakori példa, ha ma egy üzleti jelentést készítünk az előző havi értékekkel.

Az ügyfelek általában normalizált adatbázismodellel rendelkeznek, amely sok, idegenkulcs-kapcsolattal rendelkező táblát tartalmaz. Nehéz lehet megállapítani, hogy a normalizált modell adatai hogyan néztek ki egy korábbi időpontban, mert minden tábla egymástól függetlenül, a saját ütemében változik.

Ebben az esetben a legjobb megoldás, ha létrehoz egy nézetet, és alkalmazza a AS OF alklámot a teljes nézetre. Ez a megközelítés elválasztja az adathozzáférési réteg modellezését a pont-in-time elemzéstől, mivel az SQL Server átláthatóan alkalmazza a AS OF záradékot minden időbeli táblára, amely részt vesz a nézet meghatározásában. Emellett az időbeli és a nem időbeli táblákat is kombinálhatja ugyanabban a nézetben, és a AS OF csak az időbeli táblákra lesz alkalmazva. Ha a nézet nem hivatkozik legalább egy temporális táblára, az időbeli lekérdezési záradékok alkalmazása hiba miatt meghiúsul.

A következő mintakód egy nézetet hoz létre, amely három időbeli táblát illeszt össze: Department, CompanyLocationés LocationDepartments:

CREATE VIEW [dbo].[vw_GetOrgChart]
AS
SELECT [CompanyLocation].LocID,
       [CompanyLocation].LocName,
       [CompanyLocation].City,
       [Department].DeptID,
       [Department].DeptName
FROM [dbo].[CompanyLocation]
    LEFT OUTER JOIN [dbo].[LocationDepartments]
        ON [CompanyLocation].LocID = LocationDepartments.LocID
    LEFT OUTER JOIN [dbo].[Department]
        ON LocationDepartments.DeptID = [Department].DeptID;
GO

A nézetet a AS OF alcikkely és egy datetime2 literál használatával kérdezheti le.

/* Query view AS OF */
SELECT *
FROM [vw_GetOrgChart] FOR SYSTEM_TIME
    AS OF '2021-09-01 T10:00:00.7230011';

Adott sorok időbeli változásainak lekérdezése

Az időbeli almellékszálak FROM ... TO, BETWEEN ... AND, és CONTAINED IN hasznosak, amikor minden történelmi változást meg kell szerezni egy adott sorra a jelenlegi táblázatban (más néven adataudit).

Az első két almondat olyan sorverziókat ad, amelyek átfednek egy meghatározott időszakkal (azaz azokat, amelyek az adott időszak előtt kezdődtek és utána érnek véget), míg CONTAINED IN csak azokat adják vissza, amelyek a megadott időszak határain belül léteztek.

Ha csak a nem aktuális sorverziókra keresel, a lehető legjobb lekérdezési teljesítmény érdekében közvetlenül a történeti táblát kérdezd le. Használd ALL a jelenlegi és történelmi adatok lekérdezésére korlátozások nélkül.

/* Query using BETWEEN...AND sub-clause*/
SELECT [DeptID],
       [DeptName],
       [ValidFrom],
       [ValidTo],
       IIF (YEAR(ValidTo) = 9999, 1, 0) AS IsActual
FROM [dbo].[Department] FOR SYSTEM_TIME
    BETWEEN '2021-01-01' AND '2021-12-31'
WHERE DeptId = 1
ORDER BY ValidFrom DESC;

/* Query using CONTAINED IN sub-clause */
SELECT [DeptID],
       [DeptName],
       [ValidFrom],
       [ValidTo]
FROM [dbo].[Department] FOR SYSTEM_TIME
    CONTAINED IN ('2021-04-01', '2021-09-25')
WHERE DeptId = 1
ORDER BY ValidFrom DESC;

/* Query using ALL sub-clause */
SELECT [DeptID],
       [DeptName],
       [ValidFrom],
       [ValidTo],
       IIF (YEAR(ValidTo) = 9999, 1, 0) AS IsActual
FROM [dbo].[Department] FOR SYSTEM_TIME ALL
ORDER BY [DeptID], [ValidFrom] DESC;

Összefoglalva, hogyan néznek ki az adatok egy időszakban, kombináljuk FOR SYSTEM_TIME BETWEEN ... ANDGROUP BY és aggregáljuk a függvényeket. Ezt a technikát használd leíró statisztikához és trendelemzéshez. Minden olyan sorverziót összesít, amely az adott időablak során aktív volt, ahelyett, hogy egyetlen időpont állapotát rekonstruálná.

A következő példa a munkavállalói fizetések leíró statisztikákat számítja ki egy időablakon belül, osztályok szerint csoportosítva. A Employee rendszer-verzióban meghatározott táblázatot használja, amelyet az Időbeli táblák és az Időbeli táblázat használati forgatókönyvek határoztak meg. Az AnnualSalary oszlop egy numerikus mérték, amely jól illeszkedik a AVG, MIN, MAX, és STDEV függvényekhez.

A FOR SYSTEM_TIME BETWEEN ... AND záradék minden olyan sorverziót visszaad, amely átfedi az időszakot. Ennek eredményeként az aggregátumok minden olyan fizetést tartalmaznak, amely az ablak alatt érvényes volt. Amikor egy alkalmazott fizetése változik, a lekérdezés több verziót is visszaad az adott alkalmazotthoz, és mindegyik verzió hozzájárul az aggregációkhoz.

DECLARE @periodStart AS DATETIME2 = '2021-01-01';
DECLARE @periodEnd AS DATETIME2 = '2021-12-31';

SELECT [Department],
       AVG([AnnualSalary]) AS AvgSalary,
       MIN([AnnualSalary]) AS MinSalary,
       MAX([AnnualSalary]) AS MaxSalary,
       STDEV([AnnualSalary]) AS SalaryStdDev,
       COUNT(*) AS RowVersions
FROM [dbo].[Employee] FOR SYSTEM_TIME
    BETWEEN @periodStart AND @periodEnd
GROUP BY [Department]
ORDER BY AvgSalary DESC;

Kapcsolódó tartalom

  • Last updated on