SQL Academy · Pelajaran

Baris Temporal dan Berversi

Pertanyaan masa sah dan keadaan pada masa tertentu

Pelajaran 3 daripada 413 langkah

Baris Temporal dan Berversi ialah pelajaran SQL Academy percuma di CoddyKit. Ini ialah pelajaran 3 daripada 4. Anda boleh membaca keseluruhan pelajaran di bawah secara percuma — kemudian berlatih secara praktikal dalam pelayar menggunakan penyunting kod terbina dalam dan tutor kecerdasan buatan 24/7. Pelajaran ini merupakan sebahagian daripada laluan pembelajaran SQL Academy, dan kemajuan anda disegerakkan merentas web serta aplikasi CoddyKit. Kursus SQL Academy merangkumi sejumlah 4 pelajaran.

Apakah Jadual Berasaskan Masa

Jadual berasaskan masa membolehkan anda menjejaki perubahan data dari semasa ke semasa. Daripada menulis ganti baris apabila sesuatu berubah, jadual berasaskan masa mengekalkan setiap versi baris itu, dengan setiap versi ditandai menggunakan tempoh masa ia sah.

Terdapat dua konsep utama: masa kesahan (masa fakta itu benar di dunia sebenar) dan masa transaksi (masa pangkalan data merekodkan fakta itu). Gabungan kedua-duanya menghasilkan jadual dwitemporal sepenuhnya.

Masa Kesahan berbanding Masa Transaksi

Masa kesahan mewakili masa sesuatu fakta benar di dunia sebenar — contohnya, gaji seorang pekerja dari 2020-01-01 hingga 2022-06-30. Masa transaksi ialah masa baris pangkalan data dimasukkan atau luput. Bersama-sama, kedua-duanya menjawab dua soalan: Apa yang benar? dan Bilakah kita mengetahuinya?

Kebanyakan kegunaan praktikal bermula dengan penjejakan masa kesahan, yang boleh anda laksanakan secara manual menggunakan lajur valid_from dan valid_to.

Mencipta Jadual Masa Kesahan

Cara paling mudah untuk menyimpan baris berversi adalah dengan menambah lajur cap masa valid_from dan valid_to. Nilai valid_to berupa NULL (atau penanda masa hadapan yang sangat jauh seperti 9999-12-31) bermaksud baris itu sedang aktif.

CREATE TABLE employee_salary (
  id          SERIAL PRIMARY KEY,
  employee_id INT NOT NULL,
  salary      NUMERIC(12, 2) NOT NULL,
  valid_from  DATE NOT NULL,
  valid_to    DATE
);

INSERT INTO employee_salary (employee_id, salary, valid_from, valid_to)
VALUES
  (1, 50000, '2020-01-01', '2022-06-30'),
  (1, 60000, '2022-07-01', NULL);

Mendapatkan Versi Semasa

Untuk mencari baris yang sedang aktif bagi setiap pekerja, tapis baris yang mempunyai valid_to IS NULL (tanpa had tamat) atau baris yang tarikh hari ini berada dalam julat sah. Menggunakan nilai penanda seperti '9999-12-31' memudahkan perbandingan julat.

SELECT employee_id, salary
FROM employee_salary
WHERE valid_to IS NULL
ORDER BY employee_id;

Pertanyaan pada Titik Masa

Pertanyaan pada titik masa bertanya: Apakah data pada suatu titik masa tertentu? Anda menapis baris yang mempunyai cap masa tertentu dalam tempoh sah. Ini ialah salah satu ciri jadual berasaskan masa yang paling berkuasa.

-- What was employee 1's salary on 2021-03-15?
SELECT employee_id, salary, valid_from, valid_to
FROM employee_salary
WHERE employee_id = 1
  AND valid_from <= '2021-03-15'
  AND (valid_to IS NULL OR valid_to > '2021-03-15');

Mengemas Kini Baris Berversi

Apabila sesuatu fakta berubah, anda tidak melakukan UPDATE pada baris sedia ada secara terus. Sebaliknya, anda menutup baris semasa dengan menetapkan nilai valid_to, kemudian INSERT baris baharu dengan nilai baharu. Hal ini mengekalkan sejarah lengkap.

-- Employee 1 gets a raise effective 2023-01-01
BEGIN;

-- Close the current open row
UPDATE employee_salary
SET valid_to = '2022-12-31'
WHERE employee_id = 1
  AND valid_to IS NULL;

