PIVOT và UNPIVOT (Phần 1) ~ Oracle Tech Talk

Trong quá trình lấy số liệu, thiết lập bảng biểu và báo cáo, một trường hợp thường gặp là bạn phải hiển thị dữ liệu từ dạng dòng chuyển thành cột - kiểu truy vấn như vậy gọi là Pivot Query và ngược lại nghĩa là chuyển từ cột thành dòng - kiểu truy vấn như vậy gọi là Unpivot Query.

Để hiểu rõ hơn về 2 khái niệm trên ta thực hành ví dụ sau.

A. Khởi tạo dữ liệu

create table cust_sales
(
    customer_id varchar2(10),
    month varchar2(3),
    product_id varchar2(6),
    sales number
);

insert into cust_sales values('Adam','Jan','Prod A',34);
insert into cust_sales values('Adam','Feb','Prod B',49);
insert into cust_sales values('Adam','Mar','Prod C',134);
insert into cust_sales values('Jones','Feb','Prod A',92);
insert into cust_sales values('Jones','Mar','Prod A',34);
insert into cust_sales values('Kanes','Jan','Prod B',24);
insert into cust_sales values('Kanes','Feb','Prod C',83);
insert into cust_sales values('Kanes','Mar','Prod A',39);
 
create table cust_sales_month
(
    customer_id varchar2(10),  
    product_id varchar2(6),
    january number,
    february number,
    march number
);
 
insert into cust_sales_month values('Kanes','Prod A',0,0,1);
insert into cust_sales_month values('Jones','Prod A',0,1,1);
insert into cust_sales_month values('Kanes','Prod C',0,1,0);
insert into cust_sales_month values('Kanes','Prod B',1,0,0);
insert into cust_sales_month values('Adam','Prod B',0,1,0);
insert into cust_sales_month values('Adam','Prod A',1,0,0);
insert into cust_sales_month values('Adam','Prod C',0,0,1);
 
commit;

(Nhóm lệnh 1)

Chúng ta chú ý như sau:

Bảng cust_sales: dùng cho việc thực hành các vấn đề liên quan đến PIVOT
Bảng cust_sales_month: dùng cho việc thực hành các vấn đề liên quan đến UNPIVOT

B. Các vấn đề liên quan đến PIVOT:
Ta chạy câu lệnh sau:

1	SELECT * FROM cust_sales;

(Câu lệnh 1)

Kết quả:

(Hình 1)

Với kết quả trên thì việc thống kê rất khó để theo dõi cho các yêu cầu sau:

Yêu cầu 1: Thống kê kết quả bán hàng theo sản phẩm

(Hình 2)
Yêu cầu 2: Thống kê kết quả bán hàng theo tháng

(Hình 3)
Yêu cầu 3: Thống kê kết quả bán hàng theo khách hàng

(Hình 4)

Để thực hiện được các yêu cầu thống kê như trên ta có thể dùng CASE để làm (hàm này không được giới thiệu trong phạm vi bài này) hoặc dùng hàm PIVOT để thực hiện (lưu ý: hàm PIVOT chỉ có từ phiên bản Oracle 11g trở lên).

Cú pháp hàm như sau:

SELECT...

FROM ...

PIVOT[XML]

( pivot_clause

pivot_for_clause

pivot_in_clause )

WHERE ...

Trong đó:

pivot_clause: khai báo cột được tính toán bởi các hàm thống kê như SUM, COUNT, MAX, MIN, AVG, ...
pivot_for_clause: khai báo cột sẽ được nhóm lại khi thực hiện thống kê bởi hàm được dùng trong pivot_clause
pivot_in_clause: khai báo các giá trị thỏa mãn điều kiện để thực hiện pivot

Để hiểu rõ hơn về các mệnh đề được dùng trong PIVOT ta map các nội dung của yêu cầu 1 vào các mệnh đề PIVOT như sau:

Yêu cầu 1: Thống kê kết quả bán hàng theo sản phẩm

pivot_clause: thống kê kết quả bán hàng --> sum(sales)
pivot_for_clause: theo sản phẩm --> product_id
pivot_in_clause: kết quả hiển thị gồm các sản phẩm Prod A, Prod B, Prod C ở 3 cột --> ('Prod A', 'Prod B', 'Prod C')

Ta phân tích tương tự cho các yêu cầu 2 và 3 còn lại. Và đây là Pivot Query cho 3 yêu cầu trên:

