0Pricing
SQL Academy · บทเรียน

แถวแบบเวลาสัมพันธ์และแบบมีรุ่น

คำค้นหาตามเวลาที่มีผลและ ณ เวลาใดเวลาหนึ่ง

แถวแบบเวลาสัมพันธ์และแบบมีรุ่น เป็นบทเรียน SQL Academy ฟรีบน CoddyKit นี่คือบทเรียนที่ 3 จากทั้งหมด 4 บทเรียน คุณสามารถอ่านบทเรียนทั้งหมดด้านล่างฟรี — จากนั้นลองปฏิบัติด้วยตัวคุณเองในเบราว์เซอร์พร้อมตัวแก้ไขโค้ดในตัวและติวเตอร์ AI ตลอด 24/7 บทเรียนนี้เป็นส่วนหนึ่งของเส้นทางการเรียน SQL Academy และความก้าวหน้าของคุณจะซิงค์ข้ามเว็บและแอป CoddyKit คอร์ส SQL Academy มีบทเรียนทั้งหมด 4 บทเรียน

ตารางเชิงเวลาคืออะไร

ตารางเชิงเวลาช่วยให้คุณติดตามการเปลี่ยนแปลงของข้อมูลตามเวลาได้ แทนที่จะเขียนทับแถวเมื่อมีการเปลี่ยนแปลง ตารางเชิงเวลาจะเก็บทุกเวอร์ชันของแถวนั้นไว้ โดยแต่ละเวอร์ชันระบุช่วงเวลาที่มีผล

มีแนวคิดสำคัญสองประการ ได้แก่ เวลาที่มีผล (ช่วงเวลาที่ข้อเท็จจริงนั้นเป็นจริงในโลกจริง) และ เวลาของธุรกรรม (ช่วงเวลาที่ฐานข้อมูลบันทึกข้อเท็จจริงนั้น) เมื่อนำทั้งสองแนวคิดมารวมกัน จะได้ตารางแบบสองเวลาที่ครบถ้วน

เวลาที่มีผลเทียบกับเวลาของธุรกรรม

เวลาที่มีผลหมายถึงช่วงเวลาที่ข้อเท็จจริงเป็นจริงในโลกจริง — เช่น เงินเดือนของพนักงานตั้งแต่วันที่ 2020-01-01 ถึง 2022-06-30 ส่วนเวลาของธุรกรรมคือเวลาที่มีการเพิ่มแถวลงในฐานข้อมูลหรือทำให้แถวนั้นหมดผล ทั้งสองแนวคิดช่วยตอบคำถามสองข้อ ได้แก่ อะไรเป็นจริง และ เราทราบเรื่องนั้นเมื่อใด

กรณีใช้งานส่วนใหญ่เริ่มจากการติดตามเวลาที่มีผล ซึ่งคุณสามารถทำได้ด้วยตนเองโดยใช้คอลัมน์ valid_from และ valid_to

การสร้างตารางตามเวลาที่มีผล

วิธีที่ง่ายที่สุดในการจัดเก็บแถวที่มีเวอร์ชันคือการเพิ่มคอลัมน์เวลาประทับ valid_from และ valid_to ค่า NULL ใน valid_to (หรือค่าพิเศษแทนขอบเขตในอนาคตไกล เช่น 9999-12-31) หมายความว่าแถวนั้นยังใช้งานอยู่ในปัจจุบัน

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

การเรียกดูเวอร์ชันปัจจุบัน

หากต้องการค้นหาแถวที่ใช้งานอยู่ในปัจจุบันของพนักงานแต่ละคน ให้กรองแถวที่มี valid_to IS NULL (ไม่มีวันสิ้นสุด) หรือแถวที่วันที่ปัจจุบันอยู่ภายในช่วงเวลาที่มีผล การใช้ค่าพิเศษแทนขอบเขต เช่น '9999-12-31'ช่วยให้เปรียบเทียบช่วงได้ง่ายขึ้น

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

คำสั่งสอบถาม ณ เวลาอ้างอิง

คำสั่งสอบถาม ณ เวลาอ้างอิงใช้ถามว่า ข้อมูลเป็นอย่างไร ณ ช่วงเวลาใดเวลาหนึ่ง คุณกรองแถวที่เวลาประทับที่กำหนดอยู่ภายในช่วงเวลาที่มีผล ความสามารถนี้เป็นหนึ่งในคุณสมบัติที่ทรงพลังที่สุดของตารางเชิงเวลา

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

