Bài 9: Lọc nhóm và phân loại bằng CASE

Cô Mai muốn mời những khối có tổng xu đủ lớn tham gia một hoạt động chung. Cô cũng cần một nhãn ngắn cho mức xu của từng học viên để đọc danh sách nhanh hơn. Cô cần kiểm tra tổng xu của cả khối, nhưng lại gắn nhãn riêng cho từng học viên. Bạn sẽ dùng hai công cụ khác nhau để chọn khối và tạo nhãn cho từng người.

HAVING giữ nhóm sau khi tính tổng

Giả sử hoạt động chỉ mời các khối có tổng ít nhất 200 xu. Một học viên có thể chưa đạt 200 xu nhưng vẫn góp vào một khối đủ điều kiện. Vì vậy, phải cộng toàn khối trước khi quyết định giữ khối ấy.

Mệnh đề HAVING đặt điều kiện lên nhóm sau khi các giá trị tổng hợp đã được tính. Bạn viết nó sau GROUP BY. Câu sau cộng xu của mỗi khối rồi giữ những tổng đạt ngưỡng:

SELECT grade, SUM(coins) AS total
FROM students
GROUP BY grade
HAVING SUM(coins) >= 200
ORDER BY grade;

Kết quả chỉ còn khối 7 với 240 xu. Bình có 90 xu, còn Lan có 150 xu. Mỗi bạn đều chưa có 200 xu, nhưng tổng của hai bạn đã đủ ngưỡng. Điều kiện xét tổng không nên biến thành điều kiện yêu cầu mỗi người có ít nhất 200 xu.

Hình dưới đây chỉ ra bước kiểm tra để giữ hay loại từng khối. Các nhóm đã tồn tại trước khi HAVING kiểm tra kết quả tính:

HAVING cũng dùng được với số đếm hoặc trung bình. Nếu cần khối có ít nhất hai học viên, bạn đặt điều kiện cho COUNT(*). Khi đó, bạn đang xét số người trong khối thay vì tổng xu:

SELECT grade, COUNT(*) AS n
FROM students
GROUP BY grade
HAVING COUNT(*) >= 2
ORDER BY grade;

Khối 6 và 7 cùng có n bằng 2, còn khối 8 và 9 bị loại vì chỉ có một người. Không cần viết danh sách khối cố định. Khi dữ liệu thay đổi, truy vấn vẫn kiểm tra số học viên thực tế của từng khối.

WHERE và HAVING chọn hai đối tượng khác nhau

Yêu cầu tiếp theo chỉ xét học viên có từ 100 xu, rồi giữ khối có tổng từ 150 xu. Điều kiện 100 xu áp dụng cho từng học viên nên đặt trong WHERE. Điều kiện tổng 150 xu áp dụng cho cả nhóm nên đặt trong HAVING.

Bạn có thể đặt cả hai bộ lọc trong một truy vấn. Hãy đọc điều kiện từng người trước, rồi mới cộng những người còn lại:

SELECT grade, COUNT(*) AS n, SUM(coins) AS total
FROM students
WHERE coins >= 100
GROUP BY grade
HAVING SUM(coins) >= 150
ORDER BY grade;

Kết quả còn khối 7, 8 và 9. Khối 7 có một người và tổng 150 vì Bình đã bị loại trước khi gom nhóm. An vẫn tạo thành nhóm khối 6 với tổng 120 xu. Sau khi tính tổng, HAVING loại nhóm này vì chưa đủ 150 xu.

Ở bước WHERE lọc từng học viên, truy vấn chưa tính tổng của các nhóm. Vì vậy, bạn không thể đặt điều kiện kiểm tổng nhóm vào bước này. Nếu đặt điều kiện tổng vào WHERE như dưới đây, SQLite sẽ báo lỗi:

SELECT grade, SUM(coins) AS total
FROM students
WHERE SUM(coins) >= 200
GROUP BY grade;

