[SQL Tune] Pivot/CrossTab SQL Query

開發商業智慧系統時, Crosstab的分析是很重要的技術, 或稱為Pivot. 將資料轉置成欄和列都有各自代表的維度, 而相對應的值就放置在中間, 如下表所示:

外銷總數量
USA China Japan
Jan 100 150 100
Feb 50 20 10
Mar 20 40 50
Apr 10 50 60
...

這對Excel而言是一件簡單的事情，因為樞紐分析大家都會，但是對關聯式資料庫而言，是一個
很大的挑戰， 因為關聯式資料庫的資料擺放型態是 Column-Value, 如下表:

月份        外銷國             數量
Jan         USA                10
Feb         China              30
....................................

這時在 Oracle怎麼解決呢? 有些人會在呈現端-Client想辦法, 不管是VB, ASP,
JSP將資料從Oracle讀進ResultSet後，聰明的程式設計師總有辦法將資料作CrossTab
的處理, 但問題是通常程式寫完後如果換做是其他人接手，應該會看不懂程式碼在寫
什麼？原因無他，因為太複雜！還有一個辦法是買 Solution, 像 Hyperion,
Oracle Discoverer, Business Object等應該都內建一些報表工具可以做CrossTab,
缺點不用說就是貴，不用個幾百萬，案子應該起不來!

如果可以在 Oracle端就將資料處理好, 再搭配前端的資料呈現工具，有一些比較簡單
的作法.常見的像是以下的SQL, 用 Decode 或是 Case, 用兩層SQL兜出想要的結果.但
是缺點呢? 一個是寫法還是有點小複雜, 如再要多個法國的外銷數量, 程式還要改.
當然後者的問題好解決，可將此邏輯寫成一個Function, 將可能的國別傳入Function後,
再透過該Function兜出SQL後執行.

=========================================================================
select Month,China,USA,Japan,China+USA+Japan
from (
select
Month,
max(case when Country='China' then qty else 0 end) China,
max(case when Country='USA' then qty else 0 end) USA,
max(case when Country='Japan' then qty else 0end)  Japan
from Sales_Value
group by Month
);
=========================================================================

OTN有一篇文章
(http://kr.forums.oracle.com/forums/thread.jspa?messageID=2487130)
就是談這一段，下面是節錄自該文章的Source Code. 原則上就是將要做
CrossTab的相關資訊傳入後，將SQL組合出來，有些語法會有點看不懂，因為程式裡
兜出來的SQL是用11G最新的Pivot SQL.
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
create or replace function getPivotSql(

p_sql            in varchar2,

p_x_col          in varchar2,

p_x_col_type     in varchar2 default 'DATE',

p_x_col_start    in varchar2,

p_x_col_interval in varchar2,

p_x_col_int_unit in varchar2 default 'DAY',

p_x_col_count    in number,

p_y_col          in varchar2,

p_cell_col       in varchar2,

p_cell_col_aggr  in varchar2 default NULL

) return varchar2

is

v_sql varchar2(32767);

begin

v_sql := '';

v_sql := v_sql || 'with data as ('||chr(10);

v_sql := v_sql || '  '||p_sql||chr(10);

v_sql := v_sql || ')';

if p_x_col_type = 'VARCHAR2' then

  v_sql := v_sql ||', x_dist_values as ('||chr(10);

  v_sql := v_sql ||'  select distinct '||p_x_col||' val from data order by 1'||chr(10);

  v_sql := v_sql ||'), x_values_rownum as ('||chr(10);

  v_sql := v_sql ||'  select rownum zeile, val from x_dist_values where rownum <= '||p_x_col_count||chr(10);     v_sql := v_sql ||')'||chr(10);   else     v_sql := v_sql || chr(10);   end if;   v_sql := v_sql || 'select distinct '||chr(10);   v_sql := v_sql || '  data.'||p_y_col||','||chr(10);   for i in 1..p_x_col_count loop     if p_cell_col_aggr is not null then       v_sql := v_sql || p_cell_col_aggr||'(';     end if;     v_sql := v_sql || '  case when ';     if p_x_col_type = 'VARCHAR2' then       v_sql := v_sql || 'x.zeile = '||i;     elsif p_x_col_type = 'NUMBER' then       v_sql := v_sql || ' data.'||p_x_col||' between '||         p_x_col_start||' + ('||(i - 1)||' * '||         p_x_col_interval || ') and '||p_x_col_start||' + ('||         i||' * '||p_x_col_interval||') ';     elsif p_x_col_type = 'DATE' then       v_sql := v_sql || ' data.'||p_x_col||' between '||        p_x_col_start||' + interval '''||((i - 1) * p_x_col_interval )||        ''' '||p_x_col_int_unit||' and '||p_x_col_start||        ' + interval '''||i * p_x_col_interval ||''' '||p_x_col_int_unit;     end if;     v_sql := v_sql ||' then '||p_cell_col|| ' else null end';     if p_cell_col_aggr is not null then       v_sql := v_sql || ')';     end if;     v_sql := v_sql || ' as "VALUE"';     if i < p_x_col_type =" 'VARCHAR2'">
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

下面就來細究Pivot SQL的語法 吧！將前面介紹的第一種方式改寫成新版本會向如下!
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
select * from (
select Month, Country
from Sales_Value t
)
pivot
(
count(Country)
for Month in ('USA','China','Japan')
)
order by Month;
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
看起來，簡單多了吧！ 結合起第二步驟的Function，CrossTab是不是變成比較簡單有條理
而且彈性變大了？ 不用花錢買BI Solution也可以讓公司使用者享有Excel樞紐分析的功能
喔！

OTN原文如下：
http://www.oracle.com/technology/pub/articles/oracle-database-11g-top-features/11g-pivot.html