SQL Academy · บทเรียน

เหตุผลที่ควรเก็บประวัติ

ตรวจสอบ ยกเลิก และวิเคราะห์ข้อมูลในอดีต

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

ปัญหาของการเขียนทับข้อมูล

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

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

เหตุผลสามประการที่ควรเก็บประวัติ

มีเหตุผลพื้นฐานสามประการในการเก็บรักษาข้อมูลในอดีตไว้ในฐานข้อมูล:

1. การตรวจสอบย้อนหลัง — พิสูจน์ว่าการเปลี่ยนแปลงเกิดขึ้น ใครเป็นผู้ดำเนินการ และเกิดขึ้นเมื่อไร
2. การยกเลิก — ย้อนคืนข้อผิดพลาดโดยไม่ต้องกู้คืนฐานข้อมูลทั้งระบบ
3. การวิเคราะห์ — ตอบคำถามเกี่ยวกับอดีต มองหาแนวโน้ม และเปรียบเทียบช่วงเวลา

กลยุทธ์การจัดการประวัติที่ออกแบบมาอย่างดีจะตอบสนองความต้องการทั้งสามด้านโดยไม่ใช้พื้นที่จัดเก็บซ้ำซ้อนมากเกินไป

ตารางตรวจสอบย้อนหลังแบบง่าย

แนวทางที่ง่ายที่สุดคือตารางตรวจสอบย้อนหลังแยกต่างหาก ซึ่งบันทึกการเปลี่ยนแปลงทุกครั้ง แต่ละแถวจะเก็บค่าเดิม ค่าใหม่ ผู้ที่เปลี่ยนแปลง และเวลาที่เปลี่ยนแปลง

ด้านล่างคือตารางตรวจสอบย้อนหลังสำหรับตาราง products คอลัมน์ operation จะเก็บค่า INSERT, UPDATE หรือ DELETE

CREATE TABLE products_audit (
  audit_id   SERIAL PRIMARY KEY,
  product_id INT            NOT NULL,
  operation  VARCHAR(6)     NOT NULL,  -- INSERT / UPDATE / DELETE
  old_price  NUMERIC(10,2),
  new_price  NUMERIC(10,2),
  changed_by TEXT           NOT NULL,
  changed_at TIMESTAMPTZ    NOT NULL DEFAULT NOW()
);

การเติมข้อมูลลงในตารางตรวจสอบย้อนหลัง

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

ในที่นี้เราจะแทรกแถวตรวจสอบย้อนหลังหนึ่งแถวโดยตรง เพื่อแสดงโครงสร้างก่อนที่จะพูดถึงทริกเกอร์

INSERT INTO products_audit (product_id, operation, old_price, new_price, changed_by)
VALUES (42, 'UPDATE', 9.99, 12.49, 'alice');

SELECT * FROM products_audit ORDER BY changed_at DESC LIMIT 5;

การอ่านประวัติการตรวจสอบย้อนหลัง

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

SELECT
  changed_at,
  changed_by,
  operation,
  old_price,
  new_price
FROM products_audit
WHERE product_id = 42
ORDER BY changed_at DESC;

วันที่มีผล: ประวัติตามเวลาที่มีผลจริง

ตารางตรวจสอบย้อนหลังจะบันทึกเวลาที่คุณทำการเปลี่ยนแปลง (เวลาธุรกรรม) บางครั้งคุณยังต้องติดตามเวลาที่สิ่งนั้นเป็นจริงในโลกจริง ซึ่งเรียกว่าเวลาที่มีผลจริง

การเพิ่มคอลัมน์ valid_from และ valid_to ลงในตารางหลักจะสร้างประวัติตามเวลาที่มีผลจริง ซึ่งบางครั้งเรียกว่ามิติที่เปลี่ยนแปลงช้า (SCD ประเภท 2)

CREATE TABLE employee_history (
  id          SERIAL PRIMARY KEY,
  employee_id INT            NOT NULL,
  department  TEXT           NOT NULL,
  salary      NUMERIC(10,2)  NOT NULL,
  valid_from  DATE           NOT NULL,
  valid_to    DATE           -- NULL means current record
);

-- Current record for employee 7
INSERT INTO employee_history (employee_id, department, salary, valid_from)
VALUES (7, 'Engineering', 85000, '2023-01-01');

การอัปเดตระเบียนที่เปลี่ยนแปลงช้า

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

-- Step 1: close the current record
UPDATE employee_history
SET valid_to = '2024-06-01'
WHERE employee_id = 7 AND valid_to IS NULL;

-- Step 2: insert the new record
INSERT INTO employee_history (employee_id, department, salary, valid_from)
VALUES (7, 'Product', 90000, '2024-06-01');

-- Verify history
SELECT department, salary, valid_from, valid_to
FROM employee_history
WHERE employee_id = 7
ORDER BY valid_from;

การคิวรีข้อมูล ณ เวลาใดเวลาหนึ่ง

เมื่อมีคอลัมน์เวลาที่มีผลจริง คุณสามารถถามได้ว่า ในวันที่ระบุ สิ่งใดเป็นความจริง ซึ่งเป็นคิวรีที่ทำไม่ได้ด้วยรูปแบบ UPDATE แบบธรรมดา

