Truy vấn dữ liệu – Các phương thức tổng hợp

Các phương thức tổng hợp hỗ trợ chúng ta thực hiện các phép tính trên tập hợp.

Aggregate Functions

Trong một số trường hợp, chúng ta không cần liệt kê dữ liệu. Thay vào đó, chúng ta cần thực hiện các thao tác khác như đếm, tìm giá trị min, max hoặc tính giá trị trung bình… Dưới đây là một số hàm tiện ích, giúp chúng ta có thể tính toán trên dữ liệu của mình. Các hàm trong bài hôm nay sẽ có chung một đặc điểm là chúng trả về một giá trị, thay vì một danh sách dữ liệu như trước đây chúng ta thường thấy.

COUNT / COUNT_BIG

T-SQL hỗ trợ cho chúng ta 2 phương thức: COUNT và COUNT_BIG để đếm một giá trị nào đó. Cả 2 phương thức này có công dụng và cú pháp tương tự nhau. Khác biệt duy nhất là COUNT trả về kết quả kiểu INT, trong khi COUNT_BIG trả về kết quả kiểu BIGINT.

Trong thực tiễn, có rất nhiều trường hợp chúng ta cần sử dụng phương thức COUNT. Một ví dụ nhỏ trong CSDL Chinook của chúng ta. Mình muốn thống kê số lượng bài hát trong bảng Track.

Với câu truy vấn trên, mình sẽ nhận được số dòng trong bảng Track. Tuy nhiên, bây giờ mình muốn đếm số lượng tên bài hát. Bỏ qua những bài hát trùng. Lúc này câu truy vấn của mình được sửa lại thành:

Các bạn thử thực thi 2 câu truy vấn trên và kiểm tra kết quả của chúng có giống nhau không.

Khi sử dụng DISTINCT, các bạn lưu ý phải chỉ định thuộc tính một cách tường minh. DISTINCT sẽ không thể làm việc với ký tự * được.

AVG

Là viết tắt của average – trung bình cộng. Cú pháp của phương thức AVG cũng khá đơn giản và tương tự như phương thức COUNT:

Ví dụ: Chúng ta có thể tính được giá trị trung bình của các hóa đơn bằng câu truy vấn

SUM

Phương thức SUM có công dụng để tính tổng giá trị của một thuộc tính. Cú pháp:

Ví dụ: Chúng ta hãy tính tổng tiền mà cửa hàng đã thu về, bằng cách tính tổng giá trị các hóa đơn nào.

Quá đơn giản đúng không các bạn.

MIN, MAX

Hai phương thức này cũng rất đơn giản. Phương thức MIN trả về giá trị nhỏ nhất. Ngược lại, MAX sẽ trả về giá trị lớn nhất của một thuộc tính.

Các bạn hãy thử nghĩ cho mình một vài ví dụ và thực hành với 2 phương thức này.

STDEV

Phương thức này sẽ giúp bạn tính toán độ lệch chuẩn (standard deviation) của tập dữ liệu. Cú pháp của phương thức cũng tương tự với các phương thức phía trên

Mệnh đề GROUP BY

Đọc tới đây thì có lẽ các bạn sẽ cảm thấy các Aggregate Functions khá tẻ nhạt. Không có gì đặc biệt cả đúng không nào? Chính xác là vậy, cho đến khi chúng ta kết hợp chúng với một mệnh đề mới. Mệnh đề GROUP BY.

Mệnh đề GROUP BY cho phép chúng ta gom nhóm các dòng dữ liệu trong kết quả của mệnh đề WHERE. Sau khi gom nhóm, chúng ta có thể kết hợp với các Aggregate Functions. Lúc này, chúng ta có thể khai thác khá nhiều thông tin từ dữ liệu của chúng ta.

Cú pháp

Để cho dễ hiểu hơn, mình sẽ thực hiện một ví dụ sau đây. Phía trên, chúng ta đã sử dụng phương thức SUM để tính tổng giá trị các hóa đơn rồi. Vậy, bây giờ mình muốn tính tổng giá trị các hóa đơn, nhưng theo từng khu vực. Chi tiết hơn là tổng các hóa đơn của khách hàng đến từ USA,  tổng từ các khách hàng đến từ Canada…vv

Ví dụ

Trước hết, hãy nghĩ một phương án khác không sử dụng mệnh đề GROUP BY thử. Mình sẽ sử dụng SUM kèm theo mệnh đề WHERE để tính được tổng của một khu vực.

