Trong bài viết này, chúng ta hãy xem mệnh đề GROUP BY và HAVING trong SQL.
Thiết lập môi trường
Các ví dụ trong bài viết này yêu cầu phải có các bảng sau đây.
--DROP TABLE employees PURGE;
--DROP TABLE departments PURGE;
CREATE TABLE departments (
department_id NUMBER(2) CONSTRAINT departments_pk PRIMARY KEY,
department_name VARCHAR2(14),
location VARCHAR2(13)
);
INSERT INTO departments VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO departments VALUES (20,'RESEARCH','DALLAS');
INSERT INTO departments VALUES (30,'SALES','CHICAGO');
INSERT INTO departments VALUES (40,'OPERATIONS','BOSTON');
COMMIT;
CREATE TABLE employees (
employee_id NUMBER(4) CONSTRAINT employees_pk PRIMARY KEY,
employee_name VARCHAR2(10),
job VARCHAR2(9),
manager_id NUMBER(4),
hiredate DATE,
salary NUMBER(7,2),
commission NUMBER(7,2),
department_id NUMBER(2) CONSTRAINT emp_department_id_fk REFERENCES departments(department_id)
);
INSERT INTO employees VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO employees VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO employees VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO employees VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO employees VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO employees VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO employees VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO employees VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);
INSERT INTO employees VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO employees VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO employees VALUES (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);
INSERT INTO employees VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO employees VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO employees VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
COMMIT;
Các bảng này là một biến thể của các bảng EMP và DEPT từ lược đồ SCOTT. Bạn sẽ thấy rất nhiều ví dụ của Oracle trên internet bằng cách sử dụng các bảng từ lược đồ SCOTT. Bạn có thể tìm thấy các định nghĩa bảng gốc trong tập lệnh "$ ORACLE_HOME / rdbms / admin / utlsampl.sql".
Mệnh đề GROUP BY
SQL bao gồm nhiều Hàm tổng hợp (Aggregate functions) , lấy thông tin từ nhiều hàng và tổng hợp lại để tạo ra ít hàng hơn trong tập kết quả cuối cùng. Các mã sau đây có chứa một ví dụ đơn giản rằng việc sử dụng
COUNT, AVGvà SUM tổng hợp. Chúng tôi có 14 hàng trong EMPLOYEES, nhưng truy vấn tạo ra một hàng duy nhất với số lượng các hàng trong bảng, cũng như trung bình và tổng số cột SALARY cho tất cả các hàng trong bảng.SELECT COUNT(*) AS employee_count,
AVG(e.salary) AS avg_salary,
SUM(e.salary) AS sum_salary
FROM employees e;
EMPLOYEE_COUNT AVG_SALARY SUM_SALARY
-------------- ---------- ----------
14 2073.21429 29025
1 row selected.
SQL>
Không có mệnh đề
GROUP BY, toàn bộ tập kết quả được coi là một nhóm duy nhất, do đó, các hàm tổng hợp hoạt động trên toàn bộ tập kết quả. Việc thêm GROUP BYsẽ phân chia kết quả thành các nhóm hàng, với các hàm tổng hợp được áp dụng trên cơ sở nhóm. Ví dụ sau đây nhóm các hàng theo DEPARTMENT_ID, do đó các tổng hợp trên cơ sở từng bộ phận.SELECT e.department_id,
COUNT(*) AS employee_count,
AVG(e.salary) AS avg_salary,
SUM(e.salary) AS sum_salary
FROM employees e
GROUP BY e.department_id
ORDER BY e.department_id;
DEPARTMENT_ID EMPLOYEE_COUNT AVG_SALARY SUM_SALARY
------------- -------------- ---------- ----------
10 3 2916.66667 8750
20 5 2175 10875
30 6 1566.66667 9400
3 rows selected.
SQL>
Càng nhiều cột trong
GROUP BYmệnh đề, các tổng hợp càng chi tiết. Các nhóm ví dụ sau theo các cột DEPARTMENT_ID và JOB.SELECT e.department_id,
e.job,
COUNT(*) AS employee_count,
AVG(e.salary) AS avg_salary,
SUM(e.salary) AS sum_salary
FROM employees e
GROUP BY e.department_id, e.job
ORDER BY e.department_id, e.job;
DEPARTMENT_ID JOB EMPLOYEE_COUNT AVG_SALARY SUM_SALARY
------------- --------- -------------- ---------- ----------
10 CLERK 1 1300 1300
10 MANAGER 1 2450 2450
10 PRESIDENT 1 5000 5000
20 ANALYST 2 3000 6000
20 CLERK 2 950 1900
20 MANAGER 1 2975 2975
30 CLERK 1 950 950
30 MANAGER 1 2850 2850
30 SALESMAN 4 1400 5600
9 rows selected.
SQL>
Hãy nhớ rằng, tất cả các cột không tổng hợp phải được bao gồm trong
GROUP BY.Joins
Một truy vấn được nhóm có thể tham gia vào các bảng khác để cung cấp thêm thông tin. Trong ví dụ sau, chúng tôi muốn báo cáo một số dữ liệu tổng hợp dựa trên bộ phận, nhưng chúng tôi muốn hiển thị DEPARTMENT_NAME, thay vì DEPARTMENT_ID, vì vậy chúng tôi phải join vào các bảng DEPARTMENTS .
SELECT d.department_name,
COUNT(*) AS employee_count,
AVG(e.salary) AS avg_salary,
SUM(e.salary) AS sum_salary
FROM departments d
JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name
ORDER BY d.department_name;
DEPARTMENT_NAM EMPLOYEE_COUNT AVG_SALARY SUM_SALARY
-------------- -------------- ---------- ----------
ACCOUNTING 3 2916.66667 8750
RESEARCH 5 2175 10875
SALES 6 1566.66667 9400
3 rows selected.
SQL>
Điều này hoạt động tốt, nhưng chúng ta đang mất một phần thông tin quan trọng. Bộ phận OPERATIONS không có nhân viên, nhưng chúng tôi không hiển thị nó vì chúng tôi đã sử dụng một
INNER JOIN. Chuyển sang một LEFT OUTER JOIN xuất hiện để khắc phục vấn đề đó, nhưng nó báo cáo số lượng nhân viên sai.SELECT d.department_name,
COUNT(*) AS employee_count,
AVG(e.salary) AS avg_salary,
SUM(e.salary) AS sum_salary
FROM departments d
LEFT OUTER JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name
ORDER BY d.department_name;
DEPARTMENT_NAM EMPLOYEE_COUNT AVG_SALARY SUM_SALARY
-------------- -------------- ---------- ----------
ACCOUNTING 3 2916.66667 8750
OPERATIONS 1
RESEARCH 5 2175 10875
SALES 6 1566.66667 9400
4 rows selected.
SQL>
Bây giờ chúng tôi có một hàng cho bộ phận OPERATIONS ,
COUNT(*)cuộc gọi đã trả về đúng giá trị 1, nhưng đây không phải là số lượng nhân viên chính xác. Đây không phải là lỗi của GROUP BY, nhưng cách thức COUNT(*)hoạt động gọi. Thay vào đó, nếu chúng ta tham chiếu một cột bắt buộc từ bảng join ngoài trong COUNT gọi, chúng ta sẽ nhận được kết quả chính xác, vì các giá trị NULL không được tính. Ví dụ sau đây cho thấy làm thế nào.SELECT d.department_name,
COUNT(e.employee_id) AS employee_count,
AVG(e.salary) AS avg_salary,
SUM(e.salary) AS sum_salary
FROM departments d
LEFT OUTER JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name
ORDER BY d.department_name;
DEPARTMENT_NAM EMPLOYEE_COUNT AVG_SALARY SUM_SALARY
-------------- -------------- ---------- ----------
ACCOUNTING 3 2916.66667 8750
OPERATIONS 0
RESEARCH 5 2175 10875
SALES 6 1566.66667 9400
4 rows selected.
SQL>
Mệnh đề HAVING
Chúng tôi đã đề cập trước đây, có nhiều cột được tham chiếu trong mệnh đề
GROUP BY cho chúng tôi nhiều thông tin chi tiết hơn. Ví dụ sau đây nhóm dữ liệu theo DEPARTMENT_NAME và JOB.SELECT d.department_name, e.job,
COUNT(e.employee_id) AS employee_count,
AVG(e.salary) AS avg_salary,
SUM(e.salary) AS sum_salary
FROM departments d
LEFT OUTER JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name, e.job
ORDER BY d.department_name, e.job;
DEPARTMENT_NAM JOB EMPLOYEE_COUNT AVG_SALARY SUM_SALARY
-------------- --------- -------------- ---------- ----------
ACCOUNTING CLERK 1 1300 1300
ACCOUNTING MANAGER 1 2450 2450
ACCOUNTING PRESIDENT 1 5000 5000
OPERATIONS 0
RESEARCH ANALYST 2 3000 6000
RESEARCH CLERK 2 950 1900
RESEARCH MANAGER 1 2975 2975
SALES CLERK 1 950 950
SALES MANAGER 1 2850 2850
SALES SALESMAN 4 1400 5600
10 rows selected.
SQL>
Đôi khi chúng tôi chỉ quan tâm đến thông tin từ các nhóm dữ liệu cụ thể.
HAVING cho phép chúng tôi lọc ra các nhóm không đáp ứng yêu cầu cụ thể. Bạn có thể nghĩ về nó như một mệnh đề WHERE cho mệnh đề GROUP BY. Trong ví dụ sau, chúng tôi chỉ trả lại thông tin cho các nhóm có nhiều hơn 1 nhân viên.SELECT d.department_name, e.job,
COUNT(e.employee_id) AS employee_count,
AVG(e.salary) AS avg_salary,
SUM(e.salary) AS sum_salary
FROM departments d
LEFT OUTER JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name, e.job
HAVING COUNT(e.employee_id) > 1
ORDER BY d.department_name, e.job;
DEPARTMENT_NAM JOB EMPLOYEE_COUNT AVG_SALARY SUM_SALARY
-------------- --------- -------------- ---------- ----------
RESEARCH ANALYST 2 3000 6000
RESEARCH CLERK 2 950 1900
SALES SALESMAN 4 1400 5600
3 rows selected.
SQL>
Chỉ là một sự ngớ ngẩn sang một bên, có thể có một mệnh đề
HAVING mà không có một mệnh đề GROUP BY, như trong ví dụ sau đây. Toàn bộ tập kết quả được coi là một nhóm duy nhất, vì vậy bộ lọc mệnh đề HAVING vẫn có thể được áp dụng.SELECT COUNT(e.employee_id) AS employee_count,
AVG(e.salary) AS avg_salary,
SUM(e.salary) AS sum_salary
FROM departments d
LEFT OUTER JOIN employees e ON d.department_id = e.department_id
HAVING COUNT(e.employee_id) > 2
ORDER BY d.department_name, e.job;
EMPLOYEE_COUNT AVG_SALARY SUM_SALARY
-------------- ---------- ----------
14 2073.21429 29025
1 row selected.
SQL>=============================
* KHOÁ HỌC ORACLE DATABASE A-Z ENTERPRISE trực tiếp từ tôi giúp bạn bước đầu trở thành những chuyên gia DBA, đủ kinh nghiệm đi thi chứng chỉ OA/OCP, đặc biệt là rất nhiều kinh nghiệm, bí kíp thực chiến trên các hệ thống Core tại VN chỉ sau 1 khoá học.
* CÁCH ĐĂNG KÝ: Gõ (.) hoặc để lại số điện thoại hoặc inbox https://m.me/tranvanbinh.vn hoặc Hotline/Zalo 090.29.12.888
* Chi tiết tham khảo:
https://bit.ly/oaz_w
=============================
KẾT NỐI VỚI CHUYÊN GIA TRẦN VĂN BÌNH:
📧 Mail: binhoracle@gmail.com
☎️ Mobile/Zalo: 0902912888
👨 Facebook: https://www.facebook.com/BinhOracleMaster
👨 Inbox Messenger: https://m.me/101036604657441 (profile)
👨 Fanpage: https://www.facebook.com/tranvanbinh.vn
👨 Inbox Fanpage: https://m.me/tranvanbinh.vn
👨👩 Group FB: https://www.facebook.com/groups/DBAVietNam
👨 Website: https://www.tranvanbinh.vn
👨 Blogger: https://tranvanbinhmaster.blogspot.com
🎬 Youtube: https://www.youtube.com/@binhguru
👨 Tiktok: https://www.tiktok.com/@binhguru
👨 Linkin: https://www.linkedin.com/in/binhoracle
👨 Twitter: https://twitter.com/binhguru
👨 Podcast: https://www.podbean.com/pu/pbblog-eskre-5f82d6
👨 Địa chỉ: Tòa nhà Sun Square - 21 Lê Đức Thọ - Phường Mỹ Đình 1 - Quận Nam Từ Liêm - TP.Hà Nội
=============================SQL Tutorial - Bài 7: GROUP BY và HAVING, oracle tutorial, học oracle database, Tự học Oracle, Tài liệu Oracle 12c tiếng Việt, Hướng dẫn sử dụng Oracle Database, Oracle SQL cơ bản, Oracle SQL là gì, Khóa học Oracle Hà Nội, Học chứng chỉ Oracle ở đầu, Khóa học Oracle online,sql tutorial, khóa học pl/sql tutorial, học dba, học dba ở việt nam, khóa học dba, khóa học dba sql, tài liệu học dba oracle, Khóa học Oracle online, học oracle sql, học oracle ở đâu tphcm, học oracle bắt đầu từ đâu, học oracle ở hà nội, oracle database tutorial, oracle database 12c, oracle database là gì, oracle database 11g, oracle download, oracle database 19c, oracle dba tutorial, oracle tunning, sql tunning , oracle 12c, oracle multitenant, Container Databases (CDB), Pluggable Databases (PDB), oracle cloud, oracle security, oracle fga, audit_trail,oracle RAC, ASM, oracle dataguard, oracle goldengate, mview, oracle exadata, oracle oca, oracle ocp, oracle ocm , oracle weblogic, postgresql tutorial, mysql tutorial, mariadb tutorial, sql server tutorial, nosql, mongodb tutorial, oci, cloud, middleware tutorial, hoc solaris tutorial, hoc linux tutorial, hoc aix tutorial, unix tutorial, securecrt, xshell, mobaxterm, putty
