MySQL Các hàm tổng hợp: SUM, COUNT, AVG & TỐI ĐA

Hàm tổng hợp là gì trong MySQL?

An chức năng tổng hợp Đọc nhiều hàng của một cột duy nhất và gộp chúng thành một giá trị. Hàm tổng hợp chủ yếu là về:

• Thực hiện phép tính trên nhiều hàng
• Của một cột duy nhất của bảng
• Và trả về một giá trị duy nhất.

Tiêu chuẩn ISO định nghĩa năm (5) chức năng tổng hợp, cụ thể là:

1. ĐẾM
2. TÓM TẮT
3. AVG
4. MIN
5. MAX

Một quy tắc áp dụng cho cả năm quy tắc: Các hàm tổng hợp bỏ qua giá trị NULL. COUNT(*) là trường hợp ngoại lệ duy nhất, và chúng ta sẽ xem xét lý do bên dưới.

Tại sao nên sử dụng hàm tổng hợp

Các cấp bậc trong tổ chức có nhu cầu thông tin khác nhau. Các nhà quản lý cấp cao thường quan tâm đến số liệu tổng thể, chứ không phải các chi tiết riêng lẻ.

Các hàm tổng hợp cho phép chúng ta dễ dàng tạo ra dữ liệu tóm tắt từ cơ sở dữ liệu của mình.

Ví dụ, từ cơ sở dữ liệu myflix của chúng tôi, ban quản lý có thể yêu cầu các báo cáo sau:

• Phim được thuê ít nhất
• Phim được thuê nhiều nhất
• Số lần trung bình mỗi bộ phim được thuê trong một tháng.

Tất cả các báo cáo trên đều được tạo ra từ các hàm tổng hợp. Chúng ta hãy cùng xem xét chi tiết từng hàm.

COUNT hàm

Hàm COUNT trả về tổng số giá trị trong trường được chỉ định, áp dụng cho cả kiểu dữ liệu số và phi số. Giống như mọi hàm tổng hợp khác, COUNT(column) loại trừ các giá trị NULL.

COUNT(*) là một dạng đặc biệt trả về số lượng tất cả các hàng trong một bảng. Nó cũng đếm NULL và trùng lặp, vì nó đếm số hàng chứ không phải giá trị.

Bảng movierentals chứa dữ liệu này:

Giả sử chúng ta muốn tìm số lần bộ phim có ID 2 đã được thuê.

SELECT COUNT(`movie_id`) FROM `movierentals` WHERE `movie_id` = 2;

Thực hiện điều này trong MySQL Workbench Khi so sánh với myflixdb, kết quả trả về là 3, vì có ba hàng có movie_id là 2.

Từ khóa DISTINCT

Câu hỏi COUNT trả lời "có bao nhiêu?". Câu hỏi tiếp theo thường là "có bao nhiêu?". khác nhau "những cái", và đó là mục đích của DISTINCT.

Từ khóa DISTINCT loại bỏ các kết quả trùng lặp bằng cách nhóm chúng lại.ping Các giá trị giống hệt nhau được đặt cạnh nhau, chính xác như hình minh họa ở trên.

Trước tiên, hãy thực hiện một truy vấn đơn giản.

SELECT `movie_id` FROM `movierentals`;

Bây giờ, hãy thử truy vấn tương tự với từ khóa DISTINCT:

SELECT DISTINCT `movie_id` FROM `movierentals`;

Lệnh DISTINCT loại bỏ các bản ghi trùng lặp:

COUNT so với COUNT(*) so với COUNT(DISTINCT): Bạn nên sử dụng cái nào?

DISTINCT cũng có thể được đặt trong một hàm tổng hợp, và đây là điểm mà hầu hết người mới bắt đầu đều mắc sai lầm. tracTrong đó, k hàng thực sự được đếm. Bốn biểu mẫu bên dưới đều chạy trên cùng một bảng cho thuê phim gồm năm hàng được hiển thị trước đó, nhưng chúng không trả về cùng một số. Sự khác biệt nằm ở hai câu hỏi: biểu mẫu đếm hàng hay giá trị, và nó có giữ lại các giá trị trùng lặp không?

SELECT COUNT(*) AS `all_rows`,
       COUNT(`return_date`) AS `returned_rows`,
       COUNT(DISTINCT `movie_id`) AS `unique_movies`
FROM `movierentals`;

