Những hiểu lầm phổ biến về GROUP BY trong SQL

  • Phuong Dang
  • 03/Feb/2022
  1. GROUP BY hoạt động như nào?
  2. Những hiểu lầm phổ biến.
  3. Kết luận.
GROUP BY là một trong những statement được sử dụng thường xuyên khi chúng ta muốn nhóm data và sử dụng với các aggregate functions. Nó không khó nhưng với một số bạn Beginer, Fresher do không thực sự hiểu đúng bản chất dẫn đến không thể đưa ra sự lựa chọn đúng đắn khi cần dùng.

1. GROUP BY hoạt động như nào?

GROUP BY statement sẽ nhóm những rows có data giống nhau tại một hoặc nhiều column thành một summary rows.
GROUP BY thường được kết hợp sử dụng với aggregate functions (SUM, COUNT, AVG...) để đưa ra các kết quả có tính chất tổng hợp.

GROUP BY syntax:

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);

Với những bài toán cần data tổng hợp, chức năng báo cáo như: "tính doanh thu theo thời gian, tính trung bình điểm của học viên..." thì GROUP BY là một solution được nghĩ đến đầu tiên. Để giải thích cách hoạt động của GROUP BY, chúng ta cùng làm 1 ví dụ.
Hãy sử dụng table Employees với structure/data cho trước và trả lời câu hỏi bên dưới:

SQL Statement trong bài viết này sẽ sử dụng SQL SERVER 2019 để demo source

EmployeeID (PK) FullName Email Level Status
1 Ken Sánchez ken@test.com Fresher Working
2 Brian Welcker brian@test.com Junior Working
3 Stephen Jiang stephen@test.com Senior Working
4 Linda Mitchell linda@test.com Senior Out
5 Michael Blythe michael@test.com Junior Unpaid Leave
6 Syed Abbas syed@test.com Fresher Working
7 Lynn Tsoflias lynn@test.com Fresher Working
8 David Bradley david@test.com Junior Working
9 Mary Gibson mary@test.com Senior Unpaid Leave
10 Clever clever@test.com Senior Working
[Q.1] Mỗi level trong công ty có bao nhiêu nhân viên?

* Phân tích: Với yêu cầu [Q.1], rõ ràng mục tiêu của chúng ta sẽ phải đi tìm: Có bao nhiêu nhân viên Fresher? Có bao nhiêu nhân viên Junior...?

GROUP BY Employee by Level
Figure 01. Nhóm nhân viên theo level.

Đối chiếu khái niệm của GROUP BY và mục đích nhóm data như Figure 01. Ta sẽ cần nhóm các nhân viên theo colum "Level" với query như sau:

SELECT Level, Count(1) AS TotalEmployee
FROM Employees
GROUP BY Level

Chúng ta sẽ được kết quả như bên dưới:

Level TotalEmployee
Fresher 3
Junior 3
Senior 4
Câu query đơn giản phải không?
Tuy nhiên cùng nhau xem bạn có những hiểu lầm/thiếu sót nào dưới đây không nhé.

2. Những hiểu lầm phổ biến.

#1. Lựa chọn sai column để group by

Với yêu cầu của [Q.1] rất nhiều bạn đưa ra sự lựa chọn là sẽ GROUP BY theo EmployeeID. Điều này hoàn toàn sai.
Chúng ta phải dựa vào yêu cầu để xác định rõ ràng 2 yếu tố:

1. ĐỐI TƯỢNG cần nhóm: Employee
2. TIÊU CHÍ muốn nhóm: Level

=> Column(s) cho GROUP BY clause luôn luôn là TIÊU CHÍ vì thế cần lựa chọn đúng TIÊU CHÍ thay vì ĐỐI TƯỢNG.

#2. Sử dụng Primary Key/Unique column(s) để GROUP BY

Mục đích chúng ta sử dụng GROUP BY là sẽ nhóm đối tượng theo một tiêu chí chung nào đó. Đồng nghĩa rằng nếu ta có X số lượng row(s) thì sau khi GROUP BY số lượng row(s) sẽ phải <= X.
Tuy nhiên với việc sử dụng GROUP BY với >= 1 column PK/Unique thì sau khi GROUP BY số lượng row(s) sẽ không thay đổi, vì chúng ta đang tìm điểm chung của tệp data bằng 1 TIÊU CHÍ luôn khác nhau.

