機能Oracle 11g Pivot、Unpivot

バージョン11gでは、ピボット/アンピボット関数が登場しました（MS SQL 2005で最初に登場しました）。これにより、好きなように列の垂直データを動的に並べ替えることができます。







顧客テーブルがあるとします：

  SQL> descのお客様
名前ヌル？ 種類
 ----------------------------------------- --------- --------------------------
 CUST_ID NUMBER（10）
 CUST_NAME VARCHAR2（20）
 STATE_CODE VARCHAR2（2）
 TIMES_PURCHASED NUMBER（3） 

サンプルはどこですか

cust_id、state_code、times_purchasedを選択します

顧客から

cust_id による注文。

顧客の識別子、状態コード、および彼が何かを購入した回数を示します。

  CUST_ID STATE_CODE TIMES_PURCHASED
 ------- ---------- ---------------
 1 CT 1
 2 NY 10
 3 NJ 2
 4 NY 4 

各州ごとにグループ化された顧客数と注文数を調べる必要があります。

state_code、times_purchased、count（1）cntを選択します

顧客から

state_code、times_purchasedによるグループ化。

  ST TIMES_PURCHASED CNT
 ---------------- ----------
 CT 0 90
 CT 1 165
 CT 2 179
 CT 3 173
 CT 4 173
 CT 5 152
 ... 

このクエリは必要なものを返しますが、次の形式の方がはるかに便利です。

  Times_purch CT NY NJ ...
 1 0 1 0 ...
 2 23 119 37 ...
 3 17 45 1 ...
 ... 

バージョン11gより前では、これはsum（decode（state_code、 'CT'、1,0）“ CT”、sum（decode（state_code、 'NY'、1,0）“ NY”、...の繰り返しにより繰り返し実行する必要がありましたが、ピボット関数のおかげです。これを簡単に行うことができます。

選択*から（

「購入頻度」、state_codeとしてtimes_purchasedを選択します

顧客から

）ピボット（

カウント（state_code）

state_code in（「NY」は「New York」、「CT」は「Connecticut」、「NJ」は「New Jersey」、「FL」は「Florida」、「MO」は「Missouri」）

）

1で注文する

/

  Puchase Frequencyニューヨークコネチカットニュージャージーフロリダミズーリ
 ----------------- ---------- ----------- ----------- -------- ----------
 0 16 601 90 0 0 0
 1 33048 165 0 0 0
 2 33151179 0 0 0
 3 32 978 173 0 0 0
 4 33109173 0 1 0
 ... 

Unpivot関数はその逆を行います。



まだ11gに移行していない人のために、修正したTom Kiteコードを提供できます 。

create or replace type varchar2_table as table of varchar2(4000);

/

create or replace package PKG_PIVOT is



function pivot_sql (

p_max_cols_query in varchar2 default null

, p_query in varchar2

, p_anchor in varchar2_table

, p_pivot in varchar2_table

, p_pivot_head_sql in varchar2_table default varchar2_table()

)

return varchar2;



function pivot_ref (

p_max_cols_query in varchar2 default null

, p_query in varchar2

, p_anchor in varchar2_table

, p_pivot in varchar2_table

, p_pivot_name in varchar2_table default varchar2_table()

)

return sys_refcursor;



end PKG_PIVOT;

/

create or replace package body PKG_PIVOT is

/**

* Function returning query

*/

function pivot_sql (

p_max_cols_query in varchar2 default null

, p_query in varchar2

, p_anchor in varchar2_table

, p_pivot in varchar2_table

, p_pivot_head_sql in varchar2_table

) return varchar2

is

l_max_cols number;

l_query varchar2(4000);

l_pivot_name varchar2_table:=varchar2_table();

k integer ;

c1 sys_refcursor;

v varchar2(30);

begin

-- -

if (p_max_cols_query is not null ) then

execute immediate p_max_cols_query

into l_max_cols;

else

raise_application_error (-20001, 'Cannot figure out max cols' );

end if ;



--

l_query := 'select ' ;



for i in 1 .. p_anchor. count loop

l_query := l_query || p_anchor (i) || ',' ;

end loop;

--

k:=1;

if p_pivot_head_sql. count =p_pivot. count

then

for j in 1 .. p_pivot. count loop

open c1 for p_pivot_head_sql(j);

loop

fetch c1 into v;

l_pivot_name.extend(1);

l_pivot_name(k):=v;

EXIT WHEN c1%NOTFOUND;

k:=k+1;

end loop;

end loop;

end if ;



--

-- "max(decode(rn,1,C{X+1},null)) c_name+1_1"

for i in 1 .. l_max_cols loop

for j in 1 .. p_pivot. count loop

l_query := l_query || 'max(decode(rn,' || i || ',' || p_pivot (j) || ',null)) '

|| '"' ||l_pivot_name ((j-1)*l_max_cols+i) || '"' || ',' ;

end loop;

end loop;



--

l_query := rtrim (l_query, ',' ) || ' from ( ' || p_query || ') group by ' ;



--

for i in 1 .. p_anchor. count loop

l_query := l_query || p_anchor (i) || ',' ;

end loop;



l_query := rtrim (l_query, ',' );



-- SQL

return l_query;

end ;



/**

*

*/

function pivot_ref (

p_max_cols_query in varchar2 default null

, p_query in varchar2

, p_anchor in varchar2_table

, p_pivot in varchar2_table

, p_pivot_name in varchar2_table

) return sys_refcursor

is

p_cursor sys_refcursor;

begin

execute immediate 'alter session set cursor_sharing=force' ;

open p_cursor for pkg_pivot.pivot_sql (

p_max_cols_query

, p_query

, p_anchor

, p_pivot

, p_pivot_name

);

execute immediate 'alter session set cursor_sharing=exact' ;

return p_cursor;

end ;

end PKG_PIVOT;

/





使用例：

begin

:qq:=pkg_pivot.pivot_sql(

'select count(distinct trunc(dt)) from actions'

, 'select e.name name,sum(a.cnt) sum_cnt,a.dt,dense_rank() over(order by dt) rn from actions a left join emp e on e.id=a.emp group by e.name,a.dt'

, varchar2_table( 'NAME' )

, varchar2_table( 'SUM_CNT' )

, varchar2_table( 'select distinct ' 'Date ' '||trunc(dt) from actions' )

);

:qc :=pkg_pivot.pivot_ref(

'select count(distinct trunc(dt)) from actions'

, 'select e.name,sum(a.cnt) sum_cnt,a.dt,dense_rank() over(order by dt) rn from actions a left join emp e on e.id=a.emp group by e.name,a.dt'

, varchar2_table( 'NAME' )

, varchar2_table( 'SUM_CNT' )

, varchar2_table( 'select distinct ' 'Date ' '||trunc(dt) from actions' )

);

end ;



* This source code was highlighted with Source Code Highlighter .

結果：