Mengkueri data dalam tabel temporal versi sistem

Berlaku untuk: SQL Server 2016 (13.x) dan versi yang lebih baru Azure SQL DatabaseAzure SQL Managed InstanceDatabase SQL di Microsoft Fabric

Untuk mendapatkan status data terbaru (saat ini) dalam tabel temporal, query dengan cara yang sama seperti tabel non-temporal. Jika kolom PERIOD tidak disembunyikan, nilainya muncul dalam kueri SELECT *. Jika Anda menentukan PERIOD kolom sebagai HIDDEN, nilainya tidak muncul dalam SELECT * kueri. Saat kolom PERIOD disembunyikan, rujuk kolom tersebut secara spesifik dalam klausa SELECT agar nilainya dikembalikan.

Untuk melakukan analisis berbasis waktu, gunakan klausa dengan FOR SYSTEM_TIME empat subklausa spesifik temporal untuk melakukan query data di tabel saat ini dan riwayat. Untuk informasi selengkapnya tentang klausul tersebut, lihat Tabel temporal dan klausa FROM ditambah 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

Anda dapat menentukan FOR SYSTEM_TIME secara independen untuk setiap tabel dalam sebuah kueri. Gunakan di dalam ekspresi tabel umum, fungsi bernilai tabel, dan prosedur tersimpan. Saat Anda menggunakan alias tabel dengan tabel temporal, sertakan klausa FOR SYSTEM_TIME di antara nama tabel temporal dan aliasnya (lihat contoh kedua Kueri untuk waktu tertentu menggunakan subklausa AS OF).

Melakukan kueri pada waktu tertentu menggunakan AS OF subklausa

Gunakan subklausa AS OF untuk merekonstruksi keadaan data sebagaimana adanya pada waktu tertentu di masa lalu. Anda dapat merekonstruksi data dengan presisi tipe datetime2 yang Anda tentukan dalam PERIOD definisi kolom.

Gunakan AS OF subklausa dengan literal atau variabel konstan untuk menentukan kondisi waktu secara dinamis. Nilai yang Anda berikan diartikan sebagai waktu UTC.

Contoh pertama ini mengembalikan status dbo.Department tabel AS OF pada tanggal tertentu di masa lalu.

-- 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';

Contoh kedua ini membandingkan nilai antara dua titik waktu untuk subset baris.

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;

Menggunakan tampilan dengan AS OF subklausa dalam kueri temporal

Tampilan berguna saat Anda membutuhkan analisis titik waktu yang kompleks. Contoh umum adalah membuat laporan bisnis hari ini dengan nilai-nilai bulan sebelumnya.

Biasanya, pelanggan memiliki model database yang dinormalisasi, yang melibatkan banyak tabel dengan hubungan kunci asing. Mengetahui bagaimana data dari model yang dinormalisasi itu terlihat pada suatu titik di masa lalu bisa menjadi tantangan, karena semua tabel berubah secara independen dengan ritmenya masing-masing.

Dalam hal ini, opsi terbaik adalah membuat pandangan dan menerapkan subklausa AS OF ke seluruh pandangan. Pendekatan ini memisahkan pemodelan lapisan akses data dari analisis pada titik waktu tertentu, karena SQL Server secara transparan menerapkan klausa AS OF pada semua tabel temporal yang berpartisipasi dalam definisi tampilan. Selain itu, Anda dapat menggabungkan temporal dengan tabel non-temporal dalam tampilan yang sama dan AS OF hanya diterapkan pada tabel temporal. Jika tampilan tidak mereferensikan setidaknya satu tabel temporal, penerapan klausa kueri temporal padanya menghasilkan kesalahan.

Kode sampel berikut membuat tampilan yang menggabungkan tiga tabel temporal: Department, , CompanyLocationdan 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

Anda bisa mengajukan kueri tampilan menggunakan subklaus AS OF serta literal datetime2:

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

Kueri untuk perubahan pada baris tertentu dari waktu ke waktu

Subklausa temporal FROM ... TO, BETWEEN ... AND, dan CONTAINED IN berguna saat Anda perlu mengambil seluruh perubahan historis untuk baris tertentu pada tabel saat ini (juga dikenal sebagai audit data).

Dua subklausa pertama mengembalikan versi baris yang tumpang tindih dengan periode tertentu (yaitu, yang dimulai sebelum periode tertentu dan berakhir setelahnya), sementara CONTAINED IN hanya mengembalikan yang ada dalam batas periode yang ditentukan.

Jika Anda hanya mencari versi baris yang tidak saat ini, kueri langsung tabel riwayat untuk performa kueri terbaik. Gunakan ALL untuk melakukan kueri data saat ini dan historis tanpa batasan apa pun.

/* 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;

Untuk merangkum bagaimana data terlihat selama periode tertentu, gabungkan FOR SYSTEM_TIME BETWEEN ... AND dengan GROUP BY dan agregasi fungsi. Gunakan teknik ini untuk statistik deskriptif dan analisis tren. Ini merangkum semua versi baris yang aktif selama periode tersebut, alih-alih merekonstruksi satu titik waktu tertentu.

Contoh berikut menghitung statistik deskriptif untuk gaji karyawan dalam jendela waktu, dikelompokkan berdasarkan departemen. Ini menggunakan tabel berversi sistem Employee yang didefinisikan dalam tabel temporal dan skenario penggunaan tabel temporal. Kolom AnnualSalary adalah ukuran numerik yang sangat cocok untuk fungsi AVG, MIN, MAX, dan STDEV.

Klausa FOR SYSTEM_TIME BETWEEN ... AND mengembalikan setiap versi baris yang tumpang tindih dengan periode. Sebagai hasilnya, agregat tersebut mencakup setiap gaji yang berlaku selama jendela tersebut. Ketika gaji karyawan berubah, kueri mengembalikan beberapa versi untuk karyawan tersebut, dan setiap versi berkontribusi pada agregat.

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;

Konten terkait

  • Last updated on