Bài 7: Tổng hợp nhiều dòng thành một con số

Cô Mai chuẩn bị báo cáo quy mô câu lạc bộ và số xu học viên đang giữ. Cô đã có danh sách từng người, nhưng cần đếm số học viên và tính tổng xu để đưa vào báo cáo. Cô còn muốn phân biệt số học viên được hướng dẫn với số người hướng dẫn khác nhau. Nếu nhầm số lượt phân công với số người hướng dẫn, cô sẽ đưa ra kết luận sai dù phép tính đúng.

Đếm đúng thứ đang cần đếm

Trước khi đếm, bạn cần biết mỗi dòng trong bảng ghi thông tin về ai hoặc việc gì. Trong bảng students, một dòng là một học viên. Đếm dòng của bảng này sẽ cho số học viên, kể cả bạn chưa có người hướng dẫn.

Hàm tổng hợp (aggregate function) nhận các giá trị từ nhiều dòng và tính một giá trị chung. Hàm COUNT(*) đếm số dòng đầu vào. Khi chưa chia nhóm, hàm tính chung trên toàn bộ các dòng được chọn:

SELECT COUNT(*) AS students_count
FROM students;

Kết quả chỉ có một dòng, với cột students_count bằng 6. Dấu sao trong COUNT(*) yêu cầu đếm dòng, không yêu cầu liệt kê tất cả cột. Vì vậy, nó có vai trò khác dấu sao trong truy vấn lấy toàn bộ cột.

Cô Mai còn hỏi có bao nhiêu học viên đã được phân người hướng dẫn. COUNT(mentor) chỉ đếm những ô mentor có giá trị. Hãy so sánh hai cách đếm trên cùng bảng nguồn:

SELECT COUNT(*) AS students_count,
       COUNT(mentor) AS assigned
FROM students;

Kết quả có students_count bằng 6 và assigned bằng 4. Bình và Dung vẫn là học viên, nhưng ô người hướng dẫn của hai bạn là NULL. COUNT(*) vẫn đếm hai dòng của Bình và Dung. Còn COUNT(mentor) bỏ qua hai ô thiếu giá trị đó.

Nếu câu hỏi chuyển sang số người hướng dẫn khác nhau, phải loại tên lặp trước khi đếm. Từ khóa DISTINCT đã học có thể đặt bên trong COUNT. Truy vấn sau cho biết có bao nhiêu lượt phân công và bao nhiêu tên người hướng dẫn khác nhau:

SELECT COUNT(mentor) AS assignments,
       COUNT(DISTINCT mentor) AS teacher_names
FROM students;

assignments bằng 4, còn teacher_names bằng 2 vì hai tên Cô Mai và Thầy Nam đều xuất hiện nhiều lần. Giá trị NULL vẫn không được đếm. Hai người hướng dẫn có thể trùng tên. Vì vậy, kết quả này cho biết số tên khác nhau, chưa chắc là số người hướng dẫn thực tế.

Cộng, lấy trung bình và tìm hai đầu

Cô Mai cần biết số xu của cả câu lạc bộ. Hàm SUM cộng các giá trị của cột được chỉ định. Hãy chọn cột chứa số cần cộng. Ở đây, cột coins lưu số xu mỗi học viên đang giữ:

SELECT SUM(coins) AS total_coins
FROM students;

Tổng là 780 xu, không phải số lần học viên nhận thưởng. students.coins mô tả số xu đang giữ tại thời điểm đọc. Kết quả này không giải thích nguồn gốc từng khoản xu.

Hàm AVG tính trung bình của những giá trị đã có. Hàm MIN chọn giá trị nhỏ nhất, còn hàm MAX chọn giá trị lớn nhất. Các phép tính sau cùng đọc số xu của sáu người:

SELECT AVG(coins) AS average_coins,
       MIN(coins) AS smallest_coins,
       MAX(coins) AS largest_coins
FROM students;

average_coins bằng 130 vì tổng 780 được chia cho sáu giá trị. Dung giữ 60 xu, còn Chi và Huy cùng giữ 180 xu. MAX(coins) trả giá trị 180 một lần; nó không trả danh sách hai người đạt mức đó.

Hãy nhìn kết quả của chính cách viết cần tránh:

SELECT name, SUM(coins) AS total_coins
FROM students;

Truy vấn vẫn cho tổng xu đúng, nên bạn có thể bỏ sót lỗi nếu chỉ kiểm tra con số. Vấn đề nằm ở ý nghĩa cột name: báo cáo không quy định chọn người nào. Với báo cáo toàn câu lạc bộ, hãy bỏ cột tên riêng. Bạn có thể đặt bí danh total_coins để nói rõ đây là tổng xu.

Lọc đầu vào và xử lý tập rỗng

Cô Mai muốn xem riêng khối 6. WHERE chọn các dòng trước khi hàm tổng hợp tính toán. Nếu đổi điều kiện lọc, bạn sẽ tính trên những học viên khác dù giữ nguyên công thức:

SELECT COUNT(*) AS n,
       SUM(coins) AS total,
       AVG(coins) AS average
