Bài 16: Nối nhiều bảng mà không nhân sai số liệu

Thầy Nam muốn một báo cáo vừa có số lớp vừa có tổng điểm của từng học viên. Bạn ghép các bảng đã học, nhưng tổng điểm của An bỗng cao gấp đôi. Điểm gốc không hề đổi và các mã vẫn khớp. Bạn cần kiểm tra mỗi dòng sau khi ghép mô tả một lần chấm hay một cặp lớp–lần chấm.

Lấy thêm tên lớp vẫn cho một dòng mỗi đăng ký

Hãy bắt đầu từ một báo cáo đã quen. Ta cần tên học viên và tên lớp của từng đăng ký. Bảng enrollments dẫn tới một học viên bằng student_id và một lớp bằng class_id. Mỗi khóa đích xác định đúng một dòng.

Câu lệnh dưới dùng hai khóa ngoại của mỗi đăng ký để tìm tên học viên và tên lớp:

SELECT s.id, s.name, c.name AS class_name
FROM enrollments AS e
JOIN students AS s ON s.id = e.student_id
JOIN classes AS c ON c.id = e.class_id
ORDER BY s.id, c.id;

Hai dòng đầu cho biết An học lớp Python và lớp Database. Kết quả có bảy dòng vì có bảy đăng ký. Thêm tên người và tên lớp chỉ làm dòng dễ đọc hơn. Vì mỗi mã chỉ khớp một người hoặc một lớp, lấy thêm tên không làm tăng số dòng đăng ký.

Đơn vị dòng là sự việc mà một dòng đang biểu diễn. Trong kết quả này, mỗi dòng mô tả một đăng ký của một học viên vào một lớp. Nó không phải một học viên, vì cùng người có thể xuất hiện nhiều lần. Trước khi cộng một cột, bạn cần kiểm tra giá trị đó xuất hiện mấy lần và vì sao nó lặp lại.

Nếu lấy số xu của học viên đưa vào mỗi dòng đăng ký, cùng số dư sẽ xuất hiện ở nhiều lớp. Điều đó có thể hữu ích khi xem từng dòng. Nhưng nếu cộng cả cột, bạn sẽ cộng số dư của người học nhiều lớp nhiều lần. Muốn tính tổng xu của các học viên, bạn chỉ được tính số dư mỗi người một lần.

Ghép mỗi đăng ký với mỗi lần chấm của cùng học viên

Một học viên có hai danh sách riêng: danh sách đăng ký trong enrollments và danh sách lần chấm trong scores. Cả hai bảng đều lưu mã học viên, nhưng bảng điểm không có mã lớp để biết mỗi điểm thuộc lớp nào. Nối cả hai qua người sẽ ghép mỗi đăng ký với mỗi lần chấm của người ấy.

Quan sát riêng An để thấy đủ từng cặp được tạo ra:

SELECT e.class_id, t.id AS score_id, t.score
FROM enrollments AS e
JOIN scores AS t ON t.student_id = e.student_id
WHERE e.student_id = 1
ORDER BY e.class_id, t.id;

Mỗi lớp của An xuất hiện cạnh cả điểm 80 và điểm 90. Mỗi lần chấm cũng xuất hiện hai lần, một lần cho mỗi lớp. Bốn dòng không phải bốn lần chấm mới. Chúng là bốn cặp được tạo từ hai danh sách riêng: danh sách đăng ký và danh sách lần chấm.

Hiện tượng tăng số dòng khi ghép nhiều quan hệ một–nhiều thường được gọi là nhân dòng (fan-out). Khóa ngoại vẫn hợp lệ trong tình huống này. Bạn sẽ tính sai nếu xem bốn cặp đó là bốn lần chấm riêng. Dù đã nối đúng mã học viên, bạn vẫn cần kiểm tra mỗi lần chấm xuất hiện bao nhiêu lần trước khi cộng điểm.

SELECT SUM(t.score) AS joined_total
FROM enrollments AS e
JOIN scores AS t ON t.student_id = e.student_id
WHERE e.student_id = 1;

Tổng là 340, trong khi hai lần chấm gốc có tổng 170. Cả điểm 80 và 90 đều bị cộng hai lần. Thêm ORDER BY không sửa được số lần mỗi giá trị xuất hiện. Nếu đổi sang LEFT JOIN, mỗi đăng ký vẫn ghép với cả hai lần chấm. Vì vậy, tổng vẫn bị cộng lặp.

Gom về một dòng mỗi người rồi mới nối

Ta cần tính số lần chấm hoặc tổng điểm của từng học viên trước khi ghép với bảng khác. Một truy vấn con trong FROM (derived table) là câu SELECT đặt trong ngoặc, được dùng như một bảng nguồn. Bí danh sau ngoặc đặt tên cho bảng kết quả ấy. Bài 17 sẽ mở rộng những vị trí khác có thể dùng truy vấn con.

Trước hết, chạy riêng bước gom nhóm để kiểm tra mỗi học viên còn bao nhiêu dòng:

SELECT student_id, COUNT(*) AS n, SUM(score) AS total
FROM scores
GROUP BY student_id
ORDER BY student_id;

An có n = 2 và total = 170. Chi có n = 2 và total = 190. Mỗi mã người chỉ xuất hiện một lần. Lan không có nhóm vì chưa có điểm.

Với mỗi người chỉ còn một dòng điểm tổng hợp, ta có thể ghép vào báo cáo mà không lặp người theo số lần chấm.

Hàm COALESCE chọn giá trị đầu tiên khác NULL. Ta dùng COALESCE(t.n, 0) để hiển thị 0 khi người không có nhóm điểm. Ví dụ dưới đưa bước gom vào FROM của phép nối:

