Sử dụng hàm tổng hợp
T-SQL cung cấp các hàm tổng hợp như SUM, MAX và AVG để thực hiện các phép tính lấy nhiều giá trị và trả về một kết quả duy nhất.
Làm việc với các hàm tổng hợp
Hầu hết các truy vấn mà chúng tôi đã xem xét hoạt động trên một hàng tại một thời điểm, bằng cách sử dụng mệnh đề WHERE để lọc hàng. Mỗi hàng trả về 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 mục 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 xem xét các điểm sau đây:
- Hàm tổng hợp trả về một giá trị đơn (vô hướng) và có thể được dùng trong câu lệnh SELECT hầu như bất cứ nơi nào có thể dùng một giá trị duy nhất. Ví dụ: các hàm này có thể được sử dụng trong các mệnh đề SELECT, HAVING và ORDER BY. Tuy nhiên, không thể sử dụng chúng trong mệnh đề WHERE.
- Hàm tổng hợp bỏ qua NULLs, ngoại trừ khi sử dụng COUNT(*).
- 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 biệt danh bằng as.
- 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 sang thao tác 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óm tắt. Bạn sẽ tìm hiểu thêm về GROUP BY trong chủ đề tiếp theo.
- Trừ khi bạn đang sử dụng GROUP BY, bạn không nên kết hợp hàm tổng hợp với các cột không được bao gồm trong các hàm trong cùng một danh sách SELECT.
Để mở rộng ra ngoài các hàm tích hợp sẵn, SQL Server cung cấp cơ chế cho các hàm tổng hợp do người dùng xác định thông qua .NET Common Language Runtime (CLR). Chủ đề đó nằm ngoài phạm vi của mô-đun này.
Các hàm tổng hợp tích hợp sẵn
Như đã đề cập, Transact-SQL cung cấp nhiều hàm tổng hợp tích hợp sẵn. Các hàm thường được sử dụng bao gồm:
Tên Hàm
Cú pháp
Mô tả
TỔNG
SUM(biểu thức)
Tổng tất cả các giá trị số không phải NULL trong một cột.
AVG
AVG(biểu thức)
Tính trung bình tất cả các giá trị số không phải NULL trong một cột (tổng/đếm).
PHÚT
MIN(biểu thức)
Trả về số nhỏ nhất, ngày/giờ sớm nhất hoặc chuỗi xảy ra lần đầu tiên (theo quy tắc sắp xếp đối chiếu).
MAX
MAX(biểu thức)
Trả về số lớn nhất, ngày/giờ gần nhất hoặc chuỗi xảy ra lần cuối (theo quy tắc sắp xếp đối chiếu).
COUNT hoặc COUNT_BIG
COUNT(*) hoặc COUNT(biểu thức)
Với (*), đếm tất cả các hàng, bao gồm các hàng có giá trị NULL. Khi một cột được xác định là biểu thức, trả về số hàng không phải là NULL cho cột đó. COUNT trả về một số chấm; COUNT_BIG trả về giá big_int.
Để sử dụng tổng hợp tích hợp trong mệnh đề SELECT, hãy cân nhắc ví dụ sau đây 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;
Kết quả của truy vấn này trông giống như thế này:
Giá Trung bình
Giá Tối thiểu
Giá Tối đa
744.5952
2.2900
3578.2700
Lưu ý rằng ví dụ ở trên tóm tắt tất cả các hàng từ bảng Production.Product . Chúng tôi 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 giá trị tổng hợp 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 dữ liệu đầu vào cho hàm tổng hợp hoặc được tham chiếu trong mệnh đề GROUP BY.
Hãy cân nhắc truy vấn sau đây, những truy vấn này sẽ tìm cách đư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, Mức 16, Tiểu bang 1, Dòng 1
Cột 'Production.ProductCategoryID' không hợp lệ trong danh sách chọn vì nó không nằm trong hàm tổng hợp 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 duy nhất. 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 hàm.
Trong các ví dụ trước, chúng tôi đã tổng hợp dữ liệu số như giá cả và số lượng trong ví dụ trước. Một số hàm tổng hợp cũng có thể được dùng để tóm tắt dữ liệu ngày, giờ và ký tự. Các ví dụ sau đây cho thấy việc sử dụng tổng hợp với ngày và ký tự:
Truy vấn này trả về công ty đầu tiên và cuối cùng theo tên, dùng hàm MIN và MAX:
SELECT MIN(CompanyName) AS MinCustomer,
MAX(CompanyName) AS MaxCustomer
FROM SalesLT.Customer;
Truy vấn này sẽ trả về các giá trị đầu tiên và cuối cùng cho CompanyName trong chuỗi đối chiếu của cơ sở dữ liệu, mà trong trường hợp này là thứ tự bảng chữ cái:
MinCustomer
MaxCustomer
Cửa hàng xe đạp
Công ty Yellow Bicycle
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 vô hướng YEAR được dùng trong ví dụ sau đây để chỉ trả về phần năm của ngày đặt hàng, trước khi đánh giá min và MAX:
SELECT MIN(YEAR(OrderDate)) AS Earliest,
MAX(YEAR(OrderDate)) AS Latest
FROM Sales.SalesOrderHeader;
Sớm nhất
Đặt
2008
2021
Các hàm MIN và MAX cũng có thể được sử dụng với dữ liệu ngày để trả về các giá trị sớm nhất và mới nhất theo thứ tự thời gian. Tuy nhiên, chỉ có thể sử dụng AVG và SUM cho dữ liệu số, bao gồm số nguyên, tiền, phao và kiểu dữ liệu thập phân.
Sử dụng DISTINCT với các hàm tổng hợp
Bạn nên biết về việc sử dụng DISTINCT trong mệnh đề SELECT để loại bỏ các hàng trùng lặp. Khi được sử dụng với hàm tổng hợp, DISTINCT loại bỏ các giá trị trùng lặp khỏi cột đầu vào trước khi tính giá trị tóm tắt. DISTINCT rất hữu ích khi tóm tắt các lần xuất hiện giá trị duy nhất, chẳng hạn như khách hàng trong bảng đơn hàng.
Ví dụ sau đây 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(*). Hàm COUNT (DISTINCT <some_column>) đếm số lượng giá trị khác nhau có trong cột.
Sử dụng hàm tổng hợp với NULL
Điều quan trọng là cần lưu ý sự hiện diện có thể có của NULLs trong dữ liệu của bạn và về cách NULL tương tác với các cấu phần truy vấn T-SQL, bao gồm hàm tổng hợp. Có một vài cân nhắc cần lưu ý:
- Ngoại trừ count được sử dụng với tùy chọn (*), hàm tổng hợp T-SQL sẽ bỏ qua NULLs. Ví dụ: hàm SUM sẽ chỉ cộng các giá trị không phải NULL. NULLs không đánh giá bằng không. COUNT(*) đếm tất cả các hàng, bất kể giá trị hay giá trị không phải là giá trị trong bất kỳ cột nào.
- Sự hiện diện của NULLs trong một cột có thể dẫn đến tính toán không chính xác cho AVG, điều này 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(<column>) và (SUM(<column>)/COUNT(*)).
Ví dụ: xem xét bảng sau đây có tên là t1:
C1
C2
1
KHÔNG
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ả sẽ là:
sum_nonnulls
count_all_rows
count_nonnulls
trung bình
arith_average
150
6
5
30
25
Trong resultset này, cột có tên trung bình là tổng hợp nội bộ nhận được tổng 150 và chia cho số lượng giá trị không phải null trong cột c2. Phép tính sẽ là 150/5 hoặc 30. Cột được gọi là arith_average chia rõ ràng tổng cho số hàng, vì vậy phép tính là 150/6 hoặc 25.
Nếu bạn cần tóm tắt tất cả các hàng, dù là NULL hay không, hãy cân nhắc việc thay thế NULLs bằng một giá trị khác mà hàm tổng hợp của bạn sẽ không bỏ qua. Bạn có thể sử dụng hàm COALESCE cho mục đích này.