Bài 35: Bài phân tích: điểm tốt nhất của mỗi học viên

Cô Mai chuẩn bị nhận xét cuối tháng cho câu lạc bộ. Một bạn có thể làm bài nhiều lần, còn một bạn khác chưa nộp lần nào. Cô cần biết điểm tốt nhất và số lần làm của từng người. Nếu chỉ đọc danh sách điểm, cô sẽ bỏ sót người cần được nhắc làm bài.

Ôn cách giữ đủ người và phân biệt điểm thiếu

Bảng scores lưu mỗi lần chấm thành một dòng. An có hai lần, còn Lan chưa có lần nào. Hãy ôn cách gom điểm theo mã rồi ghép vào danh sách học viên:

WITH best AS (
  SELECT student_id, MAX(score) AS best_score,
    COUNT(*) AS attempts
  FROM scores
  GROUP BY student_id
)
SELECT s.id, s.name, b.best_score,
  COALESCE(b.attempts, 0) AS attempts
FROM students AS s
LEFT JOIN best AS b ON b.student_id = s.id
ORDER BY s.id;

An có điểm cao nhất 90 và 2 lần chấm. Lan vẫn có một dòng nhờ LEFT JOIN, dù không có nhóm tương ứng trong CTE best. Chỉ số lần thiếu được đổi thành 0. Điểm giữ NULL để phân biệt chưa làm với đã được chấm điểm không.

Đổi điểm thiếu thành 0 sẽ khiến người đọc tưởng Lan đã làm bài. Khi cần thống kê người đã làm, hãy kiểm sự tồn tại của lần chấm thay vì chỉ tìm điểm lớn hơn không.

Lọc tháng trước khi chọn điểm cao nhất

Báo cáo tháng cần xác định rõ những lần chấm nào được tính. Một điểm cao ngoài tháng không được thay cho điểm trong tháng. Với ngày lưu dạng YYYY-MM-DD, bạn có thể so sánh theo khoảng từ đầu tháng đến trước đầu tháng sau.

Để thấy lỗi rõ hơn, mục này thêm hai lần chấm riêng: An đạt 100 ngày 2026-08-31, Bình đạt 99 ngày 2026-10-01. Những dòng này chỉ dùng trong ví dụ có tên ranh-thang. Dữ liệu chung của các mục khác không thay đổi.

Trước tiên, hãy xem cách viết trả lời nhầm câu hỏi toàn thời gian:

SELECT student_id, MAX(score) AS best_score
FROM scores
GROUP BY student_id
ORDER BY student_id;

An nhận 100, còn Bình nhận 99. Câu lệnh vẫn đúng cú pháp nhưng không trả lời yêu cầu tháng chín. Sau khi gom nhóm, mỗi nhóm chỉ còn mã và điểm cao nhất. Bạn không thể nhìn hai cột ấy để biết điểm đến từ tháng nào.

Hãy giới hạn các lần chấm ngay trước bước gom nhóm:

SELECT student_id, MAX(score) AS best_score
FROM scores
WHERE day >= '2026-09-01' AND day < '2026-10-01'
GROUP BY student_id
ORDER BY student_id;

An trở về 90, Bình trở về 70. Dấu < ở mốc cuối loại đúng ngày đầu tháng sau. Cách viết này cũng tránh phải nhớ tháng có bao nhiêu ngày. Nếu cần đủ mọi học viên, vị trí của điều kiện ngày còn quyết định người nào được giữ.

Cách dưới đây đặt điều kiện ngày sau LEFT JOIN, khiến báo cáo mất Lan:

SELECT s.id, s.name, MAX(sc.score) AS best_score,
  COUNT(sc.id) AS attempts
FROM students AS s
LEFT JOIN scores AS sc ON sc.student_id = s.id
WHERE sc.day >= '2026-09-01' AND sc.day < '2026-10-01'
GROUP BY s.id, s.name
ORDER BY s.id;

Lan không còn trong kết quả. Dòng do LEFT JOIN giữ cho Lan có sc.day là NULL, nên không thỏa điều kiện ở WHERE. Bộ lọc trên bảng bên phải đã loại người chưa có điểm.

Hãy lọc và tổng hợp trong CTE trước, rồi ghép kết quả ấy từ students:

WITH month_stats AS (
  SELECT student_id, MAX(score) AS best_score,
    COUNT(*) AS attempts
  FROM scores
  WHERE day >= '2026-09-01' AND day < '2026-10-01'
  GROUP BY student_id
)
SELECT s.id, s.name, m.best_score,
  COALESCE(m.attempts, 0) AS attempts
FROM students AS s
LEFT JOIN month_stats AS m ON m.student_id = s.id
ORDER BY s.id;

Lan trở lại với điểm NULL và số lần 0. Bộ lọc chỉ chọn nguồn điểm tháng chín, còn phép ghép ngoài giữ mọi học viên. Đặt điều kiện ngày vào ON cũng giữ được người thiếu điểm. Bài này chọn CTE để tách rõ bước lọc và bước ghép.

Chọn cả ngày đạt điểm bằng một quy tắc phá hòa

Điểm tốt nhất mới trả lời được “đạt bao nhiêu”. Nếu cô Mai hỏi thêm ngày đạt điểm, bạn cần chọn một lần chấm cụ thể. Phá hòa (tie-breaking) là quy tắc chọn khi nhiều lần có cùng điểm. Ở đây, ta chọn lần đạt sớm nhất, rồi dùng mã lần chấm nếu ngày cũng bằng nhau.

Ví dụ sau thêm một lần An đạt 90 vào ngày 2026-09-03. Sau đó, truy vấn đánh số các lần của từng người theo quy tắc đã nêu:

