パソコン・インターネット

2012年12月11日 (火)

OracleでPivot

仕事で大量のデータを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関数のありがたみが増しますね。

その他のカテゴリー

無料ブログはココログ