Cách sử dụng GROUP BY và HAVING trong SQL

Chúng ta sẽ sử dụng Cơ sở dữ liệu Các Công ty Kỳ lân, có sẵn trên DataLab, sổ tay dữ liệu hỗ trợ AI của DataCamp. Các công ty này được gọi là Kỳ lân vì là startup có định giá trên một tỷ đô la. Để đơn giản, chúng ta sẽ tập trung vào ba bảng: companies, sales và product_emissions.  Để tự chạy toàn bộ mã ví dụ trong hướng dẫn này, bạn có thể tạo một workbook DataLab miễn phí với cơ sở dữ liệu và toàn bộ mẫu mã đã được tải sẵn.

Sử dụng SQL GROUP BY

GROUP BY là một lệnh SQL thường được dùng để tổng hợp dữ liệu nhằm rút ra insight. Có ba giai đoạn khi bạn nhóm dữ liệu:

• Chia (Split): bộ dữ liệu được chia thành các cụm hàng dựa trên giá trị của các biến ta chọn để tổng hợp
• Áp dụng (Apply): Tính một hàm tổng hợp, như trung bình, nhỏ nhất và lớn nhất, trả về một giá trị duy nhất
• Kết hợp (Combine): Tất cả các đầu ra thu được được kết hợp lại thành một bảng duy nhất. Theo cách này, chúng ta sẽ có một giá trị cho mỗi biến thể (modality) của biến quan tâm.

Ví dụ SQL GROUP BY 1

Ta bắt đầu với một ví dụ đơn giản về GROUP BY. Giả sử ta muốn tìm mười quốc gia có số lượng công ty Kỳ lân nhiều nhất. 

SELECT * 
FROM companies

Sẽ thật hữu ích nếu sắp xếp kết quả theo thứ tự giảm dần dựa trên số lượng công ty

SELECT country, COUNT(*) AS n_companies
FROM companies
GROUP BY country
ORDER BY n_companies DESC
LIMIT 10

Đây là kết quả. Có lẽ bạn sẽ không ngạc nhiên khi thấy Mỹ, Trung Quốc và Ấn Độ trong bảng xếp hạng. Hãy giải thích quyết định đằng sau truy vấn này: 

• Đầu tiên, lưu ý rằng chúng ta dùng COUNT(*) để đếm số hàng cho mỗi nhóm, tương ứng với quốc gia. Ngoài ra, ta cũng dùng bí danh SQL để đổi tên cột thành tên dễ hiểu hơn. Điều này thực hiện bằng từ khóa AS, theo sau là tên mới. COUNT được trình bày chi tiết hơn trong hướng dẫn Hàm COUNT() trong SQL.

• Các trường được chọn từ bảng companies, nơi mỗi hàng tương ứng với một công ty Kỳ lân. 

• Tiếp đó, ta cần chỉ định tên cột sau GROUP BY để tổng hợp dữ liệu theo quốc gia. 

• ORDER BY là cần thiết để hiển thị các quốc gia theo đúng thứ tự, từ số lượng công ty cao nhất đến thấp hơn. 

• Ta giới hạn kết quả còn 10 bằng LIMIT, theo sau là số hàng bạn muốn trong kết quả.

Ví dụ SQL GROUP BY 2

Bây giờ, chúng ta sẽ phân tích bảng doanh số. Với mỗi số đơn hàng, ta có loại khách hàng, dòng sản phẩm, số lượng, đơn giá, tổng cộng, v.v.

Lần này, ta quan tâm đến việc tìm giá trung bình mỗi đơn vị, tổng số đơn hàng và tổng lợi nhuận cho mỗi dòng sản phẩm:

SELECT 
    product_line,
    AVG(unit_price) AS avg_price,
    SUM(quantity) AS tot_pieces,
    SUM(total) AS total_gain
FROM sales
GROUP BY product_line
ORDER BY total_gain DESC

• Thay vì đếm số hàng, ta dùng hàm AVG() để lấy giá trung bình và SUM() để tính tổng số đơn hàng và tổng lợi nhuận cho mỗi dòng sản phẩm. 

• Như trước, ta chỉ định cột ban đầu dùng để chia bộ dữ liệu thành các cụm. Sau đó các hàm tổng hợp sẽ cho phép ta thu được một hàng cho mỗi biến thể của dòng sản phẩm. 

• Lần này, ORDER BY là tùy chọn. Nó được đưa vào để nhấn mạnh rằng tổng lợi nhuận cao hơn không phải lúc nào cũng tỷ lệ thuận với giá trung bình cao hơn hoặc tổng số sản phẩm. 

Hạn chế của WHERE

Hãy lấy lại ví dụ trước. Giờ ta muốn đặt một điều kiện cho truy vấn: chỉ lọc các dòng có tổng số đơn hàng lớn hơn 40.000. Hãy thử mệnh đề WHERE:

SELECT 
    product_line,
    AVG(unit_price) AS avg_price,
    SUM(quantity) AS tot_pieces,
    SUM(total) AS total_gain
FROM sales
WHERE SUM(total) > 40000
GROUP BY product_line
ORDER BY total_gain DESC

