Bài 10: Bài tổng hợp: báo cáo theo khối

Ban tổ chức giao cô Mai một yêu cầu gồm nhiều điều kiện. Báo cáo chỉ xét người có từ 100 xu, chọn khối có tổng từ 150 xu và xếp tổng giảm dần. Nếu hai khối có tổng xu bằng nhau, khối nhỏ hơn phải đứng trước. Bạn đã biết các công cụ cần dùng. Lần này, bạn cần kết hợp chúng để đáp ứng đủ yêu cầu của ban tổ chức.

Phân biệt điều kiện cho học viên và cho khối

Trước khi gõ SQL, hãy xác định mỗi điều kiện áp dụng cho học viên hay cả khối. Mỗi dòng kết quả mô tả một khối lớp, nhưng mức từ 100 xu dùng để chọn từng học viên. Sau đó, ta cộng xu của những người được chọn trong khối và chỉ giữ tổng từ 150 xu.

Yêu cầu đầu ra cho biết bảng kết quả cần có những cột nào, giữ dòng nào và sắp xếp ra sao. Khi viết rõ từng phần, bạn dễ nhận ra điều kiện nào dùng để chọn người, điều kiện nào dùng để chọn khối.

Hãy thử riêng phần chọn người trước. Truy vấn dưới đây giúp bạn kiểm tra những học viên nào sẽ được tính vào báo cáo. Nó chưa phải bảng cuối cùng gửi ban tổ chức:

SELECT id, name, grade, coins
FROM students
WHERE coins >= 100
ORDER BY id;

An, Chi, Huy và Lan được giữ vì có ít nhất 100 xu. Bình có 90 và Dung có 60 nên bị loại. Khi giải thích báo cáo cuối, hãy gọi các chỉ số là số người và tổng xu sau lọc.

Nếu ban tổ chức muốn tổng xu toàn khối, điều kiện chọn người sẽ phải bỏ đi. Đây là thay đổi nội dung báo cáo, không phải cách viết tương đương. Một khối có thể đủ tổng xu nếu tính tất cả học viên. Nhưng nếu chỉ cộng xu của người đạt ngưỡng cá nhân, tổng đó có thể không đủ.

Ghép các bước thành truy vấn hoàn chỉnh

Bạn có thể đọc lần lượt từ bảng nguồn đến bảng kết quả để hiểu từng bước của truy vấn. Lọc người trước, gom những người còn lại, tính tổng rồi chọn nhóm. Cuối cùng mới xếp các dòng để trình bày:

Đây là trình tự dùng để giải thích ý nghĩa truy vấn. Khi thực thi, hệ quản trị có thể đọc và xử lý dữ liệu theo cách khác để chạy nhanh hơn. Bạn vẫn dựa vào các bước này để quyết định điều kiện thuộc về dòng hay nhóm.

Câu lệnh hoàn chỉnh đặt mỗi mệnh đề một dòng. Bí danh n và total cho biết hai cột kết quả được tính từ những giá trị nào:

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

Khối 8 và 9 đều có một người với tổng 180 xu. Khối 7 có một người và tổng 150 xu, vì Bình không qua bộ lọc đầu vào. Khối 6 có An đóng góp 120 xu, nhưng nhóm này bị HAVING loại.

Bạn có thể tạm bỏ điều kiện trong HAVING để kiểm tra báo cáo. Bảng trung gian sẽ cho thấy nhóm nào tồn tại trước khi HAVING quyết định. Cách này hữu ích khi một khối biến mất và bạn chưa rõ mất ở bước nào:

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

Nếu khối 7 hiện 240 thay vì 150, hãy kiểm WHERE. Nếu khối 6 vẫn còn trong bảng cuối, hãy kiểm HAVING. Tìm lỗi theo bước giúp bạn sửa đúng nguyên nhân thay vì thử đổi nhiều phần cùng lúc.

Kiểm thứ tự khi hai tổng bằng nhau

Ban tổ chức muốn đọc tổng lớn trước, nên dùng total DESC. Tuy nhiên, yêu cầu này chưa quyết định thứ tự giữa khối 8 và 9 vì hai tổng bằng nhau. Bạn cần thêm cột sắp xếp thứ hai để quy định khối nào đứng trước khi tổng bằng nhau.

Tiêu chí phụ là cột sắp xếp được xét khi các dòng bằng nhau ở tiêu chí trước. Trong báo cáo này, grade ASC đặt khối nhỏ hơn trước. Nếu xếp khối giảm dần khi tổng bằng nhau như dưới đây, các phép tính vẫn đúng. Tuy nhiên, thứ tự hai khối sẽ ngược yêu cầu:

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

Khối 9 đứng trước khối 8 vì grade DESC chọn khối lớn hơn khi hòa. Đổi phần ấy thành grade ASC mới đáp ứng yêu cầu. Không cần đổi công thức tổng hay điều kiện giữ nhóm để sửa lỗi thứ tự.

