11

TRƯỜNG ĐẠI HỌC KHOA HỌC TỰ NHIÊN TP.HCM

TRUNG TÂM TIN HỌC

Gom nhóm dữ liệu

22

TRƯỜNG ĐẠI HỌC KHOA HỌC TỰ NHIÊN TP.HCM

TRUNG TÂM TIN HỌC

Các hàm nhóm dữ liệu (group functions) ?Các hàm nhóm dữ liệu là phép toán trên một tập các dòng để trả về một kết quả tương ứng mỗi nhóm NHANVIEN

Tổng lương các nhân viên trong bảng NHANVIEN .

…

33

TRƯỜNG ĐẠI HỌC KHOA HỌC TỰ NHIÊN TP.HCM

TRUNG TÂM TIN HỌC

Các hàm nhóm dữ liệu• AVG ([DISTINCT|ALL] bieu_thuc)• COUNT ({*|[DISTINCT|ALL] bieu_thuc}) • MAX ([DISTINCT|ALL] bieu_thuc)• MIN ([DISTINCT|ALL] bieu_thuc)• SUM ([DISTINCT|ALL] bieu_thuc)

44

TRƯỜNG ĐẠI HỌC KHOA HỌC TỰ NHIÊN TP.HCM

TRUNG TÂM TIN HỌC

SELECT [cot,] ham_nhom_du_lieu(cot), ...FROM bang[WHERE dieu_kien][GROUP BY cot][ORDER BY cot]

Cú pháp các hàm nhóm dữ liệu

55

TRƯỜNG ĐẠI HỌC KHOA HỌC TỰ NHIÊN TP.HCM

TRUNG TÂM TIN HỌC

SELECT AVG(mucluong) as AVG_LLuong, MAX(mucluong) as MAX_Luong,

MIN(mucluong) as MIN_Luong, SUM(mucluong) as SUM_Luong

FROM qlns.nhanvienWHERE macongviec LIKE ‘%TT%'

Sử dụng các hàm AVG,SUM & MAX,MINAVG và SUM : dùng cho dữ liệu kiểu số.

MIN and MAX : Có thể dùng cho phần lớn các kiểu dữ liệu

SELECT MIN(ngayvaolam), MAX(ngayvaolam)FROM qlns.nhanvien

66

TRƯỜNG ĐẠI HỌC KHOA HỌC TỰ NHIÊN TP.HCM

TRUNG TÂM TIN HỌC

SELECT COUNT(*)FROM qlns.nhanvienWHERE maphong = 50

Sử dụng hàm COUNTCOUNT(*) Trả về số dòng có trong bảng.

COUNT(bieu_thuc) trả về số dòng khác null của bieu_thuc.SELECT COUNT(phucap)FROM qlns.nhanvienWHERE maphong = 50

77

TRƯỜNG ĐẠI HỌC KHOA HỌC TỰ NHIÊN TP.HCM

TRUNG TÂM TIN HỌC

SELECT COUNT(DISTINCT maphong)FROM qlns.nhanvien;

Sử dụng từ khóa DISTINCT• COUNT(DISTINCT bieu_thuc) Trả về số dòng

khác nhau và khác NULL của bieu_thuc.

• Ví dụ : Cho biết có bao nhiêu phòng ban khác nhau trong bảng NHANVIEN .

88

TRƯỜNG ĐẠI HỌC KHOA HỌC TỰ NHIÊN TP.HCM

TRUNG TÂM TIN HỌC

SELECT AVG(phucap)FROM qlns.nhanvien

Giá trị NULL và trong các hàm nhóm dữ liệu

Các hàm nhóm dữ liệu sẽ tự động bỏ qua các giá trị NULL trong cột khi thực hiện tính toán.

99

TRƯỜNG ĐẠI HỌC KHOA HỌC TỰ NHIÊN TP.HCM

TRUNG TÂM TIN HỌC

Tạo các phân nhóm dữ liệuNHANVIEN

Tổng lương trong bảngNHANVIENcủa mỗi phòng

15.000.000

…

16.500.000

13.000.000

5.200.000

10.500.000

8.500.000

4.000.000

1010

TRƯỜNG ĐẠI HỌC KHOA HỌC TỰ NHIÊN TP.HCM

TRUNG TÂM TIN HỌC

SELECT cot, ham_nhom_du_lieu(cot)FROM bang[WHERE dieu_kien][GROUP BY bieu_thuc_phan_nhom][ORDER BY cot]

Tạo các phân nhóm dữ liệu : Mệnh đề GROUP BY

1111

TRƯỜNG ĐẠI HỌC KHOA HỌC TỰ NHIÊN TP.HCM

TRUNG TÂM TIN HỌC

SELECT maphong, AVG(mucluong)FROM qlns.nhanvienGROUP BY maphong

