Bây giờ là lúc đào sâu vào những lỗi điển hình mà tụi mình hay gặp khi dùng mấy cái hàm này. Ngay cả những cao thủ SQL cũng đôi khi dính chưởng, nên nhiệm vụ của tụi mình là học cách nhận diện và né mấy cái bẫy này cho chuẩn.
Bạn đã từng viết truy vấn mà nó báo lỗi gì đó bí ẩn kiểu "column must appear in the GROUP BY clause or be used in an aggregate function" chưa? Hoặc kết quả truy vấn nhìn kỳ kỳ mà không hiểu tại sao? Đó chỉ là phần nổi của tảng băng lỗi khi làm việc với hàm aggregate thôi. Bài này là cẩm nang sinh tồn của bạn giữa biển lỗi và hiểu lầm.
Lỗi 1: Dùng cột không aggregate ngoài GROUP BY
Vấn đề
Bạn viết truy vấn trả về dữ liệu aggregate, nhưng lại thêm một cột không nằm trong group và cũng không bọc bằng hàm aggregate. Ví dụ:
SELECT department, salary, SUM(salary)
FROM employees
GROUP BY department;
PostgreSQL sẽ báo ngay cho bạn:
ERROR: column "employees.salary" must appear in the GROUP BY clause or be used in an aggregate function
Tại sao lại vậy?
Khi bạn dùng GROUP BY, PostgreSQL sẽ gom dòng theo các cột chỉ định. Nhưng nếu bạn thêm một cột nữa (ở đây là salary), PostgreSQL không biết phải xử lý sao. Nó đâu biết bạn muốn lấy một lương bất kỳ, trung bình hay gì khác đâu.
Sửa sao? Có hai cách:
- Đảm bảo tất cả các cột không aggregate đều có mặt trong
GROUP BY:
SELECT department, salary
FROM employees
GROUP BY department, salary;
- Hoặc bọc cột đó bằng hàm aggregate nếu hợp lý:
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;
Tip: nếu PostgreSQL kêu ca về GROUP BY, tự hỏi: "Cột này mình có thực sự cần không? Nếu có, nó đóng vai trò gì?"
Lỗi 2: Dùng COUNT() và NULL không đúng
Vấn đề: bạn muốn đếm số nhân viên có khai bonus, và viết:
SELECT COUNT(bonus) AS bonus_count
FROM employees;
Nhưng kết quả lại ít hơn mong đợi. Tại sao? Vì COUNT(column) sẽ bỏ qua dòng mà column là NULL.
Giải pháp: nếu muốn đếm tất cả dòng, dùng COUNT(*):
SELECT COUNT(*) AS total_count
FROM employees;
Hoặc chỉ lấy dòng có bonus khác NULL:
SELECT COUNT(bonus) AS bonus_count
FROM employees
WHERE bonus IS NOT NULL;
Gợi ý: nếu bạn muốn phân biệt giữa dòng có NULL và không có dòng nào trong bảng, hãy cân nhắc kỹ giữa COUNT(*) và COUNT(column).
Lỗi 3: Quên lọc bằng HAVING thay vì WHERE
Vấn đề: bạn muốn tìm các phòng ban có lương trung bình trên 5000. Dev mới có thể viết kiểu này:
SELECT department, AVG(salary) AS avg_salary
FROM employees
WHERE AVG(salary) > 5000
GROUP BY department;
PostgreSQL sẽ báo lỗi:
ERROR: aggregate functions are not allowed in WHERE clause
Lý do là vì lọc bằng WHERE diễn ra trước khi group, còn hàm aggregate thì sau khi group mới tính. Nên AVG(salary) chưa được tính ở bước WHERE đâu.
Muốn sửa thì dùng HAVING để lọc dữ liệu đã aggregate:
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 5000;
Lỗi 4: Lọc với WHERE và lẫn lộn thứ tự thực thi
Vấn đề: bạn muốn biết số nhân viên ở các phòng ban mà tuổi lớn hơn 30. Truy vấn có thể như này:
SELECT department, COUNT(*)
FROM employees
GROUP BY department
WHERE age > 30;
PostgreSQL lại báo lỗi:
ERROR: syntax error at or near "WHERE"
Tại sao? WHERE luôn chạy trước GROUP BY. Ở đây bạn đặt WHERE sai chỗ rồi.
Để tránh lỗi này, hãy lọc dòng trước, rồi mới group.
SELECT department, COUNT(*)
FROM employees
WHERE age > 30
GROUP BY department;
Lỗi 5: Dùng NULL với SUM(), AVG() và các hàm khác
Vấn đề: bạn muốn tính tổng bonus đã phát cho nhân viên, và viết:
SELECT SUM(bonus) AS total_bonus
FROM employees;
Nhưng kết quả lại thấp bất thường. Đó là vì một nửa nhân viên không có bonus, và mấy cái NULL bị bỏ qua luôn.
Giải pháp: xử lý NULL trước. Ví dụ, thay NULL bằng 0:
SELECT SUM(COALESCE(bonus, 0)) AS total_bonus
FROM employees;
Bây giờ tất cả NULL sẽ thành 0, tổng sẽ chuẩn hơn.
Tụi mình sẽ tìm hiểu kỹ về hàm COALESCE trong vài bài tới.
Lỗi 6: Dùng nhiều hàm aggregate mà không hiểu mối liên hệ
Vấn đề: bạn muốn đếm tổng số nhân viên và tổng lương. Nhưng lại viết kiểu gì đó ra kết quả kỳ lạ:
SELECT COUNT(salary) AS count_salary, SUM(salary) AS total_salary
FROM employees;
Tại sao lại có thể sai? Nếu ai đó có lương là NULL, COUNT(salary) và SUM(salary) sẽ ra kết quả khác nhau, dễ gây rối.
Luôn nhớ là các hàm aggregate hoạt động độc lập. Nếu có NULL, kết quả sẽ khác nhau. Dùng COALESCE hoặc COUNT(*) để đảm bảo đồng nhất:
SELECT COUNT(*) AS total_employees, SUM(COALESCE(salary, 0)) AS total_salary
FROM employees;
Lỗi 7: Truy vấn chưa tối ưu với group lớn
Vấn đề: bạn chạy truy vấn group nhiều cột, và nó chạy 5 tiếng thay vì 5 phút:
SELECT department, job_title, location, COUNT(*)
FROM employees
GROUP BY department, job_title, location;
Trước khi group, hãy nghĩ xem có thực sự cần tất cả các cột trong GROUP BY không. Càng nhiều giá trị unique, truy vấn càng lâu. Nếu được, hãy giảm số cột group lại:
SELECT department, COUNT(*)
FROM employees
GROUP BY department;
Những lỗi này rất phổ biến, ngay cả dev SQL kỳ cựu cũng gặp. Hy vọng giờ bạn sẽ dễ dàng né mấy bãi mìn này và viết truy vấn chạy nhanh, đúng và đẹp hơn.
GO TO FULL VERSION