Bagikan melalui

Mengkueri data dalam tabel temporal versi sistem

  • Artikel

Berlaku untuk: SQL Server 2016 (13.x) dan Azure SQL Database Azure SQL Managed Instance yang lebih baru

Saat Anda ingin mendapatkan status data terbaru (saat ini) dalam tabel temporal, Anda bisa mengkueri dengan cara yang sama seperti Anda mengkueri tabel non-temporal. PERIOD Jika kolom tidak disembunyikan, nilainya muncul dalam SELECT * kueri. Jika Anda menentukan PERIOD kolom sebagai HIDDEN, nilainya tidak muncul dalam SELECT * kueri. PERIOD Saat kolom disembunyikan, Anda harus mereferensikan PERIOD kolom secara khusus dalam SELECT klausul untuk mengembalikan nilai untuk kolom ini.

Untuk melakukan semua jenis analisis berbasis waktu, gunakan klausul baru FOR SYSTEM_TIME dengan empat subklaus khusus temporal untuk mengkueri data di seluruh tabel saat ini dan riwayat. Untuk informasi selengkapnya tentang klausul ini, 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

FOR SYSTEM_TIME dapat ditentukan secara independen untuk setiap tabel dalam kueri. Ini dapat digunakan di dalam ekspresi tabel umum, fungsi bernilai tabel, dan prosedur tersimpan. Saat menggunakan alias tabel dengan tabel temporal, FOR SYSTEM_TIME klausa harus disertakan antara nama tabel temporal dan alias (lihat Kueri untuk waktu tertentu menggunakan AS OF contoh kedua subklasifikasi ).

Kueri untuk waktu tertentu menggunakan AS OF subklaus

AS OF Gunakan subklasul saat Anda perlu membangun ulang status data seperti pada waktu tertentu di masa lalu. Anda dapat menyusun ulang data dengan presisi jenis datetime2 yang ditentukan dalam PERIOD definisi kolom.

AS OF Subklaus dapat digunakan dengan literal konstanta atau dengan variabel, sehingga Anda dapat menentukan kondisi waktu secara dinamis. Nilai yang disediakan ditafsirkan sebagai waktu UTC.

Contoh pertama ini mengembalikan status dbo. Tabel departemen AS OF 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 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 subklaus dalam kueri temporal

Menggunakan tampilan berguna dalam skenario ketika analisis waktu titik waktu yang kompleks diperlukan. Contoh umum adalah membuat laporan bisnis hari ini dengan nilai untuk bulan sebelumnya.

Biasanya, pelanggan memiliki model database yang dinormalisasi, yang melibatkan banyak tabel dengan hubungan kunci asing. Mencari tahu bagaimana data dari model yang dinormalisasi melihat titik di masa lalu dapat menjadi tantangan, karena semua tabel berubah secara independen pada irama mereka sendiri.

Dalam hal ini, opsi terbaik adalah membuat tampilan dan menerapkan AS OF subklasul ke seluruh tampilan. Menggunakan pendekatan ini memungkinkan Anda memisahkan pemodelan lapisan akses data dari analisis waktu titik waktu, karena SQL Server menerapkan klausul AS OF secara transparan ke 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, menerapkan klausa kueri temporal ke dalamnya gagal dengan 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 JOIN [dbo].[LocationDepartments]
    ON [CompanyLocation].LocID = LocationDepartments.LocID
LEFT JOIN [dbo].[Department]
    ON LocationDepartments.DeptID = [Department].DeptID;
GO

Anda bisa mengkueri tampilan menggunakan AS OF subklaus dan literal datetime2 :

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

Subklaus temporal , BETWEEN ... AND dan CONTAINED IN berguna ketika Anda perlu mendapatkan semua perubahan historis FROM ... TOuntuk baris tertentu dalam tabel saat ini (juga dikenal sebagai audit data).

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

Jika Anda mencari versi baris non-saat ini saja, Anda harus mengkueri tabel riwayat secara langsung, untuk performa kueri terbaik. Gunakan ALL saat Anda perlu mengkueri 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;

Konten terkait