การปรับปรุงแถวที่มีเวอร์ชัน

เมื่อข้อเท็จจริงเปลี่ยนแปลง คุณจะไม่ใช้ UPDATE เพื่อแก้ไขแถวเดิมโดยตรง แต่ให้ปิดแถวปัจจุบันด้วยการกำหนดค่าให้ valid_to แล้ว INSERT แถวใหม่พร้อมค่าใหม่ วิธีนี้ช่วยรักษาประวัติทั้งหมดไว้

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

การใช้ daterange สำหรับช่วงเวลาที่มีผล

ชนิดข้อมูล daterange ของ PostgreSQL ช่วยจำลองช่วงเวลาที่มีผลเป็นคอลัมน์เดียวได้อย่างเหมาะเจาะ คุณสามารถใช้ตัวดำเนินการ @> (มีค่าอยู่ภายใน) เพื่อตรวจสอบว่าวันที่อยู่ในช่วงหรือไม่ และเพิ่มข้อจำกัดการยกเว้นเพื่อป้องกันช่วงเวลาที่ซ้อนทับกันของเอนทิตีเดียวกัน

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

คำสั่งสอบถาม ณ เวลาอ้างอิงด้วย daterange

เมื่อใช้แนวทาง daterange คำสั่งสอบถาม ณ เวลาอ้างอิงจะอ่านได้เข้าใจง่ายขึ้น ตัวดำเนินการ @>ตรวจสอบว่าวันที่ที่กำหนดอยู่ภายในช่วงหรือไม่ โดยจัดการขอบเขตล่างและขอบเขตบนให้โดยอัตโนมัติ

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

ตารางที่กำหนดเวอร์ชันโดยระบบ (มาตรฐาน SQL)

มาตรฐาน SQL:2011 ได้แนะนำตารางเชิงเวลาที่กำหนดเวอร์ชันโดยระบบ ฐานข้อมูลจะจัดการคอลัมน์เวลาของธุรกรรม row_start และ row_endโดยอัตโนมัติ ใน PostgreSQL คุณต้องจำลองความสามารถนี้ขึ้นมาเอง ส่วน SQL Server และ MariaDB มีความสามารถนี้ในตัวด้วย SYSTEM VERSIONING

ตัวอย่างด้านล่างแสดงไวยากรณ์ของ SQL Server / MariaDB เพื่อใช้เป็นข้อมูลอ้างอิงสำหรับแนวคิดนี้

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

การเชื่อมตารางเชิงเวลา: จัดช่วงเวลาของสองตารางให้ตรงกัน

ความท้าทายที่พบได้บ่อยคือการเชื่อมตารางเชิงเวลาสองตารางโดยให้ช่วงเวลาตรงกัน ตัวอย่างเช่น การเชื่อมเงินเดือนพนักงานกับการมอบหมายแผนกในกรณีที่ทั้งสองข้อมูลมีช่วงเวลาที่มีผล คุณจะเชื่อมด้วยคีย์ของเอนทิตี AND เงื่อนไขการซ้อนทับ โดยใช้ && กับช่วง หรือใช้การเปรียบเทียบวันที่โดยตรง

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;

การป้องกันช่องว่างและการซ้อนทับ

ปัญหาด้านคุณภาพข้อมูลที่พบได้บ่อยในตารางเชิงเวลามีสองอย่าง ได้แก่ ช่องว่าง (ช่วงเวลาที่ไม่มีระเบียน) และการซ้อนทับ (แถวสองแถวที่มีผลในเวลาเดียวกัน) ข้อจำกัดการยกเว้นที่ใช้กับ &&ช่วยป้องกันการซ้อนทับในระดับฐานข้อมูล การตรวจหาช่องว่างต้องตรวจสอบส่วนที่ขาดหายไปด้วยคำสั่งสอบถาม

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

ตรวจสอบความเข้าใจ

ทดสอบความเข้าใจเกี่ยวกับตารางเชิงเวลาและคำสั่งสอบถาม ณ เวลาอ้างอิง

ทบทวน: แถวเชิงเวลาและแถวที่มีเวอร์ชัน