-- Insert the new version
INSERT INTO employee_salary (employee_id, salary, valid_from, valid_to)
VALUES (1, 72000, '2023-01-01', NULL);

COMMIT;

Menggunakan Julat Tarikh untuk Tempoh Kesahan

Jenis daterange PostgreSQL memodelkan tempoh kesahan sebagai satu lajur dengan berkesan. Anda boleh menggunakan operator @> (mengandungi) untuk menyemak sama ada sesuatu tarikh berada dalam julat, serta menambah kekangan pengecualian bagi menghalang tempoh yang bertindih untuk entiti yang sama.

CREATE TABLE employee_salary_v2 (
  id          SERIAL PRIMARY KEY,
  employee_id INT NOT NULL,
  salary      NUMERIC(12, 2) NOT NULL,
  valid_period DATERANGE NOT NULL,
  EXCLUDE USING GIST (employee_id WITH =, valid_period WITH &&)
);

INSERT INTO employee_salary_v2 (employee_id, salary, valid_period)
VALUES
  (1, 50000, '[2020-01-01, 2022-07-01)'),
  (1, 60000, '[2022-07-01, infinity)');

Pertanyaan pada Titik Masa dengan Julat Tarikh

Dengan pendekatan daterange, pertanyaan pada titik masa menjadi sangat mudah dibaca. Operator @> menyemak bahawa tarikh yang diberikan terkandung dalam julat, sambil mengendalikan sempadan bawah dan atas secara automatik.

-- What was employee 1's salary on 2021-03-15?
SELECT employee_id, salary, valid_period
FROM employee_salary_v2
WHERE employee_id = 1
  AND valid_period @> '2021-03-15'::date;

Jadual Berversi Sistem (Piawaian SQL)

Piawaian SQL:2011 memperkenalkan jadual berasaskan masa berversi sistem. Pangkalan data mengurus lajur masa transaksi row_start dan row_end secara automatik. Dalam PostgreSQL, anda mensimulasikannya; dalam SQL Server dan MariaDB, ciri ini tersedia secara terbina dalam dengan SYSTEM VERSIONING.

Contoh di bawah menunjukkan sintaks SQL Server / MariaDB sebagai rujukan konsep.

-- SQL Server / MariaDB syntax (reference)
CREATE TABLE dbo.Product (
  ProductID   INT PRIMARY KEY,
  Name        VARCHAR(100),
  Price       DECIMAL(10,2),
  SysStart    DATETIME2 GENERATED ALWAYS AS ROW START,
  SysEnd      DATETIME2 GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME (SysStart, SysEnd)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Product_History));

Cantuman Berasaskan Masa: Menyelaraskan Dua Jadual Mengikut Masa

Satu cabaran yang lazim ialah mencantumkan dua jadual berasaskan masa berdasarkan tempoh masa yang sepadan. Contohnya, mencantumkan gaji pekerja dengan penugasan jabatan apabila kedua-duanya mempunyai tempoh masa kesahan. Anda membuat cantuman berdasarkan kunci entiti AND syarat pertindihan menggunakan && pada julat atau perbandingan tarikh yang dinyatakan secara jelas.

CREATE TABLE dept_assignment (
  employee_id INT,
  department  VARCHAR(50),
  valid_period DATERANGE
);

INSERT INTO dept_assignment VALUES
  (1, 'Engineering', '[2020-01-01, infinity)'),
  (1, 'Marketing',   '[2019-01-01, 2020-01-01)');

-- Periods where employee 1 was in Engineering AND had salary > 55000
SELECT s.salary, d.department,
       s.valid_period * d.valid_period AS overlap_period
FROM employee_salary_v2 s
JOIN dept_assignment d
  ON s.employee_id = d.employee_id
  AND s.valid_period && d.valid_period
WHERE s.employee_id = 1
  AND s.salary > 55000;

Mencegah Jurang dan Pertindihan

Dua masalah kualiti data yang lazim dalam jadual berasaskan masa ialah jurang (tempoh tanpa rekod) dan pertindihan (dua baris yang sah pada masa yang sama). Kekangan pengecualian dengan && menghalang pertindihan pada peringkat pangkalan data. Mengesan jurang memerlukan semakan liputan yang hilang menggunakan pertanyaan.

-- Find gaps in salary history for employee 1
-- (periods where upper(prev) < lower(next))
SELECT
  upper(a.valid_period) AS gap_start,
  lower(b.valid_period) AS gap_end
