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)

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

1. Giới thiệu về PIVOT và UNPIVOT
2. Các vấn đề khác liên quan đến PIVOT và UNPIVOT
3. PIVOT với cột động (Dynamic SQL Pivot)

Read More

Cách sử dụng hàm LAG và LEAD

Xét ví dụ sau: Lấy danh sách lương nhân viên kèm theo cho biết mức lương cận kề trước và sau của nhân viên đó.
Ta nhìn vào bảng kết quả sau để hiểu rõ hơn về yêu cầu trên:

DEPTNO	ENAME	SAL	PRE_SAL	NEXT_SAL
20	SMITH	800	0	950
30	JAMES	950	800	1100
20	ADAMS	1100	950	1250
30	WARD	1250	1100	1250
30	MARTIN	1250	1250	1300
10	MILLER	1300	1250	1500
30	TURNER	1500	1300	1600
30	ALLEN	1600	1500	2450
10	CLARK	2450	1600	2850
30	BLAKE	2850	2450	2975
20	JONES	2975	2850	3000
20	SCOTT	3000	2975	3000
20	FORD	3000	3000	5000
10	KING	5000	3000	0

Ta thấy rằng mức lương của JAMES là 950 kèm theo đó cũng cho ta biết được mức lương kề trước đó là 800 (của SMITH) và 1100 (của ADAMS)

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

1 2 3 4 5 6

SELECT deptno, ename, sal, LAG (sal, 1, 0) OVER (ORDER BY sal) pre_sal, LEAD (sal, 1, 0) OVER (ORDER BY sal) next_sal FROM emp;

Ở đây chúng ta sử dụng 2 hàm LAG và LEAD để có được kết quả.
Cú pháp 2 hàm này như sau:

LAG  (value_expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause) LEAD (value_expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause)

 Trong đó:

• LAG cho phép ta lấy kết quả của dòng trước đó, LEAD cho phép ta lấy kết quả của dòng sau đó
• value_expression: cột cần lấy kết quả 
• offset: bước nhảy dòng hoặc số dòng trước đó hoặc sau đó
• default: giá trị default nếu như offset vượt quá ngưỡng. Như ví dụ trên ta thấy rằng dòng đầu tiên khi dùng hàm LAG thì trước đó không có dòng nào để lấy giá trị nên kết quả trả về sẽ là giá trị default mà ta nhập vào (ở đây là 0). Nếu như ta không nhập giá trị default này thì Oracle sẽ trả về cho chúng ta kết quả là NULL

Với 2 hàm này ta có thể sử dụng để thực hiện 1 số báo cáo như:

• Lấy doanh số ngày n kèm theo doanh số ngày n-1
  

  DATE	AMOUNT	AMOUNT2
  01/01/2014	50000	0
  02/01/2014	40000	50000
  03/01/2014	90000	40000

• Lấy lịch sử đơn đặt hàng của khách hàng cho biết ngày đặt hàng, số lượng hàng của ngày đặt hàng và ngày đặt hàng trước đó
  

  ORDER_DATE	AMOUNT	PRE_ORD_DATE	PRE_ORD_AMOUNT
  01/01/2014	30		0
  09/01/2014	80	01/01/2014	30
  16/01/2014	40	09/01/2014	80

• V.v...

Read More

Cách sử dụng hàm FIRST_VALUE và LAST_VALUE

Xét ví dụ sau: Lấy danh sách nhân viên cho biết với mỗi nhóm công việc thì mức lương tương ứng của nhân viên trong nhóm công việc đó là bao nhiêu kèm theo mức lương cao nhất và thấp nhất của nhóm đó.

Để hiểu rõ hơn về yêu cầu trên, ta nhìn vào bảng kết quả sau đây:

ENAME	JOB	SAL	Lowest	Highest
FORD	ANALYST	3000	3000	3000
SCOTT	ANALYST	3000	3000	3000
SMITH	CLERK	800	800	1300
JAMES	CLERK	950	800	1300
ADAMS	CLERK	1100	800	1300
MILLER	CLERK	1300	800	1300
CLARK	MANAGER	2450	2450	2975
BLAKE	MANAGER	2850	2450	2975
JONES	MANAGER	2975	2450	2975
KING	PRESIDENT	5000	5000	5000
MARTIN	SALESMAN	1250	1250	1600
WARD	SALESMAN	1250	1250	1600
TURNER	SALESMAN	1500	1250	1600
ALLEN	SALESMAN	1600	1250	1600

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

SELECT ename, job, sal, FIRST_VALUE ( sal) OVER (PARTITION BY job ORDER BY sal ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS "Lowest", LAST_VALUE ( sal) OVER (PARTITION BY job ORDER BY sal ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS "Highest" FROM emp;

Một vài lưu ý cho ví dụ trên như sau:

• Hàm FIRST_VALUE: hàm này cho phép ta lấy ra giá trị đầu tiên của 1 tập đã được sắp xếp. Như ví dụ trên đây, sau khi PARTITION thành từng nhóm theo JOB và sắp xếp theo SAL thì ta lấy ra giá trị đầu tiên của tập này. Như nhóm SALESMAN thì FIRST_VALUE sẽ là 1250
• Hàm LAST_VALUE: hàm này cho phép ta lấy ra giá trị cuối cùng của 1 tập đã được sắp xếp. Như ví dụ trên đây, sau khi PARTITION thành từng nhóm theo JOB và sắp xếp theo SAL thì ta lấy ra giá trị cuối cùng của tập này. Như nhóm SALESMAN thì LAST_VALUE sẽ là 1600
• Hàm FIRST_VALUE và LAST_VALUE có thể được dùng kèm với từ khóa IGNORE NULLS. Từ khóa này cho phép ta loại bỏ giá trị NULL nếu như kết quả trả về có giá trị NULL. Cách dùng như sau:
  FIRST_VALUE/LAST_VALUE (column_name) IGNORE NULLS OVER(...)

Kết quả câu lệnh trên sẽ thay đổi nếu như ta thay đổi điều kiện của PRECEDING và FOLLOWING. Xét ví dụ sau: Lấy danh sách nhân viên nhóm theo từng loại công việc và cho biết trong cùng 1 nhóm thì lương của nhân viên đó và lương của người trước đó chênh lệch bao nhiêu.
Đây là bảng kết quả của yêu cầu trên:

ENAME	JOB	SAL	PRE_SAL	DIFFERENCE
FORD	ANALYST	3000	3000	0
SCOTT	ANALYST	3000	3000	0
SMITH	CLERK	800	800	0
JAMES	CLERK	950	800	150
ADAMS	CLERK	1100	950	150
MILLER	CLERK	1300	1100	200
CLARK	MANAGER	2450	2450	0
BLAKE	MANAGER	2850	2450	400
JONES	MANAGER	2975	2850	125
KING	PRESIDENT	5000	5000	0
MARTIN	SALESMAN	1250	1250	0
WARD	SALESMAN	1250	1250	0
TURNER	SALESMAN	1500	1250	250
ALLEN	SALESMAN	1600	1500	100

Và đây là câu SELECT cho bảng kết quả trên:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

SELECT ename, job, sal, FIRST_VALUE ( sal) OVER (PARTITION BY job ORDER BY sal ASC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS "PRE_SAL", sal - FIRST_VALUE ( sal) OVER (PARTITION BY job ORDER BY sal ASC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS "DIFFERENCE" FROM emp

Read More