💡 Mẹo: Sử dụng COUNT(*) để đếm số hàng, COUNT(column) khi NULL có nghĩa là "không áp dụng", và COUNT(DISTINCT column) cho các giá trị duy nhất. Ngược lại với DISTINCT là ALL — mặc định, và do đó hiếm khi được viết ra.

Hàm MIN

Hàm MIN trả về giá trị nhỏ nhất trong trường bảng đã chỉ định.

Giả sử chúng ta muốn tìm năm phát hành của bộ phim cũ nhất trong thư viện của mình. MySQLHàm MIN của 's cung cấp cho chúng ta điều đó.

SELECT MIN(`year_released`) FROM `movies`;

Kết quả:

Hàm MAX

Đúng như tên gọi, hàm MAX ngược lại với hàm MIN. Nó trả về giá trị lớn nhất từ ​​trường bảng đã chỉ định.

Giả sử chúng ta muốn tìm năm phát hành của bộ phim mới nhất trong cơ sở dữ liệu. Ví dụ sau sẽ trả về thông tin đó.

SELECT MAX(`year_released`) FROM `movies`;

Kết quả:

Hàm SUM

MIN và MAX chọn một giá trị hiện có từ một cột. SUM và AVG Tính toán một số mới từ toàn bộ cột.

Giả sử chúng ta muốn biết tổng số tiền đã thanh toán cho đến nay. MySQL TÓM TẮT chức năng Trả về tổng của tất cả các giá trị trong cột được chỉ định.. SUM chỉ hoạt động trên các trường sốvà Các giá trị NULL sẽ bị loại trừ khỏi kết quả..

Bảng sau đây hiển thị dữ liệu trong bảng thanh toán.

Truy vấn dưới đây lấy tất cả các khoản thanh toán đã thực hiện và cộng chúng lại thành một kết quả duy nhất: 2500 + 2000 + 6000 = 10500.

SELECT SUM(`amount_paid`) FROM `payments`;

Kết quả:

AVG chức năng

MySQL AVG chức năng trả về giá trị trung bình của các giá trị trong một cột được chỉ định. Cũng giống như hàm SUM, nó chỉ hoạt động trên các kiểu dữ liệu số.

Giả sử chúng ta muốn tìm số tiền trung bình đã thanh toán. Chúng ta có thể sử dụng truy vấn sau, chia tổng số 10500 cho ba dòng thanh toán không phải NULL.

SELECT AVG(`amount_paid`) FROM `payments`;

Kết quả:

⚠️ Cảnh báo: AVG Phép tính này chia cho số hàng không phải NULL, chứ không phải cho tổng số hàng của bảng. Giá trị NULL sẽ được bỏ qua thay vì tính là 0, điều này âm thầm đẩy giá trị trung bình lên. Hãy sử dụng... AVG(IFNULL(`amount_paid`, 0)) khi giá trị bị thiếu có nghĩa là bằng không.

Ví dụ thực tế: Kết hợp các hàm tổng hợp với mệnh đề GROUP BY

Mỗi hàm trên đều trả về một con số cho toàn bộ bảng. Thêm một NHÓM THEO mệnh đề trả về một con số mỗi nhóm Thay vào đó — và đó mới là cách mà những báo cáo thực sự được xây dựng.

Ví dụ sau đây nhóm các thành viên theo tên, sau đó đếm tổng số lần thanh toán, số tiền thanh toán trung bình và tổng số tiền thanh toán cho mỗi thành viên.

SELECT m.`full_names`,
       COUNT(p.`payment_id`) AS `paymentscount`,
       AVG(p.`amount_paid`) AS `averagepaymentamount`,
       SUM(p.`amount_paid`) AS `totalpayments`
FROM members m, payments p
WHERE m.`membership_number` = p.`membership_number`
GROUP BY m.`full_names`;

Thực hiện ví dụ trên trong MySQL Workbench cho chúng ta các kết quả sau.

Truy vấn kết hợp hai bảng trong mệnh đề WHERE — kiểu kết hợp bằng dấu phẩy cũ. Mã hiện đại viết cùng logic đó dưới dạng một truy vấn tường minh. INNER JOIN … ONCũng cần lưu ý rằng mọi cột không được tổng hợp trong danh sách SELECT phải xuất hiện trong mệnh đề GROUP BY, hoặc MySQL Từ phiên bản 5.7 trở lên, truy vấn sẽ bị từ chối nếu sử dụng ONLY_FULL_GROUP_BY. Xem thêm chính thức MySQL tham chiếu hàm tổng hợp.

Câu Hỏi Thường Gặp