FROM employee_salary_v2 a
JOIN employee_salary_v2 b
  ON a.employee_id = b.employee_id
  AND upper(a.valid_period) < lower(b.valid_period)
WHERE a.employee_id = 1
  AND NOT EXISTS (
    SELECT 1 FROM employee_salary_v2 c
    WHERE c.employee_id = 1
      AND lower(c.valid_period) > upper(a.valid_period)
      AND lower(c.valid_period) < lower(b.valid_period)
  )
ORDER BY gap_start;

Semakan Kefahaman

Uji kefahaman anda tentang jadual berasaskan masa dan pertanyaan pada titik masa.

Jawab dalam pelajaran interaktif

Ringkasan: Baris Berasaskan Masa dan Berversi

Dalam pelajaran ini, anda mempelajari cara memodelkan data yang berubah mengikut masa menggunakan lajur masa kesahan dan jenis daterange PostgreSQL. Perkara utama:

Jangan sesekali menulis ganti baris sejarah — tutup baris lama dan masukkan versi baharu.
Gunakan pertanyaan pada titik masa (valid_from <= target AND valid_to > target) untuk mendapatkan data pada bila-bila titik masa dahulu.
Jenis daterange dengan operator @> menjadikan pertanyaan berasaskan masa ringkas dan mudah dibaca.
Kekangan pengecualian pada && (pertindihan julat) menguatkuasakan integriti data pada peringkat pangkalan data.
Cantuman berasaskan masa menyelaraskan dua sejarah dengan mencari persilangan tempoh kesahannya.

Pola ini menjadi asas kepada pensumberan peristiwa, pengelogan audit dan mana-mana sistem yang mengutamakan ketepatan sejarah.

Percuma untuk bermula

Pelajari SQL dengan tutor kecerdasan buatan — percuma

Tulis dan jalankan kod sebenar dalam pelayar anda, dapatkan bantuan segera daripada tutor kecerdasan buatan yang tersedia 24/7, dan sambung semula dari tempat anda berhenti di web atau dalam aplikasi.

Kursus: 46
Pelajaran: 183

Mula belajar secara percuma Lihat kursus penuh

Soalan Lazim

Adakah pelajaran “Baris Temporal dan Berversi” percuma?

Ya — teks penuh “Baris Temporal dan Berversi” boleh dibaca secara percuma di web ini. Untuk berlatih secara interaktif menggunakan penyunting kod terbina dalam dan tutor kecerdasan buatan 24/7, serta membuka kunci baki kursus SQL Academy, tingkat taraf kepada CoddyKit PRO. Kursus SQL Academy merangkumi sejumlah 4 pelajaran.

Apakah yang akan saya pelajari dalam “Baris Temporal dan Berversi”?

Pertanyaan masa sah dan keadaan pada masa tertentu Anda berlatih SQL Academy menggunakan kod praktikal yang dijalankan terus dalam pelayar, manakala tutor kecerdasan buatan 24/7 menjawab soalan anda semasa anda mengikuti pelajaran.

Adakah saya memerlukan pengalaman untuk memulakan SQL Academy?

Tiada pengalaman terdahulu diperlukan. Pembelajaran SQL Academy di CoddyKit disusun untuk pelajar daripada peringkat pemula hingga lanjutan, jadi anda boleh bermula di sini atau dari awal dan belajar mengikut kadar anda sendiri. Ini ialah pelajaran 3 daripada 4.

Berapa lamakah pelajaran “Baris Temporal dan Berversi” diambil?

Kebanyakan pelajaran CoddyKit mengambil masa kira-kira 5–10 minit. Setiap pelajaran ringkas dan interaktif, jadi anda boleh membuat kemajuan secara berterusan dan menyambung tepat dari tempat anda berhenti di web atau aplikasi.

Bolehkah saya menulis dan menjalankan kod dalam pelajaran SQL Academy ini?

Ya. Setiap pelajaran SQL Academy menyertakan penyunting kod terbina dalam, jadi anda boleh menulis dan menjalankan kod sebenar terus dalam pelayar serta menerima maklum balas kecerdasan buatan serta-merta — tanpa memerlukan persediaan setempat.

Semua pelajaran dalam kursus ini

Mengapa Menyimpan Sejarah
Jadual Peristiwa Tambah Sahaja
Baris Temporal dan Berversi
Membina Semula Keadaan daripada Peristiwa

← Kembali ke SQL Academy