こんにちは。yumeです。
サイトやアプリの設計から、運用、データ分析までなんでもやってます。
最近、”SQL文投げたら結果をhtmlのレポートにして出力する”ツールを作りました。
結果、「いろんな集計をSQL文にする」仕事に追われてます。
Oracleの11gからは、SQL文でPIVOT集計できるので便利ですね。
集計用のプログラム書いたり、Excelで一覧を取り込んでPIVOTさせる手間が省けるのでほんといいです。
ということで、PIVOTを使ったデータ集計の具体例を紹介します。
実際には社内のデータを集計したのですが、ここでは、みんな大好きSCOTTさんのEMPテーブルを用いて説明していきます。
SELECT * FROM scott.emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7369 SMITH CLERK 7902 80-12-17 800 20
7499 ALLEN SALESMAN 7698 81-02-20 1600 300 30
7521 WARD SALESMAN 7698 81-02-22 1250 500 30
7566 JONES MANAGER 7839 81-04-02 2975 20
7654 MARTIN SALESMAN 7698 81-09-28 1250 1400 30
7698 BLAKE MANAGER 7839 81-05-01 2850 30
7782 CLARK MANAGER 7839 81-06-09 2450 10
7788 SCOTT ANALYST 7566 87-04-19 3000 20
7839 KING PRESIDENT 81-11-17 5000 10
7844 TURNER SALESMAN 7698 81-09-08 1500 0 30
7876 ADAMS CLERK 7788 87-05-23 1100 20
7900 JAMES CLERK 7698 81-12-03 950 30
7902 FORD ANALYST 7566 81-12-03 3000 20
7934 MILLER CLERK 7782 82-01-23 1300 10
今回のお題
「部署ごとの、職種別の、コミッションをもらってる人の人数を数えて！」
まず手始めとして、「部署ごとの、職種別人数を数える」集計をやります。
部署ごとにカウントする以下の問い合わせを・・・
SELECT deptno,COUNT(empno) AS emp_num,job FROM scott.emp GROUP BY deptno,job;
副問い合わせとしてPIVOTに利用します。
SELECT * FROM( SELECT deptno,COUNT(empno) AS emp_num,job FROM scott.emp GROUP BY deptno,job )PIVOT (SUM(emp_num) FOR job IN('CLERK','SALESMAN','MANAGER','ANALYST','PRESIDENT'));
DEPTNO 'CLERK' 'SALESMAN' 'MANAGER' 'ANALYST' 'PRESIDENT'
---------- ---------- ---------- ---------- ---------- -----------
30 1 4 1
20 2 1 2
10 1 1 1さて今回のケース。
「部署ごとの、職種別の、"コミッションをもらってる人の人数"を数えて！」
まず素直にやってみました。
【A】
SELECT * FROM( SELECT deptno,count(empno) AS emp_num,job FROM scott.emp WHERE comm IS NOT NULL GROUP BY deptno,job )PIVOT (SUM(emp_num) FOR job IN('CLERK','SALESMAN','MANAGER','ANALYST','PRESIDENT'));
DEPTNO 'CLERK' 'SALESMAN' 'MANAGER' 'ANALYST' 'PRESIDENT'
---------- ---------- ---------- ---------- ---------- -----------
30 4⇒当然なんだけど、コミッションをもらっている人がいないdeptno10,20はリストに出ない。。。
「結果には、在籍社員がいる部署全部出して！」とのご要望が。
さてどうしよう？
集計対象外のデータを結果に含める方法
そこで思いついたのが3つの解決策です。
解決策1：「在籍社員がいる部署一覧」を作り、【A】の結果と片側結合する
解決策2：「【A】のPivot前の副問い合わせ」に、ダミーデータをunionする
解決策3：「【A】のPivot前の副問い合わせ」に、Partitioned Outer Joinを使う
それぞれのSQL、実行結果、平均実行時間を比較します。
※平均実行時間は、弊社内のテスト用テーブル(130万行くらい)に対し、
各解決策の方法で作成した問い合わせの実行時間の平均です。
※sqlplusのset timingコマンドで3回計測しています。
解決策1：「在籍社員がいる部署一覧」を作り、【A】の結果と片側結合する
SELECT emp1.deptno,piv2.* FROM (SELECT deptno FROM scott.emp GROUP BY deptno) emp1 LEFT JOIN (SELECT * FROM( SELECT deptno,COUNT(empno) AS emp_num,job FROM scott.emp WHERE comm IS NOT NULL GROUP BY deptno,job )PIVOT (SUM(emp_num) FOR job IN('CLERK','SALESMAN','MANAGER','ANALYST','PRESIDENT'))) piv2 ON emp1.deptno = piv2.deptno;
DEPTNO DEPTNO 'CLERK' 'SALESMAN' 'MANAGER' 'ANALYST' 'PRESIDENT'
---------- ---------- ---------- ---------- ---------- ---------- -----------
30 30 4
20
10この方法で試した時の平均実行時間：6.283秒
たぶん一番ぱっと思いつく方法かと思います。
ちょっとブサイクな表示になってますが、そこはなんとかなるでしょう（投げやり）。
解決策2：「【A】のPivot前の副問い合わせ」に、ダミーデータをunionする
SELECT * FROM( (SELECT deptno,COUNT(empno) AS emp_num,job FROM scott.emp WHERE comm IS NOT NULL GROUP BY deptno,job) UNION ALL (SELECT deptno,0 AS emp_num,job FROM scott.emp GROUP BY deptno,job) )PIVOT (SUM(emp_num) FOR job IN('CLERK','SALESMAN','MANAGER','ANALYST','PRESIDENT'));
DEPTNO 'CLERK' 'SALESMAN' 'MANAGER' 'ANALYST' 'PRESIDENT'
---------- ---------- ---------- ---------- ---------- -----------
30 0 4 0
20 0 0 0
10 0 0 0この方法で試した時の平均実行時間：11.810秒
nullと0とで少し結果に違いがありますが、できました。
最後がPIVOT集計で終わるので、集計の流れが追いやすいです。
WHERE句によりますが、今回のデータだと負荷がネックのようです。
解決策3：「【A】のPivot前の副問い合わせ」に、Partitioned Outer Joinを使う
"PIVOTに足りない行を補完する"という考え方でもうひとつ、Partitioned Outer Joinを使ったやり方を。
今まで使ったことありませんでしたが、マスタ側のテーブルに合わせて行を補完してくれるものだということで使ってみます。
Oracle10gから追加された機能らしいです。
まず、PIVOT前の副問い合わせ部分を作ってみます。
DEPTNOごとに足りない行を補完。
SELECT emp_trn.job,emp_mst.deptno,emp_trn.emp_num FROM (SELECT deptno FROM scott.emp GROUP BY deptno) emp_mst LEFT JOIN (SELECT deptno,COUNT(empno) AS emp_num,job FROM scott.emp WHERE comm IS NOT NULL GROUP BY deptno,job) emp_trn PARTITION BY (emp_trn.deptno) ON emp_mst.deptno = emp_trn.deptno;
JOB DEPTNO EMP_NUM
--------- ---------- ----------
10
20
SALESMAN 30 4補完できました。
ここではやってませんが、emp_numがnullの場合はcase文使って0にしてあげたらいいかもですね。
あとは、補完したものをPIVOT集計するだけ。
SELECT * FROM( SELECT emp_trn.job,emp_mst.deptno,emp_trn.emp_num FROM (SELECT deptno FROM scott.emp GROUP BY deptno) emp_mst LEFT JOIN (SELECT deptno,COUNT(empno) AS emp_num,job FROM scott.emp WHERE comm IS NOT NULL GROUP BY deptno,job) emp_trn PARTITION BY (emp_trn.deptno) ON emp_mst.deptno = emp_trn.deptno )PIVOT (SUM(emp_num) FOR job IN('CLERK','SALESMAN','MANAGER','ANALYST','PRESIDENT'));
DEPTNO 'CLERK' 'SALESMAN' 'MANAGER' 'ANALYST' 'PRESIDENT'
---------- ---------- ---------- ---------- ---------- -----------
30 4
20
10この方法で試した時の平均実行時間：6.830秒
実行時間も含めてなかなかいい感じ！
個人的な感想
今回は、背景事情があり「これ以上片側結合したくないよ」なテンションだったこと、
あとは「ピボット前のデータもリストとして利用したい=階層の最上位をピボット処理にしたい」
という思いがあったので解決策2とか3を考えました。
こういう要望って割とExcelでの集計でもありがちで、
いつも「一覧」+「集計結果」をsumproduct関数で参照してたりするんですよね。
ただ、Excelのピボットテーブルは割と操作しやすいので、
「最終形をPivotにしたい！」って時にも意外とこういう考え方が
有効だったりするのかなと思います。
...そもそも、SQLでの集計ってどんくらい需要あるんですかね？
私は、VBAでガリガリ集計用のプログラムを書いているファイルを見つけてしまい、
内容把握と保守がものすごく大変だったので、SQLの方が分かりやすいなと思って使ってみてます。
せっかくなのでもっと使用シーン見つけたいです。
