読者です 読者をやめる 読者になる 読者になる

株式会社クイックのWebサービス開発blog

HAPPYなエンジニア&デザイナーのブログです

データ集計ケーススタディ「リストにないものも集計する-Pivot&Partitioned-」

データ分析

こんにちは。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の方が分かりやすいなと思って使ってみてます。

せっかくなのでもっと使用シーン見つけたいです。