Sử dụng các chức năng Nhóm

Báo cáo dữ liệu Tổng hợp bằng cách sử dụng các chức năng Nhóm

SQL có nhiều hàm tổng hợp được xác định trước có thể được sử dụng để viết các truy vấn nhằm tạo ra chính xác loại thông tin này. Mệnh đề GROUP BY chỉ định cách nhóm các hàng từ bảng dữ liệu khi tổng hợp thông tin, trong khi mệnh đề HAVING lọc ra các hàng không thuộc về các nhóm được chỉ định.

Các hàm tổng hợp thực hiện nhiều hành động khác nhau như đếm tất cả các hàng trong bảng, tính trung bình dữ liệu của cột và tính tổng dữ liệu số. Các tập hợp cũng có thể tìm kiếm một bảng để tìm các giá trị "MAX" cao nhất hoặc "MIN" thấp nhất trong một cột. Cũng như các loại truy vấn khác, bạn có thể hạn chế hoặc lọc ra các hàng mà các hàm này hoạt động với mệnh đề WHERE. Ví dụ: nếu người quản lý cần biết có bao nhiêu nhân viên làm việc trong một tổ chức, thì hàm tổng hợp có tên COUNT (*) có thể được sử dụng để tạo ra thông tin này. Hàm COUNT (*) được hiển thị trong câu lệnh SELECT bên dưới đếm tất cả các hàng trong một bàn.

SELECT COUNT(*)
FROM employees;

  COUNT(*)
----------
        24

Bảng kết quả cho hàm COUNT (*) là một cột đơn từ một hàng được gọi là giá trị hoặc kết quả vô hướng. Lưu ý rằng bảng kết quả có tiêu đề cột tương ứng với tên của hàm tổng hợp được chỉ định trong mệnh đề SELECT.

Một số hàm tổng hợp thường được sử dụng như sau:

SUM( [ALL | DISTINCT] expression )

AVG( [ALL | DISTINCT] expression )

COUNT( [ALL | DISTINCT] expression )

COUNT(*)

MAX(expression)

MIN(expression)

Từ khóa ALL và DISTINCT là tùy chọn và hoạt động như chúng với các mệnh đề SELECT mà bạn đã học cách viết. Từ khóa ALL là mặc định khi tùy chọn được phép. Biểu thức được liệt kê trong cú pháp có thể là một hằng số, một hàm, hoặc bất kỳ sự kết hợp nào của tên cột, hằng số và hàm được kết nối bằng các toán tử số học.Tuy nhiên, các hàm tổng hợp thường được sử dụng nhất với tên cột. Ngoại trừ hàm COUNT, tất cả các hàm tổng hợp không xem xét giá trị NULL.

Có hai quy tắc mà bạn phải hiểu và tuân theo khi sử dụng tổng hợp:

Các hàm tổng hợp có thể được sử dụng trong cả mệnh đề SELECT và HAVING (mệnh đề HAVING được trình bày ở phần sau của chương này).
Không thể sử dụng các hàm tổng hợp trong mệnh đề WHERE. Vi phạm của nó sẽ tạo ra Oracle ORA-00934 nhóm chức năng không được phép ở đây thông báo lỗi.

Hình minh họa

Truy vấn SELECT bên dưới đếm số lượng nhân viên trong tổ chức.

SELECT COUNT(*) Count
FROM employees;

COUNT
-----
   24

Truy vấn SELECT dưới đây trả về mức trung bình của tiền lương của nhân viên trong tổ chức.

SELECT AVG(Salary) average_sal
FROM employees;

AVERAGE_SAL
-----------
      15694

Truy vấn SELECT bên dưới trả về tổng tiền lương của nhân viên trong tổ chức.

SELECT SUM(Salary) total_sal
FROM employees;

TOTAL_SAL
---------
    87472

Truy vấn SELECT bên dưới trả về ngày tuyển dụng cũ nhất và mới nhất của nhân viên trong tổ chức.

SELECT MIN (hire_date) oldest, MAX (hire_date) latest
FROM employees;

OLDEST		LATEST
---------	-----------
16-JAN-83	01-JUL-2012

NHÓM THEO

Hàm tổng hợp thường được sử dụng cùng với mệnh đề GROUP BY. Mệnh đề GROUP BY cho phép bạn sử dụng các hàm tổng hợp để trả lời các câu hỏi quản lý phức tạp hơn như:

Mức lương trung bình của nhân viên trong từng bộ phận là bao nhiêu?

Có bao nhiêu nhân viên làm việc trong mỗi bộ phận?

Có bao nhiêu nhân viên đang làm việc trong một dự án cụ thể?