SQLite báo lỗi vì đặt hàm tổng hợp sai vị trí. Sửa bằng cách chuyển điều kiện SUM(coins) >= 200 xuống HAVING. Nếu chỉ thay thành WHERE coins >= 200, câu lệnh chạy được nhưng chuyển sang chọn người và làm sai yêu cầu.

CASE tạo một giá trị theo nhánh đúng đầu tiên

Trong danh sách học viên, cô Mai muốn gắn nhãn mức xu. Biểu thức (expression) là một cách viết tính ra một giá trị. Biểu thức CASE chọn giá trị từ các nhánh điều kiện, thay vì loại dòng khỏi kết quả.

Ở mỗi nhánh, bạn viết điều kiện sau WHEN và giá trị muốn nhận sau THEN. Sau ELSE là giá trị dùng khi không nhánh nào đúng. Từ khóa END đánh dấu kết thúc biểu thức. Trước tiên, hãy thử hai nhãn với một điều kiện:

SELECT id, name,
       CASE WHEN coins >= 150 THEN 'Đạt'
            ELSE 'Chưa đạt' END AS label
FROM students
ORDER BY id;

Chi, Huy và Lan nhận nhãn Đạt, còn ba bạn khác nhận Chưa đạt. Kết quả vẫn có sáu dòng vì CASE không lọc học viên. Nếu muốn chỉ giữ người đạt ngưỡng, bạn cần điều kiện trong WHERE.

Để tạo ba mức, thêm một nhánh cho khoảng giữa. CASE xét từ trên xuống và dừng tại nhánh đúng đầu tiên. Vì các điều kiện có thể chồng nhau, hãy đặt ngưỡng cao hơn trước:

SELECT id, name,
       CASE WHEN coins >= 150 THEN 'Nhiều'
            WHEN coins >= 100 THEN 'Vừa'
            ELSE 'Ít' END AS coin_group
FROM students
ORDER BY id;

An có 120 xu nên nhánh đầu sai, nhánh thứ hai đúng và cho Vừa. Chi có 180 xu nên dừng ngay ở nhánh đầu với nhãn Nhiều. Dù 180 cũng lớn hơn 100, nhánh thứ hai không được chọn thêm lần nữa.

Hãy đảo hai nhánh để xem nhãn của Chi, Huy và Lan thay đổi:

SELECT id, name,
       CASE WHEN coins >= 100 THEN 'Vừa'
            WHEN coins >= 150 THEN 'Nhiều'
            ELSE 'Ít' END AS coin_group
FROM students
ORDER BY id;

Ba dòng được tô đều có ít nhất 150 xu nhưng nhận nhãn Vừa. Các bạn đã thỏa nhánh coins >= 100, nên CASE dừng trước nhánh mức cao. Đưa ngưỡng 150 lên trước sẽ trả lại nhãn Nhiều cho ba bạn.

Ngưỡng cũng cần được đọc chính xác tại điểm bằng nhau. Lan có đúng 150 xu và được xếp Nhiều vì điều kiện dùng >=. Nếu đổi thành >, Lan sẽ nhận nhãn của nhánh tiếp theo. Những học viên có số xu cách xa ngưỡng vẫn có thể nhận đúng nhãn, khiến bạn dễ bỏ sót lỗi.

ELSE quyết định giá trị cho trường hợp còn lại

Nếu thiếu ELSE và không nhánh nào đúng, CASE trả NULL. Nó không tự chọn số 0 hay chuỗi rỗng. Điều này ảnh hưởng đến các phép tính đọc giá trị do CASE tạo ra.

Câu dưới đây chỉ gắn nhãn cho người đạt 150 xu, còn những người khác thiếu nhãn. Bảng đầu vào vẫn không thay đổi:

SELECT id, name,
       CASE WHEN coins >= 150 THEN 'Đạt' END AS label
FROM students
ORDER BY id;

