Sistem sürümüne sahip bir zamana bağlı tablodaki verileri sorgulama

Şunlar için geçerlidir: SQL Server 2016 (13.x) ve sonraki sürümler Azure SQL VeritabanıAzure SQL Yönetilen ÖrneğiSQL database in Microsoft Fabric

Zamansal tablodaki en güncel veri durumunu almak için, zamansız bir tablo gibi sorgulayın. PERIOD sütunları gizli değilse, değerleri bir SELECT * sorgusunda görünür. HIDDEN sütunları PERIOD olarak belirtirseniz, değerleri SELECT * sorgusunda görünmez. PERIOD sütunları gizlendiğinde, değerlerini döndürmek için bunlara SELECT yan tümcesinde özellikle başvurun.

Zaman tabanlı analiz yapmak için, FOR SYSTEM_TIME dört zamana özgü alt cümleli maddeyi kullanarak mevcut ve geçmiş tabloları boyunca veri sorgulayın. Bu tümceler hakkında daha fazla bilgi için bkz. Zamansal tablolar ve FROM tümcesi artı 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

Bir sorgudaki her tablo için bağımsız olarak belirtebilirsiniz FOR SYSTEM_TIME . Bunu yaygın tablo ifadelerinde, tablo değerli fonksiyonlarda ve depolanmış prosedürlerde kullanın. Zamansal bir tabloda tablo takma adı kullandığınızda, FOR SYSTEM_TIME ifadesini zamansal tablo adı ile takma adın arasına ekleyin (bkz. Belirli bir zamanı AS OF alt yan tümcesini kullanarak sorgulama bölümündeki ikinci örnek).

AS OF alt uyarısını kullanarak belirli bir süre için sorgulama

Alt maddeyi AS OF kullanarak verilerin geçmişte herhangi bir zamandaki durumunu yeniden inşa edin. Verileri sütun tanımlarında belirttiğiniz PERIODdatetime2 tipinin hassasiyetiyle yeniden oluşturabilirsiniz.

AS OF Zaman koşulunu dinamik olarak belirtmek için sabit literaller veya değişkenler içeren alt cümle kullanın. Verdiğiniz değerler UTC zamanı olarak yorumlanıyor.

Bu ilk örnek, tablonun dbo.DepartmentAS OF durumunu geçmişte belirli bir tarihte döndürür.

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

Bu ikinci örnek, satırların bir alt kümesi için zaman içinde iki nokta arasındaki değerleri karşılaştırır.

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;

Zamansal sorgularda AS OF alt bendi olan görünümler kullanılır

Görüşler, karmaşık zaman analizine ihtiyaç duyduğunuzda faydalıdır. Yaygın bir örnek, bugün bir önceki ayın değerleriyle bir iş raporu oluşturmaktır.

Müşteriler genellikle yabancı anahtar ilişkilerine sahip birçok tablo içeren normalleştirilmiş bir veritabanı modeline sahiptir. O normalleştirilmiş modelden alınan verilerin geçmişte bir noktada nasıl göründüğünü bulmak zor olabilir, çünkü tüm tablolar kendi ritmlerine göre bağımsız olarak değişir.

Bu durumda, en iyi seçenek bir görünüm oluşturmak ve AS OF alt dosyasını görünümün tamamına uygulamaktır. Bu yaklaşım, veri erişim katmanının modellenmesini nokta-zaman analizinden ayırır, çünkü SQL Server bu maddeyi AS OF görünüm tanımına katılan tüm zamansal tablolara şeffaf şekilde uygular. Ayrıca, aynı görünümde zamansal olmayan tablolarla zamansal tabloları birleştirebilirsiniz ve AS OF yalnızca geçici tablolara uygulanır. Görünüm en az bir zamana bağlı tabloya başvurmuyorsa, görünüme zamansal sorgulama cümlecikleri uygulamak hata verir.

Aşağıdaki örnek kod, üç zamansal tabloyu birleştiren bir görünüm oluşturur: Department, CompanyLocationve 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

AS OF alt öğesini ve datetime2 sabitini kullanarak görünümü sorgulayabilirsiniz.

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

Zaman içinde belirli satırlarda yapılan değişiklikleri sorgulama

Zamansal alt cümleler FROM ... TO, BETWEEN ... AND, ve CONTAINED IN mevcut tablodaki belirli bir satır için tüm geçmiş değişiklikleri almanız gerektiğinde (veri denetimi olarak da bilinir) faydalıdır.

İlk iki alt cümle, belirli bir dönemle örtüşen (yani verilen dönemden önce başlayıp sonra biten) satır versiyonlarını döndürürken, CONTAINED IN yalnızca belirli dönem sınırları içinde var olanları döndürür.

Sadece güncel olmayan satır sürümlerini ararıyorsanız, en iyi sorgu performansı için doğrudan geçmiş tablosunda sorgulayın. Herhangi bir kısıtlama olmadan güncel ve geçmiş verileri sorgulamak için kullanılır ALL .

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

Verilerin bir dönem boyunca nasıl göründüğünü özetlemek için GROUP BY öğesini FOR SYSTEM_TIME BETWEEN ... AND ve toplama işlevleriyle birleştirin. Bu tekniği tanımlayıcı istatistikler ve trend analizi için kullanın. Tek bir zaman noktasını yeniden oluşturmak yerine, zaman aralığı boyunca etkin olan her satır sürümünü bir araya getirir.

Aşağıdaki örnek, çalışan maaşlarının bir zaman aralığı boyunca departmanlara göre gruplandırılmış tanımlayıcı istatistikleri hesaplar. Zamansal tablolar ve Zamansal tablo kullanım senaryolarında tanımlanan sistem sürümlü tabloyu kullanırEmployee. AnnualSalary sütunu, MIN, MAX, STDEV ve AVG işlevleri için çok uygun sayısal bir ölçüdür.

FOR SYSTEM_TIME BETWEEN ... AND yan tümcesi, dönemle çakışan her satır sürümünü geri döndürür. Sonuç olarak, toplamlar dönemde geçerli olan tüm maaşları içerir. Bir çalışanın maaşı değiştiğinde, sorgu o çalışan için birden fazla versiyon döndürür ve her versiyon agremlara katkıda bulunur.

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;

İlgili içerik

  • Last updated on