Truy vấn này sẽ trả về lỗi sau:

Lỗi này cho biết không thể dùng các hàm tổng hợp trong mệnh đề WHERE. Chúng ta cần một lệnh mới để giải quyết vấn đề này.

Sử dụng SQL HAVING

Tương tự WHERE, mệnh đề HAVING lọc các hàng của một bảng. Trong khi WHERE cố gắng lọc toàn bộ bảng, HAVING lọc các hàng trong từng nhóm được xác định bởi GROUP BY

Ví dụ SQL HAVING 1

Đây lại là ví dụ trước, thay từ WHERE bằng HAVING.

SELECT 
    product_line,
    AVG(unit_price) AS avg_price,
    SUM(quantity) AS tot_pieces,
    SUM(total) AS total_gain
FROM sales
GROUP BY product_line
HAVING SUM(total) > 40000
ORDER BY total_gain DESC

Lần này truy vấn trả về ba hàng. Các dòng sản phẩm khác không đáp ứng tiêu chí, nên số kết quả từ sáu còn ba. 

Bạn còn nhận thấy gì từ truy vấn? Chúng ta không truyền bí danh cột vào HAVING, mà dùng phép tổng hợp của trường gốc. Bạn tự hỏi vì sao? Bạn sẽ giải được điều này ở ví dụ tiếp theo.

Ví dụ SQL HAVING 2

Ở ví dụ cuối, ta sẽ dùng bảng product_emissions, chứa lượng phát thải của các sản phẩm do các công ty cung cấp.

Lần này, ta quan tâm đến việc hiển thị lượng phát thải carbon sản phẩm trung bình (pcf) cho mỗi công ty thuộc nhóm ngành “Technology Hardware & Equipment.” Bên cạnh đó, sẽ hữu ích nếu xem số lượng sản phẩm của từng công ty để hiểu liệu có mối quan hệ nào giữa số lượng sản phẩm và lượng phát thải carbon hay không. Chúng ta cũng tiếp tục dùng HAVING để trích xuất các công ty có lượng phát thải carbon trung bình trên 100.

SELECT pe.company, count(product_name) AS n_products, avg(carbon_footprint_pcf) AS avg_carbon_footprint_pcf
FROM product_emissions AS pe
WHERE industry_group = 'Technology Hardware & Equipment'
GROUP BY pe.company, industry_group
having avg_carbon_footprint_pcf>100
ORDER BY n_products

Một lỗi xuất hiện sau khi thử dùng bí danh. Với mệnh đề HAVING, tên cột mới chưa tồn tại, nên nó không thể lọc truy vấn. Hãy sửa lại yêu cầu:

SELECT pe.company, count(product_name) AS n_products, avg(carbon_footprint_pcf) AS avg_carbon_footprint_pcf
FROM product_emissions AS pe
WHERE industry_group = 'Technology Hardware & Equipment'
GROUP BY pe.company, industry_group
having avg(carbon_footprint_pcf)>100
ORDER BY n_products

Lần này, điều kiện đã hoạt động và ta có thể xem kết quả từ bảng. Ta vừa học được rằng bí danh cột không thể dùng trong HAVING vì điều kiện này được áp dụng trước SELECT. Do đó, nó không thể nhận diện các trường theo tên mới.

Thứ tự thực thi trong SQL

Đây là thứ tự các lệnh khi viết truy vấn:

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

Nhưng có một câu hỏi bạn cần tự đặt ra. Các lệnh SQL thực thi theo thứ tự nào? Với con người, ta thường mặc định máy tính đọc và diễn giải SQL từ trên xuống dưới. Nhưng thực tế khác với bề ngoài. Đây mới là thứ tự thực thi đúng:

FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY
LIMIT

Vì vậy, bộ xử lý truy vấn không bắt đầu từ SELECT, mà bắt đầu bằng việc chọn các bảng cần đưa vào, và SELECT được thực thi sau HAVING. Điều này giải thích vì sao HAVING không cho phép dùng ALIAS, trong khi ORDER BY thì không gặp vấn đề. Bên cạnh đó, thứ tự thực thi này cũng làm rõ lý do HAVING được dùng cùng GROUP BY để áp dụng điều kiện trên dữ liệu đã tổng hợp, còn WHERE thì không thể. 

Tôi khuyên bạn đọc thêm hướng dẫn Thứ tự Thực thi SQL: Hiểu Cách Truy vấn Chạy, bài viết này có giải thích chi tiết nếu bạn muốn tìm hiểu thêm.

Nâng trình độ của bạn

Sau khi đọc hướng dẫn này, bạn sẽ có cái nhìn rõ ràng về sự khác nhau giữa GROUP BY và HAVING. Bạn có thể luyện tập trên DataLab để thành thạo các khái niệm này.

Nếu bạn muốn tiến lên cấp độ tiếp theo trong lộ trình học SQL, bạn có thể tham gia khóa SQL Trung cấp. Nếu bạn vẫn cần củng cố nền tảng SQL, bạn có thể quay lại khóa Nhập môn SQL để học các kiến thức cơ bản của ngôn ngữ này.