SELECT s.id, s.name, COALESCE(t.n, 0) AS score_count
FROM students AS s
LEFT JOIN (
  SELECT student_id, COUNT(*) AS n
  FROM scores
  GROUP BY student_id
) AS t ON t.student_id = s.id
ORDER BY s.id;

Kết quả có đủ sáu học viên và An vẫn có hai lần chấm. Lan mang số 0 vì LEFT JOIN giữ người chưa có nhóm. COALESCE chỉ đổi giá trị hiển thị từ NULL sang 0. Nó không thêm một lần chấm giả vào bảng điểm.

Nếu báo cáo cần cả tổng điểm và số lớp, bạn có thể gom độc lập cả hai nhánh. Sau đó mỗi người ghép với tối đa một dòng điểm và một dòng số lớp. Ở mỗi bảng kết quả đã gom nhóm, hãy kiểm tra mỗi mã học viên chỉ xuất hiện một lần. Nếu sau đó bạn nối thêm toàn bộ các lần chấm chưa gom nhóm, các dòng báo cáo có thể lại bị lặp.

DISTINCT chỉ giữ mỗi giá trị điểm một lần

SUM(DISTINCT score) chỉ cộng mỗi giá trị điểm khác nhau một lần. Nó không biết hai giá trị 90 thuộc hai lần chấm riêng. Nếu dữ liệu mẫu có điểm khác nhau, cách sửa này có thể tình cờ cho tổng đúng. Vì thế cần một trường hợp hai lần chấm cùng điểm.

Ví dụ riêng dưới đổi điểm lần chấm mã 1 thành 90. An lúc này có hai lần chấm đều được 90. Các đăng ký vẫn giữ nguyên. Thay đổi này chỉ phục vụ so sánh hai cách tính:

Ta tính trực tiếp trên hai lần chấm gốc, chưa cần nối:

SELECT SUM(score) AS actual_total,
  SUM(DISTINCT score) AS distinct_total
FROM scores
WHERE student_id = 1;

Kết quả đặt tổng đúng 180 cạnh tổng theo giá trị khác nhau 90. Hai dòng có mã lần chấm khác nhau, nên cả hai điểm đều phải được cộng vào tổng. DISTINCT trên điểm đã bỏ mất một sự kiện hợp lệ. Vì vậy, bạn không thể thêm DISTINCT vào phép cộng rồi coi như đã sửa được mọi trường hợp nhân dòng.

Nếu cần đếm số lớp khác nhau, dùng DISTINCT trên mã lớp lại phù hợp. Chẳng hạn, COUNT(DISTINCT e.class_id) có thể đếm số lớp khác nhau của một người. Cách đếm đó không có nghĩa bạn cũng nên dùng SUM(DISTINCT s.coins) để cộng xu của mọi người. Chi và Huy cùng có 180 xu và cả hai đều cần được tính.

Trước khi gửi báo cáo, bạn hãy kiểm ba kiểu người. Người có nhiều lớp và nhiều lần chấm giúp bạn thấy mỗi điểm có bị cộng lặp hay không. Với người chưa có điểm, hãy kiểm tra báo cáo có giữ người đó và hiển thị số lần chấm bằng 0 hay không. Hai lần chấm cùng điểm giúp bạn kiểm tra DISTINCT có vô tình bỏ mất một lần chấm hợp lệ hay không.

Hoàn thành báo cáo của thầy Nam

Báo cáo mở đầu cần số lớp và tổng điểm của từng học viên. Ta gom đăng ký và lần chấm riêng theo student_id. Sau đó, mỗi học viên chỉ ghép với tối đa một dòng của mỗi bảng đã gom:

SELECT s.id, s.name,
  COALESCE(e.n, 0) AS class_count,
  COALESCE(t.n, 0) AS score_count,
  COALESCE(t.total, 0) AS total_score
FROM students AS s
LEFT JOIN (
  SELECT student_id, COUNT(*) AS n
  FROM enrollments
  GROUP BY student_id
) AS e ON e.student_id = s.id
LEFT JOIN (
  SELECT student_id, COUNT(*) AS n, SUM(score) AS total
  FROM scores
  GROUP BY student_id
) AS t ON t.student_id = s.id
ORDER BY s.id;

An có class_count = 2, score_count = 2 và total_score = 170. Tổng không còn là 340 vì mỗi lần chấm chỉ được cộng một lần trước khi nối. Số lớp ở đây tính cả đăng ký tạm dừng.

Lan vẫn có một dòng với cả ba giá trị bằng 0. Hai phép LEFT JOIN giữ Lan dù bạn ấy chưa có lớp hoặc điểm. COALESCE đổi các giá trị thiếu thành 0, nên thầy Nam nhận đủ báo cáo cho mọi học viên.

Lỗi thường gặp

Tóm tắt

• Số bảng được nối chưa nói lên số dòng. Quan hệ giữa các bảng mới quyết định số dòng kết quả.
• Khi nối đăng ký với điểm theo học viên, mỗi đăng ký sẽ ghép với mọi lần chấm của người đó.
• Gom từng nhánh về một dòng mỗi người trước khi ghép báo cáo.
• COALESCE giúp hiển thị số 0 khi thiếu nhóm điểm. DISTINCT bỏ giá trị trùng, nên không thay thế được việc gom nhóm đúng trước khi nối.

Kiểm tra nhanh

Đọc thêm

• SQLite giải thích nguồn dữ liệu, phép nối và gom nhóm.
• SQLite mô tả SUM và tác dụng của DISTINCT.