Bài 34: VIEW và dữ liệu tổng hợp được lưu

Cô Mai và thầy Nam cùng cần danh sách học viên đã có người hướng dẫn. Hai người chép cùng quy tắc vào hai báo cáo, rồi một báo cáo được sửa còn báo cáo kia giữ nguyên. Cô muốn đặt quy tắc ở một nơi để dùng lại. Đồng thời, báo cáo cuối buổi cần giữ số liệu lúc chốt, dù dữ liệu ngày mai thay đổi.

Đặt tên cho một truy vấn để dùng lại

Khung nhìn, thường gọi là view, là một truy vấn được lưu dưới một tên. Khi đọc tên ấy trong FROM, bạn dùng kết quả của truy vấn đã đặt tên. View thường không lưu một bản sao cố định của các dòng. Nó lưu cách lấy các dòng từ nguồn.

Trong tình huống của cô Mai, học viên đã có người hướng dẫn là những dòng có mentor IS NOT NULL. Tạo view guided_students từ đúng các cột cần dùng, rồi đọc nó:

CREATE VIEW guided_students (id, name, mentor) AS
SELECT id, name, mentor
FROM students
WHERE mentor IS NOT NULL;
SELECT id, name, mentor
FROM guided_students
ORDER BY id;

Kết quả có An, Chi, Huy và Lan. Bình và Dung không xuất hiện vì ô người hướng dẫn của hai bạn là NULL. Điều kiện nằm trong định nghĩa view, nên câu đọc không phải viết lại điều kiện ấy. Các báo cáo dùng cùng view sẽ cùng bắt đầu từ một quy tắc.

Cô Mai còn cần đếm số học viên của từng người hướng dẫn. Báo cáo thứ hai đọc cùng view:

SELECT mentor, COUNT(*) AS students
FROM guided_students
GROUP BY mentor
ORDER BY mentor;

Cô Mai có hai học viên là An và Huy; thầy Nam có hai học viên là Chi và Lan. Báo cáo thứ hai không chép lại điều kiện mentor IS NOT NULL, vì view đã giữ quy tắc ấy.

Danh sách sau tên view đặt tên rõ cho từng cột đầu ra. Bạn cũng có thể đặt bí danh bằng AS trong truy vấn tạo view. Cách ghi rõ cột giúp người đọc biết báo cáo cung cấp gì. Nếu cần thêm một cột về sau, hãy xem lại những nơi đang dùng báo cáo trước khi sửa định nghĩa.

View được tạo trong cơ sở dữ liệu nơi bạn chạy lệnh. Mỗi ví dụ trong bài chạy trên một bản dữ liệu mới, nên các ví dụ đều tạo lại view guided_students. Trong cơ sở dữ liệu thật, bạn cần quản lý định nghĩa như các thay đổi cấu trúc khác.

Đọc view sau khi nguồn thay đổi

Để phân biệt view với bản sao, hãy đổi dữ liệu nguồn rồi đọc lại view. Trong ví dụ này, cô Mai vừa nhận hướng dẫn Dung. Ta gán người hướng dẫn vào dòng của Dung rồi đọc danh sách đã có hướng dẫn:

CREATE VIEW guided_students (id, name, mentor) AS
SELECT id, name, mentor
FROM students
WHERE mentor IS NOT NULL;
UPDATE students
SET mentor = 'Cô Mai'
WHERE id = 4;
SELECT id, name, mentor
FROM guided_students
ORDER BY id;

View bây giờ có thêm Dung, với người hướng dẫn là Cô Mai. Ta không chèn Dung vào view và không chạy lệnh làm mới view. Câu đọc thực hiện định nghĩa đã lưu trên nguồn sau cập nhật. Bảng trạng thái sau lệnh cho thấy thay đổi nằm ở students.

Chiều ngược lại cũng có thể xảy ra. Nếu bỏ người hướng dẫn của một học viên, bạn ấy không còn thỏa điều kiện. Câu dưới đây bắt đầu lại từ bộ mẫu ban đầu, rồi bỏ người hướng dẫn của Huy:

CREATE VIEW guided_students (id, name, mentor) AS
SELECT id, name, mentor
FROM students
WHERE mentor IS NOT NULL;
UPDATE students
SET mentor = NULL
WHERE id = 5;
SELECT id, name, mentor
FROM guided_students
ORDER BY id;

Kết quả chỉ còn An, Chi và Lan. View không giữ danh sách thành viên từ thời điểm tạo, mà áp dụng lại điều kiện khi được đọc. Vì vậy thay đổi nguồn có thể làm một dòng vào hoặc ra khỏi báo cáo. Đây là điểm khác với một tệp danh sách đã xuất ra trước đó.

SQLite mặc định chỉ cho đọc view, không cho sửa trực tiếp các dòng qua view. Nếu thử sửa tên học viên bằng view như dưới đây, bạn sẽ nhận lỗi:

CREATE VIEW guided_students (id, name, mentor) AS
SELECT id, name, mentor
FROM students
WHERE mentor IS NOT NULL;
UPDATE guided_students
SET name = 'Huy mới'
WHERE id = 5;

Bạn sửa dữ liệu ở bảng nguồn như hai ví dụ trước, rồi đọc lại view. Một số hệ quản trị hỗ trợ sửa qua một số loại view. SQLite cũng có cơ chế xử lý riêng bằng trigger, ngoài phạm vi bài này. Đừng lấy lỗi ở phòng thực hành làm quy tắc cho mọi hệ quản trị.

Giữ số liệu tại một thời điểm bằng bảng chụp

