Bài viết này được viết bởi Tác Giả: Phạm Ngọc Hùng, mình thấy bài viết này rất hay và hữu ích nên đăng lại để các bạn có thể tham khảo.

[SQLSERVER] Sử dụng GROUP BY và ROLLUP, CUBE để thống kê dữ liệu

Để thống kê dữ liệu, các hệ quản trị cơ sở dữ liệu cung cấp các hàm tổng hợp dữ liệu như AVG(), SUM(), COUNT(),… sử dụng kết hợp với mệnh đề GROUP BY.

Bài viết này giới thiệu cách sử dụng mệnh đề GROUP BY và các tùy chọn mở rộng ROLLUP, CUBE với các hàm tổng hợp dữ liệu trong hệ quản trị CSDL SQL Server.

Tạo dữ liệu minh họa

Trước hết, ta cần tạo dữ liệu để minh họa cho ví dụ này. Xét một TABLE Student chứa thông tin về điểm các môn học của sinh viên:

Tạo bảng trên bằng lệnh CREATE TABLE sau:

create table Student ( [name] nchar(30), [subject] nchar(10), [mark] float);

Chèn dữ liệu vào trong TABLE Student như sau:

insert into Student values('Hung', 'JAVA', 8);
insert into Student values('Hung', 'C', 9);
insert into Student values('Hung', 'C', 7);
insert into Student values('Hung', 'SQL', 5);
insert into Student values('Tuan', 'JAVA', 4);
insert into Student values('Tuan', 'C', 7);
insert into Student values('Tuan', 'SQL', 10);
insert into Student values('Tuan', 'SQL', 5);

Để kiểm tra dữ liệu đã được thêm vào đủ chưa, ta chạy lệnh sau:

SELECT [name] AS [Student name], [subject] AS [Subject name], [mark] AS [Mark] FROM Student;

Khi đó, ta thu được kết quả giống như hình minh họa ở trên.

Thống kê dữ liệu

Sử dụng mệnh đề GROUP BY cùng với hàm tính giá trị trung bình AVG, ta có thể tính điểm trung bình theo môn của từng sinh viên. Gõ lệnh SELECT (1) như sau:

SELECT [name] as [Student name], [subject] as [Subject name], 
    AVG([mark])  as [Average mark] FROM Student GROUP BY [name], [subject];

Lệnh này sẽ trả lại kết quả như sau:

Cột bên thứ 3 (không có tên) chứa điểm trung bình từng môn của sinh viên.

Trong việc quản lý sinh viên, người dùng còn có nhu cầu tính điểm trung bình tất cả các môn của mỗi sinh viên và điểm trung bình chung của tất cả sinh viên như sau:

Khi đó, ta sử dụng thêm các tùy chọn ROLLUP và CUBE vào trong mệnh đề GROUP BY.

Thay lệnh SELECT (1) ở trên bằng lệnh SELECT (2) có thêm tùy chọn ROLLUP như sau:

SELECT [name] as [Student name], [subject] as [Subject name], AVG([mark]) as [Average mark] 
    FROM Student GROUP BY [name], [subject] WITH ROLLUP;

Ta thu được kết quả:

Trong kết quả trên, tùy chọn ROLLUP đã chèn thêm các dòng tính điểm trung bình tất cả môn học của từng sinh viên. Tại ô tên môn học, ROLLUP cho giá trị NULL.

Ta có thể sửa các giá trị NULL này thành tên gọi mang ý nghĩa tại các ô đó bằng cách sử dụng Hàm GROUPING() để xác định xem trường (FIELD) [name] hoặc [subject] có phải hiện đang được nhóm hay không với lệnh SELECT (3) như sau:

SELECT 
   CASE when GROUPING([name]) = 1 THEN 'All students' ELSE [name] END 
      AS [Student name],
   CASE when GROUPING([subject]) = 1 THEN 'All subjects' ELSE [subject] END
      AS [Subject name], 
   CAST(AVG([mark]) AS DECIMAL(9, 2)) AS [Average mark] 
   FROM Student GROUP BY [name], [subject] WITH ROLLUP;

Khi đó, ta sẽ nhận được kết quả như mong muốn:

Ta có nhận xét là, trong kết quả trên, ta chỉ tính được điểm trung bình từng môn của từng sinh viên cũng như điểm trung bình tất cả các môn của từng sinh viên, (tức là tính trung bình theo sinh viên), nhưng không có điểm trung bình của tất cả sinh viên cho từng môn.(chẳng hạn điểm trung bình của tất cả sinh viên trong môn lập trình C).

Để tính loại điểm trung bình thứ 3, ta có thể đảo ngược vị trí của [name] và [subject] trong mệnh đề GROUP BY như sau:

SELECT [subject] as [Subject name], [name] as [Student name], AVG([mark]) 
     FROM Student GROUP BY [subject], [name] WITH ROLLUP;

Tuy nhiên, để kết quả có cả 3 loại điểm trung bình trên, tùy chọn ROLLUP không đáp ứng được, mà ta cần sử dụng tùy chọn CUBE.

Thay thế tùy chọn CUBE cho ROLLUP trong lệnh SELECT (3), ta có lệnh SELECT (4):

SELECT 
   CASE when GROUPING([name]) = 1 THEN 'All students' ELSE [name] END 
      AS [Student name],
   CASE when GROUPING([subject]) = 1 THEN 'All subjects' ELSE [subject] END
      AS [Subject name], 
   CAST(AVG([mark]) AS DECIMAL(9, 2)) AS [Average mark] 
   FROM Student GROUP BY [name], [subject] WITH CUBE;

Khi chạy lệnh này, ta thu được kết quả:

Have Fun :)

Tác giả: Pham Ngọc Hùng