An, Bình và Dung có label là NULL, nhưng vẫn xuất hiện trong kết quả. Đây là giá trị mới do biểu thức tạo ra. Nó không có nghĩa cột name hay coins trong bảng gốc đã bị xóa.

Nếu báo cáo cần nhãn cho mọi người, hãy ghi ELSE rõ ràng. Nếu cột nguồn có thể chứa NULL, bạn cũng cần quyết định sẽ gắn nhãn gì khi thiếu dữ liệu. Cột coins trong bộ mẫu bắt buộc có giá trị nên ví dụ này chỉ cần phân loại theo mức xu.

Cộng các giá trị CASE để đếm có điều kiện

Cô Mai cần một con số cho biết bao nhiêu người đạt ít nhất 150 xu. Bạn cho biểu thức trả 1 với người đạt ngưỡng và 0 với người chưa đạt. Cộng các giá trị này lại, bạn sẽ biết có bao nhiêu người đạt.

Hãy đặt biểu thức CASE vào bên trong SUM. Bí danh giúp người đọc biết tổng này đang đếm người chứ không cộng xu:

SELECT SUM(
         CASE WHEN coins >= 150 THEN 1 ELSE 0 END
       ) AS high_count
FROM students;

Kết quả là 3 vì có ba dòng đạt điều kiện. Viết COUNT(CASE WHEN coins >= 150 THEN 1 ELSE 0 END) lại đếm cả số 0. COUNT bỏ qua NULL, nhưng vẫn đếm số 0 vì ô đó có giá trị.

Cách đếm có điều kiện hữu ích khi muốn giữ nhóm có số đếm 0. Bạn có thể nhóm theo khối rồi chỉ đếm người đạt trong mỗi khối. Khối chưa có ai đạt vẫn xuất hiện trong kết quả. Các học viên của khối còn trong nguồn và mỗi người đóng góp 0 vào tổng.

Hãy đếm số học viên và số người đạt ít nhất 150 xu trong từng khối:

SELECT grade, COUNT(*) AS n,
       SUM(CASE WHEN coins >= 150 THEN 1 ELSE 0 END) AS high_count
FROM students
GROUP BY grade
ORDER BY grade;

Khối 6 có n bằng 2 và high_count bằng 0. An và Dung vẫn nằm trong nhóm, nhưng mỗi người góp 0 vào số đếm đạt. Báo cáo vì vậy vẫn cho thấy khối này cần hỗ trợ.

Nếu chuyển điều kiện sang WHERE, hãy quan sát những khối còn lại:

SELECT grade, COUNT(*) AS high_count
FROM students
WHERE coins >= 150
GROUP BY grade
ORDER BY grade;

Kết quả chỉ còn khối 7, 8 và 9, mỗi khối có một người đạt. Khối 6 biến mất vì không còn học viên nào sau bộ lọc. Hai cách cùng đếm người đạt nhưng khác nhau ở việc giữ nhóm có số 0.

Nếu nguồn hoàn toàn rỗng, SUM vẫn trả NULL, đúng quy tắc bạn đã học ở bài 7. ELSE 0 chỉ cho giá trị 0 khi có dòng để tính biểu thức. Nó không tạo thêm học viên hay nhóm khi nguồn không có dòng nào.

Lỗi thường gặp

Tóm tắt

• HAVING quyết định nhóm nào còn lại sau khi tính tổng hợp.
• WHERE và HAVING có thể cùng xuất hiện vì chọn hai đối tượng khác nhau.
• CASE tạo một giá trị, giữ nhánh đúng đầu tiên và không lọc dòng.
• Thiếu ELSE cho kết quả NULL khi không nhánh nào thỏa điều kiện.
• SUM của các giá trị 1 và 0 đo số dòng đạt điều kiện.

Kiểm tra nhanh

Đọc thêm

• SQLite mô tả WHERE, GROUP BY và HAVING.
• SQLite giải thích thứ tự nhánh và ELSE của CASE.