=> Không bao giờ sử dụng Primary Key/Unique của tệp data để GROUP BY
Hãy lưu ý rằng EmployeeID là PK của Employees table. Nên khi chúng ta GROUP BY trên Employees table thì không bao giờ dùng EmployeeID. Tuy nhiên vẫn là column EmployeeID đó chúng ta hoàn toàn có thể sử dụng ở những table khác nếu EmployeeID không đóng vai trò là UNIQUE.

#3. Không xác định được khi nào nên dùng WHERE hoặc HAVING với GROUP BY

Đôi khi làm việc với GROUP BY chúng ta sẽ phải filter data theo 1 hoặc nhiều tiêu chí nào đó. Lúc này một số bạn sẽ bối rối giữa việc lựa chọn giữa WHERE và HAVING. Hãy cùng nhau so sánh:

# WHERE HAVING
Định nghĩa Dùng để filter row(s) data trước khi GROUP BY (Pre-filter). Dùng để filter row(s) data sau khi GROUP BY (Post-filter).
Vị trí Trước GROUP BY Sau GROUP BY
Hạn chế Không thể sử dụng với aggregate functions. Refer [Q.3] Chỉ filter được với các column trong GROUP BY clause hoặc trong aggregate functions. Refer [Q.2]
Performance
* Điều kiện có thể filter được cả ở WHERE và HAVING.		 Nhanh hơn Refer [Q.4] Chậm hơn

Để làm rõ hơn về những so sánh trên, chúng ta cùng nhau trả lời những câu hỏi tiếp theo

Với yêu cầu nhóm những nhân viên đang làm việc, thì chúng ta sẽ chỉ thực hiện thao tác với dữ liệu Status = 'Working'

# WHERE HAVING
SQL
SELECT Level, Count(1) AS TotalEmployee
FROM Employees
WHERE Status = 'Working'
GROUP BY Level
SELECT Level, Count(1) AS TotalEmployee
FROM Employees
GROUP BY Level
HAVING Status = 'Working'
Kết quả
Level TotalEmployee
Fresher 3
Junior 2
Senior 2
ERROR: Column 'Employees.Status' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.
=> Từ ví dụ này chúng ta có thể thấy HAVING sẽ chỉ filter được column nằm trong GROUP BY clause hoặc aggregate function.

Với yêu cầu số lượng nhân viên >= 4, thì chúng ta sẽ chỉ thực hiện thao tác với dữ liệu Count(1) >= 4

# WHERE HAVING
SQL
SELECT Level, Count(1) AS TotalEmployee
FROM Employees
WHERE COUNT(1) >= 4
GROUP BY Level
SELECT Level, Count(1) AS TotalEmployee
FROM Employees
GROUP BY Level
HAVING COUNT(1) >= 4
Kết quả ERROR: An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.
Level TotalEmployee
Senior 4
=> Từ ví dụ này chúng ta có thể thấy WHERE "Không thể sử dụng với aggregate functions"

# WHERE HAVING
SQL
SELECT Level, Count(1) AS TotalEmployee
FROM Employees
WHERE Level = 'Fresher'
GROUP BY Level
SELECT Level, Count(1) AS TotalEmployee
FROM Employees
GROUP BY Level
HAVING Level = 'Fresher'
Kết quả
Level TotalEmployee
Fresher 3

Chúng ta có thể thấy WHERE và HAVING đều trả về 1 kết quả. Tuy nhiên cách thức filter sẽ khác nhau.
Với WHERE (pre-filter) sẽ loại bỏ data trước khi GROUP BY, chúng ta chỉ còn 3 rows sau filter để GROUP BY và COUNT.
Ngược lại với HAVING (post-filter), chúng ta sẽ phải GROUP BY chia nhóm và COUNT với 10 rows sau đó mới loại bỏ data không phù hợp.

=> Trong trường hợp điều kiện có thể filter tại WHERE thì ưu tiên sử dụng WHERE trước để đảm bảo performance tốt hơn

Với yêu cầu này chúng ta cần filter 2 tiêu chí Status = 'Working' và COUNT(1) > 2

SELECT Level, Count(1) AS TotalEmployee
FROM Employees
WHERE Status = 'Working'
GROUP BY Level
HAVING COUNT(1) > 2
Level TotalEmployee
Fresher 3
=> WHERE và HAVING hoàn toàn có thể cùng nằm trong cùng 1 câu query

3. Kết luận

Trên đây là những hiểu lầm/thiếu sót phổ biến ở các bạn Beginer. Hi vọng rằng sau bài viết này chúng ta sẽ thực sự hiểu và sẽ có những sự lựa chọn đúng đắn khi dùng GROUP BY.

Source code tham khảo