All about Oracle Technologies

Saturday 28 March 2015

PIVOT và UNPIVOT (Phần 1)

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
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
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
    1
    2
    3
    4
    5
    6
    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
    1
    2
    3
    4
    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
    1
    2
    3
    4
    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.
1
2
3
4
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ả:
1
2
3
4
5
SELECT *
  FROM cust_sales_month UNPIVOT (yes
                        FOR month
                        IN ("JANUARY", "FEBRUARY", "MARCH"))
ORDER BY customer_id, product_id;
(Câu lệnh 6)

0 comments :

Post a Comment