Bảng chụp (snapshot table) là bảng chứa kết quả đã lấy tại thời điểm tạo. Câu CREATE TABLE ... AS SELECT vừa tạo bảng vừa chép kết quả truy vấn vào đó. Tên bảng ấy không còn là một quy tắc đọc lại nguồn. Các dòng đã lưu chỉ đổi khi có thao tác ghi vào chính bảng chụp.

Cô Mai muốn giữ danh sách người có hướng dẫn lúc chốt buổi. Hãy tạo bảng chụp, bỏ người hướng dẫn của Huy trong nguồn, rồi đọc bản chụp:

CREATE TABLE guided_snapshot AS
SELECT id, name, mentor
FROM students
WHERE mentor IS NOT NULL;
UPDATE students
SET mentor = NULL
WHERE id = 5;
SELECT id, name, mentor
FROM guided_snapshot
ORDER BY id;

Bảng chụp vẫn có Huy với người hướng dẫn Cô Mai. Bảng nguồn sau lệnh cho thấy mentor của Huy đã là NULL. Hai kết quả khác nhau vì lần đọc bảng chụp không chạy lại câu chọn nguồn. Với báo cáo lịch sử, giữ thông tin lúc chốt có thể chính là điều cần thiết.

Bạn cũng có thể đặt nguồn mới và bản chụp cạnh nhau để thấy độ lệch. Ví dụ này chụp số xu, rồi cộng thêm cho học viên mã 1:

CREATE TABLE coin_snapshot AS
SELECT id, coins
FROM students;
UPDATE students
SET coins = coins + 10
WHERE id = 1;
SELECT s.id, s.coins AS current_coins,
  p.coins AS snapshot_coins
FROM students AS s
JOIN coin_snapshot AS p ON p.id = s.id
WHERE s.id = 1;

Hai cột lần lượt là 130 và 120. Chênh lệch không chứng minh một bảng bị hỏng, vì chúng mô tả hai thời điểm. Một báo cáo cần số liệu mới phải đọc nguồn hoặc có bước làm mới dữ liệu lưu sẵn. Một báo cáo cần giữ lịch sử lại không nên bị ghi đè mỗi lần nguồn đổi.

Chọn độ mới và cách làm mới trước khi lưu sẵn

Trong PostgreSQL, view vật hóa (materialized view) lưu kết quả truy vấn để đọc lại. Nó giữ cả định nghĩa truy vấn và kết quả, khác với view thường chỉ giữ định nghĩa. Lệnh REFRESH MATERIALIZED VIEW tính lại dữ liệu lưu sẵn từ nguồn. Nội dung có thể cũ trong khoảng giữa hai lần làm mới.

Quy trình của một báo cáo lưu sẵn có thể được đọc như sau:

Giả sử cô Mai xem báo cáo chốt buổi hôm qua. Một số thay đổi sáng nay chưa có trong báo cáo ấy là điều bình thường. Nhưng nếu cô đang tìm người chưa có hướng dẫn để phân công ngay, dữ liệu cũ có thể gây nhầm. Cùng một nguồn, hai nhu cầu có thể cần hai cách đọc khác nhau.

Trên SQLite, ta có thể tính lại bảng chụp bằng một quy trình ghi rõ ràng. Ví dụ sau thay tên người hướng dẫn trong nguồn rồi nạp lại bảng chụp trong một giao dịch:

CREATE TABLE guided_snapshot AS
SELECT id, name, mentor
FROM students
WHERE mentor IS NOT NULL;
UPDATE students
SET mentor = 'Cô Linh'
WHERE id = 1;
BEGIN;
DELETE FROM guided_snapshot;
INSERT INTO guided_snapshot (id, name, mentor)
SELECT id, name, mentor
FROM students
WHERE mentor IS NOT NULL;
COMMIT;
SELECT id, name, mentor
FROM guided_snapshot
ORDER BY id;

An có người hướng dẫn mới trong bản chụp sau lần nạp lại. Việc thay đổi xảy ra nhờ các lệnh ghi, không nhờ tên gọi của bảng. Giao dịch gom bước xóa dữ liệu cũ và bước nạp dữ liệu mới thành một thao tác. Đây vẫn là quy trình quản lý bảng chụp do bạn viết, không phải lệnh làm mới view vật hóa.

View thường giúp tái sử dụng quy tắc, nhưng không bảo đảm truy vấn chạy nhanh hơn. Nếu định nghĩa ghép quá nhiều dòng hoặc tính sai, đọc view vẫn gặp vấn đề ấy. Trước khi chọn lưu sẵn để giảm tính lại, hãy kiểm tính đúng và đo trên dữ liệu đại diện. Báo cáo lưu sẵn cũng cần ghi thời điểm tính để người đọc biết số liệu mới đến đâu.

Lỗi thường gặp

Tóm tắt

• View thường lưu một truy vấn có tên, giúp nhiều nơi dùng chung quy tắc.
• Dòng có thể vào hoặc ra khỏi view khi nguồn thay đổi và được đọc lại.
• Bảng chụp lưu các dòng tại thời điểm chạy câu chép dữ liệu.
• View vật hóa PostgreSQL lưu kết quả và có lệnh làm mới riêng, không có trong phòng SQLite.
• Chọn độ mới cần thiết, lịch làm mới và thời điểm báo cáo trước khi dùng dữ liệu lưu sẵn.

Kiểm tra nhanh

Đọc thêm

• SQLite: tạo view và giới hạn sửa qua view.
• PostgreSQL: view vật hóa và việc làm mới.