share facebook facebook2 twitter menu hatena pocket slack

2013.01.25 FRI

OracleでPivot

大黒 仁

WRITTEN BY大黒 仁

ある案件で、大量のデータを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<10 order by dt;

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関数の便利さが感じられます。

こちらの記事はなかの人(htshdkk)監修のもと掲載しています。
元記事は、こちら

大黒 仁

大黒 仁