Bài 4. Các hàm thống kê số liệu (Group functions)
Bài này chúng ta đề cập đến các hàm thống kê dữ liệu:
Danh sách các hàm.
Sử dụng các hàm.
Thêm các mệnh đề với các hàm.
Loại trừ (giới hạn) dữ liệu.
Các hàm thống kê dữ liệu là một phần quan trọng với công việc của DBA.
Danh sách các hàm gồm
Các hàm thống kê dữ liệu có khả năng tác động trên nhiều bản ghi cùng một lúc. Chúng thường được sử dụng để tính các giá trị nhỏ nhất, giá lớn nhất, giá trị trung bình, đếm số lượng, độ lệch chuẩn.
Sau đây là danh sách các hàm thống kê.
min(x)
Tìm giá trị nhỏ nhất trong một giá trị của trường x trả về từ câu lệnh select.
max(x)
Tìm giá trị lớn nhất trong một giá trị của trường x trả về từ câu lệnh select.
avg(x)
Tìm giá trị lớn nhất trong trường x trả về từ câu lệnh select.
count(x)
Đếm số lượng bản ghi trả về từ câu lệnh select.
sum(x)
Tính tổng các giá trị trong trường x trả về từ câu lệnh select.
stddev(x)
The standard deviation for all values in column x in the group returned by the select statement.
variance(x)
The variance for all values in column x in the group.
Sử dụng các hàm thống kê dữ liệu (Using group functions)
Ví dụ với dữ liệu nhân sự của công ty 'Fred & Barney Hosting Co.'. Bao gồm 4 phòng với 8 nhân viên. Sau đây chúng ta sẽ tạo bảng và nhập dữ liệu.
Mã:
create table employees (id varchar2(10),
name varchar2(30),
age varchar2(3),
sex varchar2(1),
comm_date date,
salary number(10,2));
Sử dụng SQL*Plus ta có như sau:
Mã:
SQL> create table employees (id varchar2(10),
2 name varchar2(30),
3 age varchar2(3),
4 sex varchar2(1),
5 comm_date date,
6 salary number(10,2));
Table created.
Kiểm tra lại ta có:
Mã:
SQL>
SQL> desc employees;
Name Null? Type
------------------------------- -------- ----
ID VARCHAR2(10)
NAME VARCHAR2(30)
AGE VARCHAR2(3)
SEX VARCHAR2(1)
COMM_DATE DATE
SALARY NUMBER(10,2)
Nhập dữ liệu:
Mã:
insert into employees
(id, name, age, sex, comm_date,
salary)
values
(1, 'Smith', 45, 'F', to_date('01-FEB-1997','DD-MON-YYYY'),
70000);
insert into employees
(id, name, age, sex, comm_date,
salary)
values
(2, 'Slate', 52, 'M', to_date('01-FEB-1997','DD-MON-YYYY'),
80000);
insert into employees
(id, name, age, sex, comm_date,
salary)
values
(3, 'Rubble', 22, 'M', to_date('10-FEB-1998','DD-MON-YYYY'),
22000);
insert into employees
(id, name, age, sex, comm_date,
salary)
values
(4, 'Flintstone', 18, 'M', to_date('01-FEB-1998','DD-MON-YYYY'),
70000);
insert into employees
(id, name, age, sex, comm_date,
salary)
values
(5, 'OOI', 21, 'F', to_date('01-FEB-1999','DD-MON-YYYY'),
99000);
insert into employees
(id, name, age, sex, comm_date,
salary)
values
(6, 'Vajhaal', 21, 'M', to_date('01-MAR-1999','DD-MON-YYYY'),
2000);
insert into employees
(id, name, age, sex, comm_date,
salary)
values
(7, 'Mostert', 25, 'M', to_date('20-AUG-1998','DD-MON-YYYY'),
50000);
insert into employees
(id, name, age, sex, comm_date,
salary)
values
(8, 'Jeffcoat', 45, 'M', to_date('01-FEB-1999','DD-MON-YYYY'),
35000);
Như vậy ta đã có đủ dữ liệu cần thiết.
Bây giờ chúng ta sẽ thực hiện sử dụng các hàm
Đầu tiên là hàm count(). Có 2 cách dùng như sau:
Mã:
select count(*), count(1) from employees;
Câu lệnh trên sẽ có kết quả như sau:
Mã:
SQL> select count(*), count(1) from employees;
COUNT(*) COUNT(1)
--------- ---------
8 8
Chúng ta nhận được cùng một kết quả. Có sự khác biệt duy nhất là count(1) thực hiện nhanh hơn.
Để xem ai là người có lương thấp nhất, cao nhất, lương trung bình là bao nhiêu ta dùng lệnh sau:
Mã:
select min(salary), max(salary), avg(salary)
from employees;
Chúng ta sẽ nhận được kết quả như sau:
Mã:
SQL> select min(salary), max(salary), avg(salary)
2 from employees;
MIN(SALARY) MAX(SALARY) AVG(SALARY)
----------- ----------- -----------
2000 99000 53500
Tổng số lương phải trả cho các nhân viên là bao nhiêu? Chúng ta sẽ sử dụng hàm tính tổng (sum) như sau:
select sum(salary) from employees;
Và kết quả thu được sẽ là:
Mã:
SQL> select sum(salary) from employees;
SUM(SALARY)
-----------
428000
Now for the technical side of things, these two functions are rarely used, in fact I have used then less than once in the past year.
Mã:
select variance(salary),
stddev(salary) f
rom employees;
You should see the output.
Mã:
SQL> select variance(salary), stddev(salary) from employees;
VARIANCE(SALARY) STDDEV(SALARY)
---------------- --------------
1.045E+09 32328.669
Sử dụng thêm các mệnh đề Ngoài việc muốn tính min() and max() chúng ta còn muốn tính theo nhóm đối tượng mà ở đây phân biệt theo giới tính.
select min(salary), max(salary)
from employees
group by sex;
Chúng ta thu được kết quả như sau:
Mã:
SQL> select min(salary), max(salary)
2 from employees
3 group by sex;
MIN(SALARY) MAX(SALARY)
----------- -----------
70000 99000
2000 80000
Tuy nhiên câu lệnh trên mới chỉ tính ra kết quả theo từng loại giới tính nhưng không thể hiện cho chúng ta thấy chính sác số nào thuộc giới nào. Câu lệnh sau cho chúng ta đầy đủ thông tin.
Như vậy câu lệnh của chúng ta sẽ là:
Mã:
select sex, min(salary), max(salary)
from employees
group by sex;
Và kết quả sẽ thu được đầy đủ như sau:
Mã:
SQL> select sex, min(salary), max(salary)
2 from employees
3 group by sex;
S MIN(SALARY) MAX(SALARY)
- ----------- -----------
F 70000 99000
M 2000 80000
Loại trừ dữ liệu (Excluding data)Chúng ta có thể dùng 02 phương thức để thực hiện loại trừ dữ liệu đó là mệnh đề “WHERE” cách thứ 2 là mệnh đề “HAVING”.
mệnh đề “WHERE” thực hiện điều kiện trên mọi bản ghi, còn mệnh đề 'HAVING' trên nhóm bản ghi định trước.
Tính tổng lương theo giới tính nếu có ít nhất 4 người thuộc cùng giới đó.
Mã:
select sex, sum(salary)
from employees
group by sex
having count(1) > 4;
Chúng ta có kết quả:
Mã:
SQL> select sex, sum(salary)
2 from employees
3 group by sex
4 having count(1) > 4;
S SUM(SALARY)
- -----------
M 259000
Như vậy có nhiều hơn 4 nam và ít hơn 4 nữ, chúng ta sẽ kiểm tra lại.
Mã:
select sex, count(1)
from employees
group by sex;
Kết quả thu được là:
SQL> select sex, count(1)
2 from employees
3 group by sex;
S COUNT(1)
- ---------
F 2
M 6
hot-oracle.com