Bagikan melalui

Mengkueri data dalam tabel temporal versi sistem

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

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. Jika kolom PERIOD tidak disembunyikan, nilainya muncul dalam kueri SELECT *. 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 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

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, klausa FOR SYSTEM_TIME harus disertakan antara nama tabel temporal dan alias (lihat contoh kedua untuk kueri waktu tertentu menggunakan subklausa AS OF).

Melakukan kueri pada waktu tertentu menggunakan AS OF subklausa

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

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

Contoh pertama ini mengembalikan keadaan tabel dbo.Department 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 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

Menggunakan tampilan berguna dalam skenario ketika analisis pada titik waktu tertentu 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 tampak pada suatu titik di masa lalu dapat menjadi tantangan, karena semua tabel berubah secara independen pada jadwal mereka sendiri.

Dalam hal ini, opsi terbaik adalah membuat pandangan dan menerapkan subklausa AS OF ke seluruh pandangan. Pendekatan ini memungkinkan Anda untuk memisahkan antara pemodelan lapisan akses data dari analisis berdasarkan titik waktu, karena SQL Server menerapkan klausul AS OF secara transparan ke setiap tabel temporal yang berpartisipasi dalam definisi lihat. 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 JOIN [dbo].[LocationDepartments]
    ON [CompanyLocation].LocID = LocationDepartments.LocID
LEFT JOIN [dbo].[Department]
    ON LocationDepartments.DeptID = [Department].DeptID;
GO

Anda bisa mengajukan kueri tampilan menggunakan subklaus AS OF serta 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 FROM ... TO, BETWEEN ... AND dan CONTAINED IN berguna ketika Anda perlu mendapatkan semua perubahan historis untuk 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

  • Last updated on