Sử dụng mệnh đề GROUP BYTất cả các cột trong danh sách của SELECT nếu không

phải là hàm nhóm dữ liệu thì phải tồn tại trong mệnh đề GROUP BY .

1212

TRƯỜNG ĐẠI HỌC KHOA HỌC TỰ NHIÊN TP.HCM

TRUNG TÂM TIN HỌC

Sử dụng mệnh đề GROUP BYCác cột trong GROUP BY không bắt buộc phải có trong

danh sách của SELECTSELECT AVG(mucluong)FROM qlns.nhanvienGROUP BY maphong

1313

TRƯỜNG ĐẠI HỌC KHOA HỌC TỰ NHIÊN TP.HCM

TRUNG TÂM TIN HỌC

Tạo phân nhóm trên nhiều cộtNHANVIEN

“Tính tổng lương cho mỗi công

việc và phân nhóm theo phòng

ban

…

1414

TRƯỜNG ĐẠI HỌC KHOA HỌC TỰ NHIÊN TP.HCM

TRUNG TÂM TIN HỌC

SELECT maphong maph, macongviec, SUM(mucluong)FROM qlns.nhanvienGROUP BY maphong, macongviecORDER BY maphong, macongviec

Sử dụng GROUP BY trên nhiều cột

1515

TRƯỜNG ĐẠI HỌC KHOA HỌC TỰ NHIÊN TP.HCM

TRUNG TÂM TIN HỌC

Một số lưu ý khi sử dụng các hàm nhóm dữ liệu

Bất kỳ một cột hay biểu thức trong danh sách của SELECT không phải là hàm nhóm dữ liệu thì phải có mệnh đề GROUP BY

SELECT maphong, COUNT(tennv)FROM qlns.nhanvien

Thiếu các cột trong mệnh đề GROUP BY

1616

TRƯỜNG ĐẠI HỌC KHOA HỌC TỰ NHIÊN TP.HCM

TRUNG TÂM TIN HỌC

Một số lưu ý khi sử dụng các hàm nhóm dữ liệu• Không thể sử dụng mệnh đề WHERE để giới hạn bớt

các nhóm.

• Sử dụng mệnh đề HAVING để lọc bớt các nhóm.

• Không được phép sử dụng các hàm nhóm dữ liệu trong mệnh đề WHERE.SELECT maphong, AVG(mucluong)FROM qlns.nhanvienWHERE AVG(mucluong) > 8000000GROUP BY maphong

Không thể sử dụng WHERE để lọc bớt các nhóm

1717

TRƯỜNG ĐẠI HỌC KHOA HỌC TỰ NHIÊN TP.HCM

TRUNG TÂM TIN HỌC

Lọc bớt kết quả phân nhóm

Liệt kê những phòng ban có MAX lương lớn

hon 8.500.000 đ

NHANVIEN

…

1818

TRƯỜNG ĐẠI HỌC KHOA HỌC TỰ NHIÊN TP.HCM

TRUNG TÂM TIN HỌC

SELECT cotcolumn, ham_nho_du_lieuFROM bang[WHERE dieu_kien][GROUP BY bieu_thuc_nhom_du_lieu][HAVING dieu_kien_loc_nhom_du_lieu][ORDER BY cot]

Mệnh đề HAVINGĐể giới hạn bớt các nhóm trả về :1. Các dòng đã được phân nhóm.2. Các hàm nhóm dữ liệu đã đưa vào.3. Chỉ những nhóm thỏa điều kiện HAVING mới

nằm trong tập kết quả trả về.

1919

TRƯỜNG ĐẠI HỌC KHOA HỌC TỰ NHIÊN TP.HCM

TRUNG TÂM TIN HỌC

Sử dụng mệnh đề HAVINGSELECT maphong, MAX(mucluong) as [Max Luong]FROM qlns.nhanvienGROUP BY maphongHAVING MAX(mucluong)>=10.000.000

2020

TRƯỜNG ĐẠI HỌC KHOA HỌC TỰ NHIÊN TP.HCM

TRUNG TÂM TIN HỌC

SELECT macongviec, SUM(mucluong) TIENLUONGFROM qlns.nhanvienWHERE macongviec NOT LIKE '%TTHI%'GROUP BY macongviecHAVING SUM(mucluong) >= 10.000.000ORDER BY SUM(mucluong)

Sử dụng mệnh đề HAVING

2121

TRƯỜNG ĐẠI HỌC KHOA HỌC TỰ NHIÊN TP.HCM

TRUNG TÂM TIN HỌC

Thực hành

• Viết truy vấn sử dụng các hàm phân nhóm

• Phân nhóm các dòng kết hợp hàm phân nhóm

• Kết hợp mệnh đề HAVING, mệnh đề WHERE