Đây là cách duy nhất mình nghĩ ra. Nó có thể giúp được bài toán của chúng ta. Nhưng có nhiều phát sinh. Chúng ta dễ dàng nhận thấy mỗi khu vực  sẽ là một câu truy vấn. Do đó:

  • Nếu nhiều quốc gia, khu vực sẽ rất tốn công sức
  • Và còn khả năng bị lặp, thiếu nữa.

Rõ ràng cách này không phù hợp cho chúng ta. Hãy thử sử dụng mệnh đề GROUP BY. Lúc này, chúng ta có câu truy vấn

Và đây là kết quả của chúng ta.

Ví dụ bạn quản lý dữ liệu 1 chuỗi của hàng của một thương hiệu nào đó. Việc so sánh hiệu năng kinh doanh giữa các của hàng sẽ trở nên dễ dàng hơn bao giờ hết với mệnh đề GROUP BY đúng không.

Phân tích kỹ về mệnh đề GROUP BY

Tuy nhiên, mình biết lúc này các bạn cũng rất phân vân về cách dùng của mệnh đề GROUP BY. Không phải vì cấu trúc nó quá phức tạp, mà cách nó làm việc hơi khó hiểu một chút. Và để hiểu rõ được về nó. Chúng ta cần xem lại thứ tự thực hiện các mệnh trề trong câu truy vấn.

Trong câu truy vấn phía trên, thứ tự thực hiện các mệnh đề sẽ là

  1. FROM
  2. GROUP BY
  3. SELECT

Ban đầu bộ máy tìm kiếm sẽ đến bảng dữ liệu được chỉ định là Invoice (FROM). Sau đó, nó bắt đầu phân nhóm dữ liệu theo yêu cầu của mệnh đề GROUP BY. Các bạn có thể hình dung, bộ máy tìm kiếm sẽ chia bảng Invoice thành các phần nhỏ theo thuộc tính BillingCountry. Tiếp đến là thực thi mệnh đề SELECT. Và cuối cùng là kết hợp các kết quả từ các phần dữ liệu lại và trả về cho chúng ta.

Theo đó, cũng khá giống cách chúng ta làm ban đầu. Khi không sử dụng mệnh đề GROUP BY. Nhưng với mệnh đề GROUP BY, hệ thống sẽ tự động xử lý hết cho chúng ta.

Gom nhóm dựa trên nhiều thuộc tính

Ngoài ra, nếu bạn nhìn kỹ vào cú pháp của mệnh đề GROUP BY, bạn có thể thấy. Chúng ta có thể gom nhóm thêm nhiều hơn 1 tiêu chí. Ví dụ: chúng ta tiếp tục nâng cấp câu truy vấn phía trên, không chỉ dừng lại phân hóa đơn theo quốc gia, mà còn gom theo thành phố nữa. Lúc này câu truy vấn của chúng ta sẽ là:

Và dưới đây là một phần kết quả thu được. Các bạn hãy thực thi câu truy vấn này để có thể nhìn thấy được toàn bộ kết quả trả về. Nhờ đó, chúng ta sẽ hiểu rõ hơn.

Bây giờ có một câu hỏi cho các bạn. Nếu các bạn không sử dụng 2 tiêu chí trong mệnh đề GROUP BY là: BillingCountryBillingCity. Thay vào đó, các bạn chỉ sử dụng BillingCity mà thôi. thì trong mệnh đề SELECT, các bạn có thể truy vấn thuộc tính BillingCountry hay không? Hay một cách xúc tích nhất. Câu truy vấn dưới đây có hợp lệ không?

Nếu có lỗi khi thực thi. Hãy thử giải thích tại sao.

HAVING

Sau mệnh đề GROUP BY để gom nhóm và tính toán dữ liệu, chúng ta được hỗ trợ thêm mệnh đề HAVING. Với mệnh đề HAVING, chúng ta có thể dễ dàng tìm kiếm (lọc) các nhóm trong kết quả trả về.

Cú pháp của mệnh đề HAVING rất đơn giản

Tiếp tục ví dụ phía trên, nhưng lần này, mình chỉ mong muốn truy vấn những cửa hàng có tổng doanh số trên 70 mà thôi. Lúc này, mệnh đề HAVING sẽ tìm kiếm và trả về kết quả mong muốn cho mình. Câu truy vấn tiếp tục được cập nhật thành:

Và đây là kết quả:

Với các Aggregate Functions kết hợp cùng với mệnh đề GROUP BY, HAVING. Chúng ta có thể dễ dàng phân tích, thống kê và đánh giá dữ liệu của chúng ta rồi. Các bạn hãy suy nghĩ và thử thêm một vài câu truy vấn nữa.