ある案件で、大量のデータをPivot集計する必要があったため、Oracle11gで追加されたPivot関数を使ってみました。
11g以前でも集計関数とdecode,case関数を駆使すればPivotのようなことはできましたが、11gでは内部的に
同じようなことを行えるようになったようです。
それでは以下の様にtab1テーブルを用意し、任意のデータを投入しておきます。
SQL> desc tab1
Name Null? Type
----------------------------------------- -------- ----------------------------
DT NOT NULL DATE
ITEM_NAME VARCHAR2(1)
NUM VARCHAR2(4)
SQL> select * from tab1 where rownum
DT I NUM
------------------- - ----
2012/12/06 18:50:52 C 2
2012/12/06 18:50:53 C 3
2012/12/06 18:50:53 C 3
2012/12/06 18:50:56 C 6
2012/12/06 18:57:54 c 4
2012/12/06 18:58:13 c 3
2012/12/06 18:58:21 c 1
2012/12/06 18:58:52 C 2
2012/12/06 19:03:22 A 2
9 rows selected.
SQL> select count(*) from tab1;
COUNT(*)
----------
30638
SQL>
シチュエーションとしては「インターネットの時間限定販売でA,B,Cという商品を販売して、
1時間毎の販売数を集計」といった内容になるかと思います。
これまでは以下のように行ってきました。
SQL> select
2 to_char(dt,'yyyy/mm/dd hh24'),
3 sum(decode(item_name,'A',num,null)) A,
4 sum(decode(item_name,'B',num,null)) B,
5 sum(decode(item_name,'C',num,null)) C
6 from tab1
7 group by to_char(dt,'yyyy/mm/dd hh24')
8 order by 1
9 /
TO_CHAR(DT,'Y A B C
------------- ---------- ---------- ----------
2012/12/06 18 53 916
2012/12/06 19 9843 277 3200
2012/12/06 20 23510 631 1720
2012/12/06 21 11135 525 867
2012/12/06 22 95 328
SQL>
しかし、Pivot関数を使用すると以下のようにシンプルなクエリになりました。
SQL> select * from (
2 select to_char(dt,'yyyy/mm/dd hh24'),item_name,num from tab1)
3 pivot (sum(num) for item_name in ('A','B','C'))
4 order by dt
5 /
DT 'A' 'B' 'C'
------------- ---------- ---------- ----------
2012/12/06 18 53 916
2012/12/06 19 9843 277 3200
2012/12/06 20 23510 631 1720
2012/12/06 21 11135 525 867
2012/12/06 22 95 328
SQL>
集計するアイテム数が増えるほど、Pivot関数の便利さが感じられます。