FROM students
WHERE grade = 6;

Hai giá trị 120 và 60 cho tổng 180, trung bình 90. Cột n đếm người đã qua bộ lọc, không đếm toàn câu lạc bộ. Vì vậy, tiêu đề báo cáo cần nói rõ đang xét khối nào.

Luồng dưới đây thể hiện dữ liệu đi vào phép tính. Bạn có thể đọc truy vấn theo các bước này để hiểu kết quả. Khi thực thi, máy có thể đọc dữ liệu theo cách khác:

Khối 12 hiện chưa có học viên. Khi không có dòng đầu vào, phép đếm và phép cộng cho kết quả khác nhau. Hãy chạy cùng công thức với khối chưa xuất hiện:

SELECT COUNT(*) AS n,
       SUM(coins) AS total,
       AVG(coins) AS average
FROM students
WHERE grade = 12;

Kết quả vẫn có một dòng vì truy vấn yêu cầu tính chung, chưa chia nhóm. n bằng 0 xác nhận không có học viên thỏa điều kiện. total và average là NULL, biểu thị chưa có giá trị để cộng hoặc tính trung bình.

Nếu quy định báo cáo yêu cầu tổng rỗng hiển thị 0, bạn có thể dùng COALESCE đã học. Chỉ đổi NULL thành 0 khi quy định báo cáo cho phép hiển thị như vậy. Hãy giữ cả số người để người đọc phân biệt nguồn rỗng với một nhóm thực sự có tổng bằng 0:

SELECT COUNT(*) AS n,
       COALESCE(SUM(coins), 0) AS total
FROM students
WHERE grade = 12;

Đọc đơn vị của lịch sử trước khi cộng

Trong bảng ledger, một dòng là một lần số xu thay đổi. Cột delta là lượng thay đổi: số dương làm tăng, số âm làm giảm. Cộng cột này sẽ cho thay đổi ròng trong phần lịch sử đang xét.

Để đọc riêng học viên mã 1, hãy giữ cả khoản cộng và khoản trừ. Câu sau chưa dùng kiến thức ghép bảng; nó chỉ lọc theo mã đã lưu trong lịch sử:

SELECT SUM(delta) AS net_change
FROM ledger
WHERE student_id = 1;

Kết quả 10 là lượng tăng ròng trong các dòng được chọn. Nó khác số dư 120 của An đang hiển thị trong bảng học viên. Muốn tính số dư cuối từ lịch sử, bạn còn cần số dư đầu và phạm vi lịch sử đầy đủ.

Nếu chỉ lấy các dòng dương, câu hỏi đã đổi thành tổng thu vào. So sánh kết quả sau với tổng ròng để thấy vai trò của bộ lọc:

SELECT SUM(delta) AS incoming
FROM ledger
WHERE student_id = 1 AND delta > 0;

incoming bằng 20 vì khoản trừ đã bị loại. Cả hai truy vấn đều chạy đúng, nhưng trả lời hai câu hỏi khác nhau. Hãy ghi rõ đang đo tổng thu, thay đổi ròng hay số dư trước khi chọn công thức.

Tương tự, một dòng scores là một lần chấm bài. AVG(score) trên bảng ấy tính trung bình theo lần chấm. Người có nhiều lần chấm đóng góp nhiều giá trị hơn, nên kết quả chưa phải trung bình mỗi người có trọng số bằng nhau.

Hãy tính trung bình trên mọi lần chấm trong bảng scores:

SELECT AVG(score) AS average_score
FROM scores;

Kết quả khoảng 81,4 là trung bình của bảy lần chấm, dù chỉ có năm học viên được chấm. An và Chi mỗi người đóng góp hai điểm số. Những bạn còn lại có điểm mỗi người chỉ đóng góp một giá trị.

Để xem riêng An, hãy lọc các lần chấm theo mã học viên của bạn ấy:

SELECT AVG(score) AS average_score
FROM scores
WHERE student_id = 1;

Trung bình riêng của An là 85, khác trung bình chung theo lần chấm. Muốn tính trung bình theo người, trước hết cần gom các lần chấm của mỗi người thành một nhóm. Bạn sẽ học cách gom nhóm ở bài 8.

Lỗi thường gặp

Tóm tắt

• Chọn đối tượng đếm trước: dòng, ô đã có giá trị, hay giá trị khác nhau.
• SUM tính tổng, AVG tính trung bình, còn MIN và MAX tìm giá trị nhỏ nhất và lớn nhất. Hãy đặt bí danh rõ nghĩa cho từng kết quả.
• WHERE quyết định những dòng nào góp vào phép tính chung.
• Nếu nguồn rỗng, COUNT trả 0, còn SUM và AVG trả NULL.
• Tổng thay đổi trong lịch sử cần được phân biệt với số dư đang giữ.

Kiểm tra nhanh

Đọc thêm

• SQLite giải thích COUNT, SUM, AVG, MIN và MAX.
• SQLite giải thích cột chưa tổng hợp trong truy vấn tổng hợp.