Thứ Tư, 5 tháng 8, 2020

SQL cơ bản - Bài 7: GROUP BY và HAVING

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
  • GROUP BY
  • Joins
  • HAVING

Thiết lập môi trường

Bạn có thể thực hiện tất cả các truy vấn trực tuyến miễn phí bằng SQL Fiddle .
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 COUNTAVGvà 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 JOINChuyể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 BYTrong 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>
@ Trần Văn Bình - Founder of "Oracle DBA Việt Nam" #OracleTutorial #OracleDBA #OracleDatabaseAdministration #học oracle database #oca #ocp #oce #ocm

ĐỌC NHIỀU

Trần Văn Bình - Oracle Database Master