Nếu dùng LIMIT để chỉ lấy vài dòng đầu, bạn càng cần quy định thứ tự khi tổng bằng nhau. Khi nhiều tổng bằng nhau, cột sắp xếp phụ có thể quyết định nhóm nào được lấy và nhóm nào nằm ngoài giới hạn. Vì vậy, hãy hoàn thành quy tắc sắp xếp trước khi giới hạn số dòng.

Thử điểm bằng ngưỡng và trường hợp không có nhóm

Một truy vấn đúng trên dữ liệu ban đầu chưa đủ chứng minh mọi điều kiện đã được viết đúng. Giá trị biên là giá trị nằm ngay tại ranh giới giữ hoặc loại. Với yêu cầu từ 100 xu và tổng từ 150 xu, bạn cần thử cả học viên có đúng 100 xu và nhóm có đúng 150 xu.

Mục này dùng bảng minh họa riêng report_sample, không thay dữ liệu học viên chung. Bảng có ba dòng: khối 6 nhận 100 và 50 xu, khối 7 nhận 150 xu. Bảng thử này chỉ được chuẩn bị cho các ví dụ có ghi setup=kiem-bien, tách riêng khỏi dữ liệu học viên chung.

Hãy áp dụng cùng yêu cầu lên bảng nhỏ này. Các giá trị được chọn để phân biệt bộ lọc cá nhân với tổng nhóm:

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

Khối 7 còn lại với một người và tổng 150. Dòng 50 của khối 6 bị loại trước khi cộng, nên tổng khối 6 chỉ bằng 100. Nếu cộng trước rồi mới lọc, bạn sẽ giữ nhầm khối 6 với tổng 150.

Điều kiện dùng >= sẽ giữ cả giá trị đúng bằng ngưỡng, còn > chỉ giữ giá trị lớn hơn. Dữ liệu này chứng minh tổng 150 cần được giữ. Khi tự thử thêm, hãy chọn cả giá trị ngay dưới và ngay trên ranh giới để quan sát hướng thay đổi.

Một báo cáo khác chỉ tính học viên đã có người hướng dẫn trong cột mentor. Sau đó, báo cáo giữ những khối có ít nhất hai học viên như vậy. Hãy kiểm trường hợp không nhóm nào đạt mà truy vấn vẫn chạy bình thường:

SELECT grade, COUNT(*) AS n
FROM students
WHERE mentor IS NOT NULL
GROUP BY grade
HAVING COUNT(*) >= 2
ORDER BY grade;

Kết quả rỗng là hợp lệ, dù toàn câu lạc bộ có bốn người được hướng dẫn. Điều kiện xét từng nhóm, không xét tổng số người trên toàn bảng. Trong trường hợp này, hãy giữ kết quả rỗng để thể hiện đúng rằng chưa có khối nào đủ điều kiện.

Đếm người đạt mà vẫn giữ địa phương có số 0

Ban tổ chức còn cần số người có từ 150 xu theo địa phương. Báo cáo phải có mọi thành phố xuất hiện trong danh sách học viên. Thành phố chưa có người đạt vẫn cần một dòng mang số 0.

Nếu đặt điều kiện xu trong WHERE, thành phố không còn người sẽ mất nhóm. Thay vào đó, hãy giữ mọi người và dùng CASE để mỗi dòng góp 1 hoặc 0. Phép cộng được tính riêng trong từng nhóm thành phố:

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

Hà Nội có Chi và Lan đạt ngưỡng nên số đếm bằng 2. Cần Thơ có Huy nên bằng 1, còn Huế và Đà Nẵng bằng 0. Số 0 cho biết thành phố đó có học viên trong danh sách, nhưng chưa ai có đủ 150 xu.

Một thành phố chưa xuất hiện trong students vẫn không có dòng. CASE giữ các nhóm đã có, không tạo danh sách địa phương mới. Phân biệt hai trường hợp này giúp bạn mô tả chính xác giới hạn của báo cáo.

Lỗi thường gặp

Tóm tắt

• Trước khi viết SQL, hãy xác định báo cáo nói về ai, cần tính gì, giữ những dòng nào và sắp xếp ra sao.
• Nếu kết quả sai, hãy xem những học viên còn lại sau WHERE, rồi kiểm tra tổng từng nhóm trước HAVING.
• Nếu hai dòng bằng nhau ở cột sắp xếp đầu tiên, cột phụ sẽ quyết định dòng nào đứng trước.
• Hãy thử cả số xu đúng bằng ngưỡng và trường hợp không nhóm nào đủ điều kiện.
• CASE cho phép đếm người đạt trong khi vẫn giữ nhóm có số đếm 0.

Kiểm tra nhanh

Đọc thêm

• SQLite mô tả các bước xử lý SELECT và quy tắc ORDER BY.
• SQLite mô tả CASE dùng để tạo giá trị cho từng dòng.