Ở bài viết trước, mình đã chia sẻ tới các bạn bốn nhóm hàm phổ biến trong SQL: hàm scalar, hàm ranking, hàm logic và hàm rowset. Các bạn có thể xem bài viết đó tại đây nhé.
Trong bài viết này, mình sẽ tiếp tục chia sẻ tới các bạn nhóm hàm cực kỳ quan trọng trong SQL: nhóm hàm tổng hợp (Aggregate).
T-SQL cung cấp các hàm tổng hợp (hàm aggregate) như SUM, MAX và AVG để thực hiện các phép tính nhận nhiều giá trị và trả về một kết quả duy nhất.
Các nội dung chính
Làm việc với các hàm Aggregate
Hầu hết các truy vấn chúng ta đã xem đều hoạt động trên một hàng tại một thời điểm, sử dụng mệnh đề WHERE để lọc các hàng. Mỗi hàng được trả về ở kết quả sẽ tương ứng với một hàng trong tập dữ liệu ban đầu.
Nhiều hàm tổng hợp được cung cấp trong SQL Server. Trong phần này, chúng ta sẽ xem xét các hàm phổ biến nhất như SUM, MIN, MAX, AVG và COUNT.
Khi làm việc với các hàm tổng hợp, bạn cần lưu ý những điểm sau:
- Các hàm tổng hợp trả về một giá trị duy nhất (scalar) và có thể được sử dụng trong các câu lệnh SELECT ở hầu hết mọi nơi có thể sử dụng một giá trị đơn lẻ. Ví dụ, các hàm này có thể được sử dụng trong mệnh đề SELECT, HAVING và ORDER BY. Tuy nhiên, chúng không thể được sử dụng trong mệnh đề WHERE.
- Các hàm tổng hợp bỏ qua NULL, ngoại trừ khi sử dụng COUNT (*).
- Các hàm tổng hợp trong danh sách SELECT không có tiêu đề cột trừ khi bạn cung cấp bí danh bằng AS.
- Các hàm tổng hợp trong danh sách SELECT hoạt động trên tất cả các hàng được chuyển đến hoạt động SELECT. Nếu không có mệnh đề GROUP BY, tất cả các hàng thỏa mãn bất kỳ bộ lọc nào trong mệnh đề WHERE sẽ được tính đến.
- Trừ khi bạn đang sử dụng GROUP BY, bạn không nên kết hợp các hàm aggregate với các cột không có trong các hàm trong cùng một danh sách SELECT.
Các hàm Aggregate tích hợp sẵn
Như đã đề cập, Transact-SQL cung cấp nhiều hàm tổng hợp được tích hợp sẵn. Các chức năng thường được sử dụng bao gồm:
| Tên hàm | Cú pháp | Giải thích |
| SUM | SUM(expression) | Tổng tất cả các giá trị số không NULL trong một cột. |
| AVG | AVG(expression) | Trung bình cộng tất cả các giá trị số không NULL trong một cột. (=SUM/COUNT) |
| MIN | MIN(expression) | Trả về số nhỏ nhất, ngày/giờ/thời gian sớm nhất, đoạn văn bản xuất hiện đầu tiên (theo cách sắp xếp của cột). |
| MAX | MAX(expression) | Trả về số lớn nhất, ngày/giờ/thời gian gần đây nhất, đoạn văn bản xuất hiện cuối cùng (theo cách sắp xếp của cột). |
| COUNT hoặc COUNT_BIG | COUNT(*) or COUNT(expression) | Với dấu (*), đếm tất cả các dòng, kể cả các dòng NULL. Khi một cột được chỉ định là một expression, thì sẽ đếm số dọng không NULL. Hàm COUNT trả về một số nguyên, hàm COUNT_BIG trả về một số nguyên lớn (Big_int). |
Để sử dụng các hàm Aggregate tích hợp sẵn trong mệnh đề SELECT, hãy xem ví dụ sau trong cơ sở dữ liệu mẫu MyStore:
SELECT AVG(ListPrice) AS AveragePrice,
MIN(ListPrice) AS MinimumPrice,
MAX(ListPrice) AS MaximumPrice
FROM Production.Product; | AveragePrice | MinimumPrice | MaximumPrice |
| 744.5952 | 2.2900 | 3578.2700 |
Kết quả:
Lưu ý rằng ví dụ trên tổng hợp lại tất cả các hàng từ bảng Production.Product. Chúng ta có thể dễ dàng sửa đổi truy vấn để trả về giá trung bình, tối thiểu và tối đa cho các sản phẩm trong một danh mục cụ thể bằng cách thêm mệnh đề WHERE, như sau:
SELECT AVG(ListPrice) AS AveragePrice,
MIN(ListPrice) AS MinimumPrice,
MAX(ListPrice) AS MaximumPrice
FROM Production.Product
WHERE ProductCategoryID = 15; Khi sử dụng hàm aggregate trong mệnh đề SELECT, tất cả các cột được tham chiếu trong danh sách SELECT phải được sử dụng làm đầu vào cho một hàm tổng hợp hoặc được tham chiếu trong mệnh đề GROUP BY.
Hãy xem xét truy vấn sau, truy vấn này cố gắng đưa trường ProductCategoryID vào kết quả tổng hợp:
SELECT ProductCategoryID, AVG(ListPrice) AS AveragePrice,
MIN(ListPrice) AS MinimumPrice,
MAX(ListPrice) AS MaximumPrice
FROM Production.Product; Chạy truy vấn này dẫn đến lỗi sau
Msg 8120, Level 16, State 1, line 1 Cột ‘Production.ProductCategoryID’ không hợp lệ trong list chọn vì nó không có trong hàm aggregate hoặc mệnh đề GROUP BY.
Truy vấn coi tất cả các hàng là một nhóm tổng hợp. Do đó, tất cả các cột phải được sử dụng làm đầu vào để tổng hợp các chức năng.
Trong các ví dụ trước, chúng ta đã tổng hợp dữ liệu số chẳng hạn như giá và số lượng trong ví dụ trước,. Một số hàm tổng hợp cũng có thể được sử dụng để tóm tắt dữ liệu ngày, giờ và ký tự. Các ví dụ sau cho thấy việc sử dụng các tổng hợp có ngày tháng và ký tự:
Truy vấn này trả về công ty đầu tiên và công ty cuối cùng theo tên, sử dụng MIN và MAX:
SELECT MIN(CompanyName) AS MinCustomer,
MAX(CompanyName) AS MaxCustomer
FROM SalesLT.Customer; Truy vấn này sẽ trả về giá trị đầu tiên và giá trị cuối cùng cho CompanyName trong trình tự đối chiếu của cơ sở dữ liệu, trong trường hợp này là thứ tự bảng chữ cái:
| MinCustomer | MaxCustomer |
| A Bike Store | Yellow Bicycle Company |
Các hàm khác có thể được lồng với các hàm tổng hợp.
Ví dụ: hàm scalar YEAR được sử dụng trong ví dụ sau để chỉ trả về phần năm của ngày đặt hàng, trước khi MIN và MAX được đánh giá:
SELECT MIN(YEAR(OrderDate)) AS Earliest,
MAX(YEAR(OrderDate)) AS Latest
FROM Sales.SalesOrderHeader; | Earliest | Latest |
| 2008 | 2021 |
Các hàm MIN và MAX cũng có thể được sử dụng với dữ liệu ngày tháng, để trả về các giá trị thứ tự thời gian sớm nhất và mới nhất. Tuy nhiên, AVG và SUM chỉ có thể được sử dụng cho dữ liệu số, bao gồm các kiểu dữ liệu số nguyên, tiền, thực và thập phân.
Sử dụng DISTINCT với hàm Aggregate
Việc sử dụng DISTINCT trong mệnh đề SELECT là để loại bỏ các hàng trùng lặp. Khi được sử dụng với một hàm tổng hợp, DISTINCT sẽ xóa các giá trị trùng lặp khỏi cột đầu vào trước khi tính toán giá trị tóm tắt. DISTINCT hữu ích khi tóm tắt các lần xuất hiện duy nhất của các giá trị, chẳng hạn như khách hàng trong bảng đơn đặt hàng.
Ví dụ sau trả về số lượng khách hàng đã đặt hàng, bất kể họ đã đặt bao nhiêu đơn hàng:
SELECT COUNT(DISTINCT CustomerID) AS UniqueCustomers
FROM Sales.SalesOrderHeader; COUNT (<some_column>) chỉ đếm số hàng có một số giá trị trong cột. Nếu không có giá trị NULL.
COUNT (<some_column>) sẽ giống như COUNT (*). COUNT (DISTINCT <some_column>) đếm có bao nhiêu giá trị khác nhau trong cột.
Sử dụng hàm Aggregate với NULL
Điều quan trọng là phải biết về sự hiện diện có thể có của NULL trong dữ liệu của bạn và cách NULL tương tác với các thành phần truy vấn T-SQL, bao gồm cả hàm tổng hợp. Có một số cân nhắc cần biết:
- Ngoại trừ COUNT được sử dụng với tùy chọn (*), các hàm tổng hợp T-SQL bỏ qua các NULL. Ví dụ: một hàm SUM sẽ chỉ thêm các giá trị không phải NULL. NULL không đánh giá bằng 0. COUNT (*) đếm tất cả các hàng, bất kể giá trị hay không giá trị trong bất kỳ cột nào.
- Sự hiện diện của NULL trong một cột có thể dẫn đến tính toán không chính xác cho AVG, sẽ chỉ tính tổng các hàng được điền và chia tổng đó cho số hàng không phải NULL. Có thể có sự khác biệt về kết quả giữa AVG (<cột>) và (SUM (<cột>) / COUNT (*)).
Ví dụ, hãy xem xét bảng sau có tên t1:
| C1 | C2 |
| 1 | NULL |
| 2 | 10 |
| 3 | 20 |
| 4 | 30 |
| 5 | 40 |
| 6 | 50 |
Truy vấn này minh họa sự khác biệt giữa cách AVG xử lý NULL và cách bạn có thể tính giá trị trung bình với cột được tính SUM / COUNT (*):
SELECT SUM(c2) AS sum_nonnulls,
COUNT(*) AS count_all_rows,
COUNT(c2) AS count_nonnulls,
AVG(c2) AS average,
(SUM(c2)/COUNT(*)) AS arith_average
FROM t1; Kết quả:
| sum_nonnulls | count_all_rows | count_nonnulls | average | arith_average |
| 150 | 6 | 5 | 30 | 25 |
Nếu bạn cần tính toán đến tất cả các hàng, cho dù NULL hay không, hãy xem xét việc thay thế các NULL bằng một giá trị khác sẽ không bị hàm tổng hợp của bạn bỏ qua. Bạn có thể sử dụng chức năng COALESCE cho mục đích này.
Vậy là chúng ta đã vừa đi qua nhóm hàm Aggregate với những tổng hợp chi tiết trên. Bạn có thể tìm hiểu lại các hàm tích hợp sẵn trong SQL trong bài trước.
Tham khảo khóa học về SQL của Datapot tại đây