Yêu cầu 1: Thống kê kết quả bán hàng theo sản phẩm

SELECT *
 FROM cust_sales PIVOT (SUM(sales)
                  FOR product_id
                  IN ('Prod A' AS product_a,
                      'Prod B' AS product_b,
                      'Prod C' AS product_c));

(Câu lệnh 2)

Yêu cầu 2: Thống kê kết quả bán hàng theo tháng

SELECT *
  FROM cust_sales PIVOT (SUM (sales)
                  FOR month
                  IN ('Jan' AS january, 'Feb' AS february, 'Mar' AS march));

(Câu lệnh 3)

Yêu cầu 3: Thống kê kết quả bán hàng theo khách hàng

SELECT *
 FROM cust_sales PIVOT (SUM(sales)
                  FOR customer_id
                  IN('Adam','Jones','Kanes'));

(Câu lệnh 4)

C. Các vấn đề liên quan đến UNPIVOT:

Unpivot là trường hợp ngược lại của PIVOT nghĩa là khi ta có 1 tập kết quả đang được hiển thị dưới dạng cột ta muốn biến chuyển kết quả này hiển thị dưới dạng dòng. Xem ví dụ sau:

1	SELECT * FROM cust_sales_month;

(Câu lệnh 5)

(Hình 5)

Bảng dữ liệu trên cho biết các khách hàng có mua sản phẩm vào các tháng nào trong năm. Nếu như tháng nào mua thì đánh dấu là 1 ngược lại là 0. Với bảng dữ liệu trên ta muốn tùy biến hiển thị dữ liệu theo dạng dòng như sau:

CUSTOMER_ID	PRODUCT_ID	MONTH	YES
Adam	Prod A	JANUARY	1
Adam	Prod A	FEBRUARY	0
Adam	Prod A	MARCH	0
Adam	Prod B	FEBRUARY	1
Adam	Prod B	JANUARY	0
Adam	Prod B	MARCH	0
Adam	Prod C	FEBRUARY	0
Adam	Prod C	JANUARY	0
Adam	Prod C	MARCH	1
Jones	Prod A	FEBRUARY	1
Jones	Prod A	JANUARY	0
Jones	Prod A	MARCH	1
Kanes	Prod A	MARCH	1
Kanes	Prod A	JANUARY	0
Kanes	Prod A	FEBRUARY	0
Kanes	Prod B	FEBRUARY	0
Kanes	Prod B	JANUARY	1
Kanes	Prod B	MARCH	0
Kanes	Prod C	MARCH	0
Kanes	Prod C	FEBRUARY	1
Kanes	Prod C	JANUARY	0

(Bảng kết quả 1)

Trước khi đi đến cách thực hiện, ta tìm hiểu sơ qua cú pháp của hàm UNPIVOT.

UNPIVOT
( unpivot_clause
  unpivot_for_clause
  unpivot_in_clause )

Trong đó:

unpivot_clause: khác với PIVOT, mệnh đề được khai báo ở đây không thực hiện tính toán; nó chỉ đơn thuần là tên cột dùng để hiển thị dữ liệu sau khi unpivot
unpivot_for_clause: khai báo cột cần unpivot để lấy giá trị tương ứng với tên cột được khai báo ở unpivot_clause
unpivot_in_clause: khai báo danh sách các giá trị khi thực hiện UNPIVOT

Để hiểu rõ hơn cách sử dụng hàm UNPIVOT, ta xét yêu cầu của bảng kết quả 4: Thống kê danh sách khách hàng có sản phẩm nào được mua / không mua theo từng tháng. Ta thực hiện map các nội dung trên vào các mệnh đề unpivot:

unpivot_clause: mua / không mua --> tên cột chứa giá trị
unpivot_for_clause: theo từng tháng --> giá trị cột month
unpivot_in_clause: theo từng tháng --> ở đây cần thống kê 3 tháng: JANUARY, FEBRUARY và MARCH

Và đây là kết quả:

SELECT *
  FROM cust_sales_month UNPIVOT (yes
                        FOR month
                        IN ("JANUARY", "FEBRUARY", "MARCH"))
ORDER BY customer_id, product_id;

(Câu lệnh 6)

Các bài viết có liên quan

Oracle Tech Talk

All about Oracle Technologies

Saturday, 28 March 2015

PIVOT và UNPIVOT (Phần 1)

1 comment :