ในบทเรียนนี้ คุณได้เรียนรู้วิธีจำลองข้อมูลที่เปลี่ยนแปลงตามเวลาโดยใช้คอลัมน์เวลาที่มีผลและชนิดข้อมูล daterange ของ PostgreSQL ประเด็นสำคัญ:

  • อย่าเขียนทับแถวในอดีต — ให้ปิดแถวเดิมแล้วเพิ่มเวอร์ชันใหม่
  • ใช้คำสั่งสอบถาม ณ เวลาอ้างอิง (valid_from <= target AND valid_to > target) เพื่อเรียกดูข้อมูล ณ เวลาใด ๆ ในอดีต
  • ชนิดข้อมูล daterangeพร้อมตัวดำเนินการ @>ช่วยให้คำสั่งสอบถามเชิงเวลากระชับและอ่านเข้าใจง่าย
  • ข้อจำกัดการยกเว้นบน && (การซ้อนทับของช่วง) ช่วยบังคับใช้ความถูกต้องของข้อมูลในระดับฐานข้อมูล
  • การเชื่อมตารางเชิงเวลาช่วยจัดแนวประวัติสองชุดให้ตรงกันด้วยการหาจุดตัดของช่วงเวลาที่มีผล

รูปแบบเหล่านี้เป็นรากฐานของการจัดเก็บที่ขับเคลื่อนด้วยเหตุการณ์ การบันทึกการตรวจสอบ และทุกระบบที่ความถูกต้องของประวัติมีความสำคัญ

คำถามที่พบบ่อย

บทเรียน “แถวแบบเวลาสัมพันธ์และแบบมีรุ่น” ฟรีหรือไม่

ใช่ — ข้อความเต็มของ “แถวแบบเวลาสัมพันธ์และแบบมีรุ่น” ฟรีให้อ่านที่นี่บนเว็บ เพื่อปฏิบัติแบบโต้ตอบ (ตัวแก้ไขโค้ดในตัวและติวเตอร์ AI ตลอด 24/7) และปลดล็อคส่วนที่เหลือของคอร์ส SQL Academy ให้อัปเกรดเป็น CoddyKit PRO คอร์ส SQL Academy มีบทเรียนทั้งหมด 4 บทเรียน

คุณจะเรียนรู้อะไรในบทเรียน “แถวแบบเวลาสัมพันธ์และแบบมีรุ่น”

คำค้นหาตามเวลาที่มีผลและ ณ เวลาใดเวลาหนึ่ง คุณปฏิบัติ SQL Academy ด้วยโค้ดที่ใช้งานได้จริงที่คุณเรียกใช้โดยตรงในเบราว์เซอร์ และติวเตอร์ AI ตลอด 24/7 ตอบคำถามของคุณขณะที่คุณไปผ่านบทเรียน

คุณต้องมีประสบการณ์ก่อนที่จะเริ่มเรียน SQL Academy หรือไม่

ไม่จำเป็นต้องมีประสบการณ์มาก่อน SQL Academy บน CoddyKit ออกแบบมาสำหรับผู้เริ่มต้นไปจนถึงผู้เรียนขั้นสูง คุณสามารถเริ่มต้นที่นี่หรือเริ่มจากตัวแรกและเรียนด้วยความเร็วของคุณเอง นี่คือบทเรียนที่ 3 จากทั้งหมด 4 บทเรียน

บทเรียน “แถวแบบเวลาสัมพันธ์และแบบมีรุ่น” ใช้เวลานานแค่ไหน

บทเรียน CoddyKit ส่วนใหญ่ใช้เวลาประมาณ 5–10 นาที แต่ละบทเรียนจึงสั้นและเป็นแบบโต้ตอบ คุณสามารถก้าวหน้าอย่างต่อเนื่องและกลับมาเรียนต่อจากตรงที่เพิ่งหยุดบนเว็บและแอปได้เลย

ฉันเขียนและรันโค้ดในบทเรียน SQL Academy นี้ได้ไหม

ได้ บทเรียน SQL Academy ทุกบทมีตัวแก้ไขโค้ดในตัว คุณจึงเขียนและรันโค้ดจริงได้เลยในเบราว์เซอร์ และได้รับข้อเสนอแนะจาก AI ในทันที — ไม่ต้องติดตั้งในเครื่องของคุณ

บทเรียนทั้งหมดในหลักสูตรนี้

  1. เหตุผลที่ควรเก็บประวัติ
  2. ตารางเหตุการณ์แบบเพิ่มข้อมูลอย่างเดียว
  3. แถวแบบเวลาสัมพันธ์และแบบมีรุ่น
  4. การสร้างสถานะขึ้นใหม่จากเหตุการณ์
← กลับไปที่ SQL Academy