ส่วนคำสั่ง WHERE จะตรวจสอบว่าวันที่เป้าหมายอยู่ภายในช่วงเวลาที่แถวมีผลหรือไม่

-- What department and salary did employee 7 have on 2023-09-15?
SELECT department, salary, valid_from, valid_to
FROM employee_history
WHERE employee_id = 7
  AND valid_from <= '2023-09-15'
  AND (valid_to > '2023-09-15' OR valid_to IS NULL);

ตารางเชิงเวลาที่มีรุ่นตามระบบ

ฐานข้อมูล SQL สมัยใหม่ (PostgreSQL 16+, SQL Server, MySQL 8) รองรับตารางเชิงเวลาที่มีรุ่นตามระบบ ฐานข้อมูลจะติดตามเวลาธุรกรรมในคอลัมน์ที่ซ่อนอยู่โดยอัตโนมัติ และคุณสามารถคิวรีสถานะในอดีตด้วยไวยากรณ์พิเศษ

ตัวอย่างของ SQL Server — แนวคิดนี้เหมือนกันในระบบฐานข้อมูลต่าง ๆ:

-- SQL Server / MariaDB style (illustrative)
CREATE TABLE orders (
  order_id    INT PRIMARY KEY,
  status      VARCHAR(20),
  total       NUMERIC(10,2),
  SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START,
  SysEndTime   DATETIME2 GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
) WITH (SYSTEM_VERSIONING = ON);

-- Query historical state
SELECT * FROM orders FOR SYSTEM_TIME AS OF '2024-01-15 12:00:00'
WHERE order_id = 100;

การใช้ประวัติเพื่อเลิกทำ

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

-- Undo all price changes made by the bad batch job at a specific time
UPDATE products p
SET price = a.old_price
FROM products_audit a
WHERE p.id         = a.product_id
  AND a.operation  = 'UPDATE'
  AND a.changed_by = 'batch_job'
  AND a.changed_at BETWEEN '2024-03-10 02:00:00' AND '2024-03-10 02:05:00';

-- Confirm affected rows
SELECT COUNT(*) AS rows_restored FROM products_audit
WHERE changed_by = 'batch_job'
  AND changed_at BETWEEN '2024-03-10 02:00:00' AND '2024-03-10 02:05:00';

การวิเคราะห์ข้อมูลตามช่วงเวลา

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

คำสั่งสอบถามนี้แสดงราคาเฉลี่ยของสินค้าในแต่ละเดือนตามปฏิทินโดยใช้ตารางตรวจสอบ

SELECT
  DATE_TRUNC('month', changed_at) AS month,
  ROUND(AVG(new_price), 2)         AS avg_price
FROM products_audit
WHERE product_id = 42
  AND operation IN ('INSERT', 'UPDATE')
GROUP BY 1
ORDER BY 1;

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

ทดสอบความเข้าใจเกี่ยวกับการจัดเก็บข้อมูลประวัติใน SQL

ทบทวน: เหตุใดจึงควรเก็บประวัติ

ในบทเรียนนี้ คุณได้เรียนรู้ว่าเหตุใดการเขียนทับข้อมูลจึงมีความเสี่ยง และรูปแบบของ SQL ช่วยรักษาประวัติไว้สำหรับการตรวจสอบ การเลิกทำ และการวิเคราะห์ได้อย่างไร

ประเด็นสำคัญ:

ตารางตรวจสอบบันทึกทุกการทำงานของ INSERT, UPDATE และ DELETE พร้อมระบุผู้ดำเนินการและเวลา
แถวตามเวลาที่มีผล (SCD ประเภทที่ 2)ใช้คอลัมน์ valid_from / valid_to เพื่อบันทึกลำดับเวลาที่เกิดขึ้นจริง
คำสั่งสอบถาม ณ เวลาใดเวลาหนึ่งใช้ตอบคำถามเกี่ยวกับอดีตด้วยการกรองจากคอลัมน์วันที่เหล่านั้น
ตารางเชิงเวลาที่กำหนดเวอร์ชันโดยระบบทำให้การติดตามเวลาของธุรกรรมในระดับฐานข้อมูลเป็นไปโดยอัตโนมัติ
ข้อมูลประวัติช่วยให้เลิกทำได้อย่างเจาะจงและวิเคราะห์อนุกรมเวลาได้อย่างละเอียดโดยไม่ต้องมีสำเนาสำรอง

การเก็บรักษาอดีตไม่ใช่ภาระส่วนเกิน — แต่เป็นรากฐานของระบบที่น่าเชื่อถือและตรวจสอบได้

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

บทเรียน “เหตุผลที่ควรเก็บประวัติ” ฟรีหรือไม่

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

คุณจะเรียนรู้อะไรในบทเรียน “เหตุผลที่ควรเก็บประวัติ”

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

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

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

บทเรียน “เหตุผลที่ควรเก็บประวัติ” ใช้เวลานานแค่ไหน

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

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

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

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

← กลับไปที่ SQL Academy