Nhóm theo chức năng thiết lập các nhóm dữ liệu dựa trên các cột và chỉ tổng hợp thông tin trong một nhóm. Tiêu chí phân nhóm được xác định bởi các cột được chỉ định trong mệnh đề GROUP BY. Theo hệ thống phân cấp này, dữ liệu đầu tiên được tổ chức trong các nhóm và sau đó mệnh đề WHERE hạn chế các hàng trong mỗi nhóm.

Hướng dẫn sử dụng điều khoản GROUP BY

(1) Tất cả các cột hoặc cột phụ thuộc được sử dụng trong hàm GROUP BY phải tạo cơ sở cho việc phân nhóm, do đó cũng phải được bao gồm trong mệnh đề GROUP BY.

SELECT	DEPARTMENT_ID, SUM(SALARY)
FROM employees;

DEPARTMENT_ID,
*
ERROR at line 2:
ORA-00937: not a single-group group function

(2) Mệnh đề GROUP BY không hỗ trợ việc sử dụng bí danh cột mà là tên thực.

(3) Mệnh đề GROUP BY chỉ có thể được sử dụng với các hàm tổng hợp như SUM, AVG, COUNT, MAX và MIN. Nếu nó được sử dụng với các hàm một hàng, Oracle ném một ngoại lệ là "ORA-00979: không phải là biểu thức GROUP BY" .

(4) Không thể sử dụng các hàm tổng hợp trong mệnh đề GROUP BY. Oracle sẽ trả về thông báo lỗi "ORA-00934: nhóm chức năng không được phép" tại đây.

Truy vấn dưới đây liệt kê số lượng nhân viên làm việc trong từng bộ phận.

SELECT  DEPARTMENT_ID,  COUNT (*)
FROM employees
GROUP BY DEPARTMENT_ID;

Tương tự, truy vấn dưới đây để tìm tổng số tiền lương cho các id công việc tương ứng trong mỗi bộ phận. Lưu ý rằng nhóm được thành lập dựa trên Bộ phận và Id công việc. Vì vậy, chúng xuất hiện trong mệnh đề GROUP BY.

SELECT DEPARTMENT_ID, JOB_ID, SUM (SAL)
FROM employees
GROUP BY DEPARTMENT_ID, JOB_ID;

Truy vấn dưới đây cũng cho kết quả tương tự. Xin lưu ý rằng việc nhóm dựa trên các cột id bộ phận và id công việc nhưng không được sử dụng cho mục đích hiển thị.

SELECT SUM (SALARY)
FROM employees
GROUP BY DEPARTMENT_ID, JOB_ID;

Sử dụng DISTINCT, TẤT CẢ các từ khóa có hàm Tổng hợp

Bằng cách chỉ định từ khóa DISTINCT với tham số đầu vào, nhóm theo hàm chỉ xem xét giá trị duy nhất của cột để tổng hợp. Bằng cách chỉ định từ khóa ALL với tham số đầu vào, nhóm theo hàm sẽ xem xét tất cả các giá trị của cột để tổng hợp, bao gồm cả giá trị rỗng và trùng lặp. TẤT CẢ là thông số kỹ thuật mặc định.

Mệnh đề HAVING

Mệnh đề HAVING được sử dụng cho các hàm tổng hợp giống như cách mệnh đề WHERE được sử dụng cho tên cột và biểu thức. Về cơ bản, các mệnh đề HAVING và WHERE thực hiện điều tương tự, đó là lọc các hàng để đưa vào bảng kết quả dựa trên một điều kiện . Mặc dù có vẻ như mệnh đề HAVING lọc ra các nhóm, nhưng điều đó lại không, thay vào đó, mệnh đề HAVING lọc các hàng.

Khi tất cả các hàng của một nhóm đều bị loại bỏ. Tóm lại, sự khác biệt quan trọng giữa mệnh đề WHERE và HAVING là:

Mệnh đề WHERE được sử dụng để lọc các hàng TRƯỚC hành động NHÓM (tức là trước khi tính toán các hàm tổng hợp).

Mệnh đề HAVING lọc các hàng SAU hành động NHÓM (tức là sau khi tính toán các hàm tổng hợp).

SELECT JOB_ID,	SUM (SALARY)
FROM employees
GROUP BY JOB_ID
HAVING SUM (SALARY) > 10000;

Mệnh đề HAVING là một tùy chọn điều kiện liên quan trực tiếp đến tùy chọn mệnh đề GROUP BY vì mệnh đề HAVING loại bỏ các hàng khỏi bảng kết quả dựa trên kết quả của mệnh đề GROUP BY.

SELECT department_id, AVG(Salary)
FROM employees
HAVING AVG(Salary) > 33000;
ERROR at line 1:  ORA-00937: not a single-group group function