WITH ranked AS (
  SELECT id, student_id, score, day,
    ROW_NUMBER() OVER (
      PARTITION BY student_id
      ORDER BY score DESC, day ASC, id ASC
    ) AS rn
  FROM scores
)
SELECT student_id, score, day
FROM ranked
WHERE rn = 1
ORDER BY student_id;

An được chọn ngày 2026-09-02, vì đó là lần đạt 90 sớm hơn. PARTITION BY student_id đánh số lại cho từng người. Thứ tự trong cửa sổ chọn lần chấm, còn thứ tự ngoài cùng sắp báo cáo. Hai thứ tự phục vụ hai việc riêng.

Không nên lấy một cột ngày tùy ý cạnh phép tính điểm cao nhất. Khi hòa điểm, ngày được lấy có thể không theo quy tắc bạn muốn. Nếu báo cáo chỉ cần điểm, dùng MAX(score) là đủ. Nếu cần thông tin của lần chấm, hãy chọn dòng bằng một thứ tự đầy đủ.

Kiểm số lần trước khi ghép thêm lớp

Cô Mai muốn xem điểm cùng thông tin đăng ký. Một học viên có nhiều lần chấm và nhiều đăng ký, nên ghép trực tiếp hai nhánh sẽ làm lặp các lần chấm. Trường hợp này dễ bị bỏ qua vì điểm cao nhất vẫn có vẻ đúng.

Hãy xem số lần đếm được khi ghép thẳng hai bảng chi tiết:

SELECT s.id, s.name, MAX(sc.score) AS best_score,
  COUNT(sc.id) AS attempts
FROM students AS s
LEFT JOIN scores AS sc ON sc.student_id = s.id
LEFT JOIN enrollments AS e ON e.student_id = s.id
GROUP BY s.id, s.name
ORDER BY s.id;

An bị đếm thành 4 lần dù chỉ có 2 lần chấm. Mỗi lần chấm ghép được với cả hai đăng ký. Giá trị lớn nhất vẫn là 90, nên chỉ kiểm cột điểm sẽ không phát hiện lỗi. Cách sửa là gom mỗi nhánh theo người trước khi ghép các kết quả tổng hợp:

WITH score_stats AS (
  SELECT student_id, MAX(score) AS best_score,
    COUNT(*) AS attempts
  FROM scores
  GROUP BY student_id
), class_stats AS (
  SELECT student_id, COUNT(*) AS classes
  FROM enrollments
  GROUP BY student_id
)
SELECT s.id, s.name, sc.best_score,
  COALESCE(sc.attempts, 0) AS attempts,
  COALESCE(c.classes, 0) AS classes
FROM students AS s
LEFT JOIN score_stats AS sc ON sc.student_id = s.id
LEFT JOIN class_stats AS c ON c.student_id = s.id
ORDER BY s.id;

An có attempts = 2 và classes = 2. Mỗi CTE chỉ còn một dòng cho mỗi mã người, nên hai nhánh không nhân số lần của nhau. Cột classes tính mọi đăng ký, kể cả tạm dừng.

Khi kiểm báo cáo, bạn hãy chọn một người có nhiều dòng ở cả hai bảng. Sau đó đối chiếu số lần với bảng chấm gốc. Trường hợp này giúp phát hiện lỗi mà người chỉ có một lớp không bộc lộ.

Hoàn thành báo cáo tháng chín

Cô Mai cần đủ mọi học viên, số lần trong tháng và ngày đạt điểm tốt nhất. Ta kết hợp lọc tháng, đánh số theo quy tắc phá hòa, rồi ghép từ danh sách học viên:

Ví dụ hoàn chỉnh dùng lại dữ liệu hòa điểm: An có thêm lần đạt 90 ngày 2026-09-03:

WITH month_scores AS (
  SELECT id, student_id, score, day
  FROM scores
  WHERE day >= '2026-09-01' AND day < '2026-10-01'
), ranked AS (
  SELECT student_id, score, day,
    ROW_NUMBER() OVER (
      PARTITION BY student_id
      ORDER BY score DESC, day ASC, id ASC
    ) AS rn
  FROM month_scores
), month_counts AS (
  SELECT student_id, COUNT(*) AS attempts
  FROM month_scores
  GROUP BY student_id
)
SELECT s.id, s.name, r.score AS best_score,
  r.day AS best_day, COALESCE(c.attempts, 0) AS attempts
FROM students AS s
LEFT JOIN ranked AS r ON r.student_id = s.id AND r.rn = 1
LEFT JOIN month_counts AS c ON c.student_id = s.id
ORDER BY s.id;

An có điểm tốt nhất 90, ngày đạt 2026-09-02 và 3 lần chấm trong tháng. Lan có điểm và ngày là NULL, số lần bằng 0. Điều kiện r.rn = 1 nằm trong ON để giữ cả người không có lần được chọn.

Báo cáo cuối tách rõ ba việc: chọn nguồn điểm, chọn lần đạt điểm tốt nhất và giữ danh sách học viên. Khi đổi tháng báo cáo, bạn chỉ cần sửa hai mốc ngày ở bước đầu.

Lỗi thường gặp

Tóm tắt

• Xác định mỗi dòng là một lần chấm hay một người trước khi tổng hợp.
• Điểm thiếu giữ NULL, còn số lần thiếu có thể chuyển thành 0.
• Báo cáo theo tháng phải giới hạn nguồn điểm trước khi chọn giá trị lớn nhất.
• Khi cần cả ngày đạt điểm, chọn một dòng bằng quy tắc phá hòa rõ ràng.
• Kiểm cả số lần lẫn điểm tốt nhất để phát hiện lỗi nhân dòng.

Kiểm tra nhanh

Đọc thêm

• SQLite: hàm cửa sổ và cách đánh số dòng.
• SQLite: cách xử lý nhóm trong SELECT.