GROUP BY và HAVING — Tổng hợp theo nhóm

Dữ liệu dùng trong bài này — bảng score

Ở bài trước bạn đã thu gọn cả một bảng thành một giá trị duy nhất. `GROUP BY` của bài này (gom nhóm — gộp các hàng có cùng giá trị vào một nhóm) thực hiện tổng hợp theo từng nhóm và trả về một hàng tổng hợp cho mỗi nhóm. Ngoài ra, `HAVING` cho phép bạn lọc kết quả tổng hợp, ví dụ "chỉ những nhóm có trung bình từ 80 trở lên".

Đề tài vẫn là bảng score như lần trước (30 hàng = 10 người × 3 môn). Gom nhóm theo name cho 10 nhóm, còn gom nhóm theo subject cho 3 nhóm.

Trước khi vào các bài tập, hãy xem qua định nghĩa các cột và một mẫu dữ liệu của bảng score.

① Dùng PRAGMA table_info(score); để kiểm tra tên cột, kiểu dữ liệu và khóa chính.

② Dùng SELECT * FROM score LIMIT 5; để xem trước 5 hàng dữ liệu đầu tiên.

GROUP BY — tổng hợp theo từng nhóm

Khi bạn viết GROUP BY cột, các hàng có cùng giá trị ở cột đó được gộp vào một nhóm, và các hàm tổng hợp được tính một lần cho mỗi nhóm. Với SELECT name, AVG(score) FROM score GROUP BY name;, 3 hàng có cùng tên (mỗi môn một hàng) tạo thành một nhóm, và bạn nhận được một hàng trung bình cho mỗi tên. "Cột thường" duy nhất bạn có thể đặt trong SELECT là các cột được nêu trong `GROUP BY`; bất kỳ cột nào khác đều phải bọc trong một hàm tổng hợp (vì giá trị của nó không duy nhất trong một nhóm).

-- Số lượng / trung bình / điểm cao nhất theo môn (3 nhóm theo subject)
SELECT
  subject,
  COUNT(*)             AS row_count,
  ROUND(AVG(score), 1) AS avg_score,
  MAX(score)           AS top_score
FROM score
GROUP BY subject;

Hãy hình dung yêu cầu "tôi muốn một danh sách điểm trung bình của từng học sinh". (Chạy đúng thì phần giải thích sẽ hiện ra.)

① Gom nhóm bảng score theo tên.

② Với mỗi nhóm, lấy tên là name, số môn đã thi là subjects, và điểm trung bình là avg_score, theo thứ tự đó.

③ Làm tròn trung bình đến 2 chữ số thập phân. Thứ tự không được chỉ định.

HAVING — lọc kết quả tổng hợp

Sau khi tổng hợp theo nhóm, đôi khi bạn muốn lọc theo giá trị tổng hợp, ví dụ "chỉ hiển thị những nhóm có trung bình từ 80 trở lên". Bạn không thể làm điều này bằng WHERE. WHERE là mệnh đề xét từng hàng riêng lẻ trước khi gom nhóm, nên một giá trị tổng hợp nhóm như AVG(score) chưa được tính. Đó là lúc dùng đến `HAVING`. HAVING được viết sau GROUP BY và xét điều kiện của nó trên kết quả tổng hợp của các nhóm. Viết HAVING AVG(score) >= 80 chỉ giữ lại những nhóm có trung bình từ 80 trở lên.

-- Chỉ những tên có trung bình vượt 85 (HAVING xét giá trị tổng hợp)
SELECT
  name,
  ROUND(AVG(score), 2) AS avg_score
FROM score
GROUP BY name
HAVING AVG(score) > 85;

Hãy hình dung yêu cầu "tôi muốn liệt kê chỉ những học sinh xuất sắc có điểm trung bình từ 80 trở lên".

① Gom nhóm bảng score theo tên.

② Với mỗi nhóm, lấy tên là name và điểm trung bình là avg_score (làm tròn đến 2 chữ số thập phân).

③ Chỉ giữ lại những nhóm có trung bình từ 80 trở lên. Thứ tự không được chỉ định.

WHERE và HAVING — lọc hàng, hay lọc nhóm

WHERE và HAVING đều là bộ lọc, nhưng chúng hoạt động ở các thời điểm khác nhau. WHERE xét từng hàng riêng lẻ và loại chúng trước khi gom nhóm. HAVING xét theo từng nhóm và loại chúng sau khi gom nhóm và tổng hợp. Thứ tự thực thi là FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY.

Bạn cũng có thể dùng cả hai cùng lúc. Khi đó luồng là "thu hẹp các hàng mục tiêu bằng WHERE, rồi gom nhóm, rồi lọc kết quả tổng hợp bằng HAVING". Ví dụ, để lấy "những người có trung bình theo tên từ 80 trở lên, chỉ xét Math và English", bạn thu hẹp hàng bằng WHERE subject IN ('Math','English') và thu hẹp nhóm bằng HAVING AVG(score) >= 80.

-- Dùng WHERE và HAVING cùng nhau
-- Loại Science, chỉ giữ những tên có trung bình từ 80 trở lên
SELECT
  name,
  ROUND(AVG(score), 2) AS avg_two
FROM score
WHERE subject <> 'Science'
GROUP BY name
HAVING AVG(score) >= 80;

Hãy hình dung yêu cầu "chỉ xét hai môn Math và English, tôi muốn những người có trung bình theo tên từ 85 trở lên".

① Từ bảng score, chỉ giữ những hàng có subject là Math hoặc English (Science nằm ngoài phạm vi).

② Gom nhóm các hàng còn lại theo tên và lấy tên là name và điểm trung bình hai môn là avg_two (làm tròn đến 2 chữ số thập phân).

③ Sau đó chỉ giữ những nhóm có trung bình từ 85 trở lên. Thứ tự không được chỉ định.

Hãy hình dung yêu cầu "tôi muốn hiển thị trung bình theo môn dưới dạng bảng xếp hạng, cao nhất trước".

① Gom nhóm bảng score theo môn.

② Với mỗi nhóm, lấy môn là subject, số học sinh đã thi là students, và điểm trung bình là avg_score (làm tròn đến 2 chữ số thập phân), theo thứ tự đó.

③ Sắp xếp chúng từ trung bình cao nhất xuống thấp nhất.