[Oracle]樞紐分析的好方法 PIVOT

 有時候計算庫齡與帳齡時，當計算完後總是要做個樞紐分析，才能得到最終的結果，忽然是否想到PLSQL是否有提供這個的方法可以使用

CREATE TABLE TEST_PIVOT(
 CUST_NUMBER     VARCHAR2(10),
 NAME            VARCHAR2(10),
 AR_AMOUNT       NUMBER,
 DAYS_PAST_DUE   NUMBER
)



INSERT INTO TEST_PIVOT(CUST_NUMBER,NAME,AR_AMOUNT,DAYS_PAST_DUE)VALUES('A01','Judy',1000,5);
INSERT INTO TEST_PIVOT(CUST_NUMBER,NAME,AR_AMOUNT,DAYS_PAST_DUE)VALUES('A01','Candy',500,20);
INSERT INTO TEST_PIVOT(CUST_NUMBER,NAME,AR_AMOUNT,DAYS_PAST_DUE)VALUES('A02','Teddy',999,10);
INSERT INTO TEST_PIVOT(CUST_NUMBER,NAME,AR_AMOUNT,DAYS_PAST_DUE)VALUES('A03','Andy',651,30);
INSERT INTO TEST_PIVOT(CUST_NUMBER,NAME,AR_AMOUNT,DAYS_PAST_DUE)VALUES('A04','Ray',300,-1);

SELECT * FROM TEST_PIVOT;

SELECT CUST_NUMBER, 
       NAME, 
       AR_AMOUNT,
       DAYS_PAST_DUE, 
       CASE
         WHEN DAYS_PAST_DUE < 0 THEN '未逾期'
         WHEN DAYS_PAST_DUE BETWEEN  1 AND 10  THEN '1~10天'
         WHEN DAYS_PAST_DUE BETWEEN 11 AND 20 THEN '11~20天'
         WHEN DAYS_PAST_DUE BETWEEN 21 AND 31 THEN '21~31天'
         WHEN DAYS_PAST_DUE > 32 THEN '大於32天'
       END AREA
  FROM TEST_PIVOT

CUST_NUMBER NAME      AR_AMOUNT   DAYS_PAST_DUE   AREA
-----------------------------------------------------------------
A01      Judy        1000         5               1~10天
A01      Candy        500         20              11~20天
A02      Teddy        999         10              1~10天
A03      Andy         651         30              21~31天
A04      Ray          300         -1              未逾期



SELECT CUST_NUMBER,
       NAME,
       AR_AMOUNT_SUM,
       "'未逾期'",
       "'1~10天'",
       "'11~20天'",
       "'21~31天'",
       "'大於32天'"
  FROM (SELECT PRE_PV.CUST_NUMBER,
               PRE_PV.NAME,
               PRE_PV.AREA,
               PRE_PV.AR_AMOUNT,
               SUM(PRE_PV.AR_AMOUNT) OVER(PARTITION BY CUST_NUMBER, NAME) AR_AMOUNT_SUM
          FROM (SELECT CUST_NUMBER,
                       NAME,
                       AR_AMOUNT,
                       CASE
                         WHEN DAYS_PAST_DUE < 0 THEN '未逾期'
                         WHEN DAYS_PAST_DUE BETWEEN 1 AND 10 THEN '1~10天'
                         WHEN DAYS_PAST_DUE BETWEEN 11 AND 20 THEN '11~20天'
                         WHEN DAYS_PAST_DUE BETWEEN 21 AND 31 THEN '21~31天'
                         WHEN DAYS_PAST_DUE > 32 THEN '大於32天'
                       END AREA
                  FROM TEST_PIVOT) PRE_PV) PIVOT(SUM(AR_AMOUNT) FOR AREA IN('未逾期', '1~10天', '11~20天', '21~31天', '大於32天'))
                  
CUST_NUMBER	NAME	AR_AMOUNT_SUM	'未逾期'	'1~10天'	'11~20天'	'21~31天'	'大於32天'
A01	        Judy	1000		                   1000			
A03	        Andy	651				                                       651	
A02	        Teddy	999		                      999			
A04	        Ray	  300	              300				
A01	        Candy	500			                            500