まえがき

rollup, cube, grouping_id, grouping_setsを使用して
マトリクスをどこまで作成できるか挑戦したいと思い、実践します。

元データ

SCOTT/TIGERでやってみようと思います。

grouping_idについて

grouping_idの返却値は
引数に指定した列を集約単位として扱わない場合に１をたたせ、
それ以外を０にねかせた値を１０進数に変換した値です。

集約単位に応じて集約キーが表示されなくなる列を考えて書いています。
表示されなくなる列は１を立てます。

部署・jobごとに集約するのであれば、どちらも表示します。
０＊２＾１＋０＊２＾０⇒０＋０⇒０

部署ごとに集約するのであれば、
どのjobを表示するかは気にしていないので、jobは表示しません。
０＊２＾１＋１＊２＾０⇒０＊２＋１＊１⇒０＋１⇒１

jobごとに集約するのであれば、
どの部署を表示するかは気にしていないので、部署は表示しません。
１＊２＾１＋０＊２＾０⇒１＊２＋０＊１⇒２＋０⇒２

全体で集約するのであれば、
どの部署・どのjobを表示するかは気にしていないので、
jobも部署も表示しません。
１＊２＾１＋１＊２＾０⇒１＊２＋１＊１⇒２＋１⇒３

grouping_setsの場合

grouping_sets.sql


SELECT
    CASE
        WHEN GROUPING_ID(deptno,job) = '0' THEN '部署jobごと'--（０＋０）
        WHEN GROUPING_ID(deptno,job) = '1' THEN '部署ごと'--（０＋１）
        WHEN GROUPING_ID(deptno,job) = '2' THEN 'jobごと'--（１＋０）
        WHEN GROUPING_ID(deptno,job) = '3' THEN '全体'--（１＋１）
    END AS 集約単位
    ,GROUPING_ID(deptno,job)
    ,deptno
    ,job
    ,SUM(sal)
FROM
    emp
GROUP BY
    GROUPING SETS (
                    (deptno,job)--部署job単位で集約
                    ,(deptno)--部署単位で集約
                    ,(job)--job単位で集約
                    ,()--全量で集約
                    )
ORDER BY
    deptno
    ,job
;

【grouping_setsの取得結果】

rollupの場合①

rollup、指定した列の順序気にします。。

部署単位表示されません。

GROUPING_ID(deptno,job)も１の値はありません。

rollup_1.sql


SELECT
    CASE
        WHEN GROUPING_ID(deptno,job) = '0' THEN '部署jobごと'--（０＋０）
        WHEN GROUPING_ID(deptno,job) = '1' THEN '部署ごと'--（０＋１）
        WHEN GROUPING_ID(deptno,job) = '2' THEN 'jobごと'--（１＋０）
        WHEN GROUPING_ID(deptno,job) = '3' THEN '全体'--（１＋１）
    END AS 集約単位
    ,GROUPING_ID(deptno,job)
    ,job
    ,deptno
    ,SUM(sal)
FROM
    emp
GROUP BY
    ROLLUP(job,deptno)
--以下と等価
--GROUPING SETS (
--				(DEPTNO,JOB)--部署JOB単位で集約
--				,(JOB)--JOB単位で集約
--				,()--全量で集約
--				)
ORDER BY
    job
    ,deptno
;

【rollup①の取得結果】

rollupの場合②

rollup、指定した列の順序気にします。。

job単位表示されません。

GROUPING_ID(deptno,job)も２の値はありません。

rollup_2.sql


SELECT
    CASE
        WHEN GROUPING_ID(deptno,job) = '0' THEN '部署jobごと'--（０＋０）
        WHEN GROUPING_ID(deptno,job) = '1' THEN '部署ごと'--（０＋１）
        WHEN GROUPING_ID(deptno,job) = '2' THEN 'jobごと'--（１＋０）
        WHEN GROUPING_ID(deptno,job) = '3' THEN '全体'--（１＋１）
    END AS 集約単位
    ,GROUPING_ID(deptno,job)
    ,job
    ,deptno
    ,SUM(sal)
FROM
    emp
GROUP BY
    ROLLUP(deptno,job)
--以下と等価
--    GROUPING SETS (
--                    (deptno,job)--部署job単位で集約
--                    ,(deptno)--部署単位で集約
--                    ,()--全量で集約
--                    )
ORDER BY
    deptno
    ,job
;

【rollup②の取得結果】

cubeの場合

cube、順序気にしません。。

cube.sql


SELECT
    CASE
        WHEN GROUPING_ID(deptno,job) = '0' THEN '部署jobごと'--（０＋０）
        WHEN GROUPING_ID(deptno,job) = '1' THEN '部署ごと'--（０＋１）
        WHEN GROUPING_ID(deptno,job) = '2' THEN 'jobごと'--（１＋０）
        WHEN GROUPING_ID(deptno,job) = '3' THEN '全体'--（１＋１）
    END AS 集約単位
    ,GROUPING_ID(deptno,job)
    ,job
    ,deptno
    ,SUM(sal)
FROM
    emp
GROUP BY
    CUBE(job,deptno)
--以下と等価
--    GROUPING SETS (
--                    (deptno,job)--部署job単位で集約
--                    ,(deptno)--部署単位で集約
--                    ,(job)--job単位で集約
--                    ,()--全量で集約
--                    )
ORDER BY
    deptno
    ,job
;

【cubeの取得結果】

やってみたかったこと

まえおき長くなりましたが、
こういうの作ってみたかったのです。。

つくります。

サマリ行とサマリ行以外で分けて考える・・
サマリ行とサマリ行以外をunion allでくっつける。。

【サマリ行以外】
サマリ行より上は、よくある縦持ちを横持ちにするやつです。
部署ごとの縦横変換ですので、部署がnullでないやつを
sub.deptno IS NOT NULLで抽出しています。

部署ごとの合計列を表示したいので、
sub.aggflg = '1' AND sub.JOB IS NULLで対象行を見つけにいっています。

同じ部署に属するJOBごとに列を設けたいので、
sub.aggflg = '0' AND sub.JOB = 'CLERK'
sub.aggflg = '0' AND sub.JOB = 'SALESMAN'
sub.aggflg = '0' AND sub.JOB = 'MANAGER'
sub.aggflg = '0' AND sub.JOB = 'ANALYST'
sub.aggflg = '0' AND sub.JOB = 'PRESIDENT'

のようにして、JOB数分、対象行を見つけにいっています。

【サマリ行】
サマリ行は各列の合計になっていてほしいので、
ここでの各列はJOBです。

sub.aggflg = '2' AND sub.JOB = 'CLERK'
sub.aggflg = '2' AND sub.JOB = 'SALESMAN'
sub.aggflg = '2' AND sub.JOB = 'MANAGER'
sub.aggflg = '2' AND sub.JOB = 'ANALYST'
sub.aggflg = '2' AND sub.JOB = 'PRESIDENT'

のようにして、JOB数分、対象行を見つけにいっています。
sub.aggflg = '2' はJOBごとの集計結果です。。

部署合計列に表示するのは、
JOBごとの集計結果と部署ごとの集計結果の足し算なので、

sub.aggflg = '3' AND sub.JOB IS NULL AND sub.deptno IS NULL

のようにして、対象行を見つけにいっています。

それと、集計単位が２、３の行しか使わないので、あらかじめ
sub.aggflg IN ('2','3')で対象行を絞っています。

matrix.sql


SELECT
    to_char(sub.deptno) AS deptno
    ,MAX(
            CASE
                WHEN sub.aggflg = '0' AND sub.JOB = 'CLERK' THEN sub.SUM
                ELSE NULL
            END) AS clerk
    ,MAX(
            CASE
                WHEN sub.aggflg = '0' AND sub.JOB = 'SALESMAN' THEN sub.SUM
                ELSE NULL
            END) AS salesman
    ,MAX(
            CASE
                WHEN sub.aggflg = '0' AND sub.JOB = 'MANAGER' THEN sub.SUM
                ELSE NULL
            END) AS MANAGER
    ,MAX(
            CASE
                WHEN sub.aggflg = '0' AND sub.JOB = 'ANALYST' THEN sub.SUM
                ELSE NULL
            END) AS analyst
    ,MAX(
            CASE
                WHEN sub.aggflg = '0' AND sub.JOB = 'PRESIDENT' THEN sub.SUM
                ELSE NULL
            END) AS president
    ,MAX(
            CASE
                WHEN sub.aggflg = '1' AND sub.JOB IS NULL THEN sub.SUM
                ELSE NULL
            END) AS 部署合計
FROM
    (
        SELECT
            CASE
                WHEN GROUPING_ID(deptno,JOB) = '0' THEN '部署jobごと'
                WHEN GROUPING_ID(deptno,JOB) = '1' THEN '部署ごと'
                WHEN GROUPING_ID(deptno,JOB) = '2' THEN 'jobごと'
                WHEN GROUPING_ID(deptno,JOB) = '3' THEN '全体'
            END AS 集約単位
            ,GROUPING_ID(deptno,JOB) AS aggflg
            ,deptno
            ,JOB
            ,SUM(sal) AS SUM
        FROM
            emp
        GROUP BY
            GROUPING SETS (
                            (deptno,JOB)--部署job単位で集約
                            ,(deptno)--部署単位で集約
                            ,(JOB)--job単位で集約
                            ,()--全量で集約
                            )
    ) sub
WHERE
    sub.deptno IS NOT NULL
GROUP BY
    sub.deptno
UNION ALL
SELECT
    'job合計'
    ,MAX(
            CASE
                WHEN sub.aggflg = '2' AND sub.JOB = 'CLERK' THEN sub.SUM
                ELSE NULL
            END) AS clerk
    ,MAX(
            CASE
                WHEN sub.aggflg = '2' AND sub.JOB = 'SALESMAN' THEN sub.SUM
                ELSE NULL
            END) AS salesman
    ,MAX(
            CASE
                WHEN sub.aggflg = '2' AND sub.JOB = 'MANAGER' THEN sub.SUM
                ELSE NULL
            END) AS MANAGER
    ,MAX(
            CASE
                WHEN sub.aggflg = '2' AND sub.JOB = 'ANALYST' THEN sub.SUM
                ELSE NULL
            END) AS analyst
    ,MAX(
            CASE
                WHEN sub.aggflg = '2' AND sub.JOB = 'PRESIDENT' THEN sub.SUM
                ELSE NULL
            END) AS president
    ,MAX(
            CASE
                WHEN sub.aggflg = '3' AND sub.JOB IS NULL AND sub.deptno IS NULL THEN sub.SUM
                ELSE NULL
            END) AS 合計
FROM
    (
        SELECT
            CASE
                WHEN GROUPING_ID(deptno,JOB) = '0' THEN '部署jobごと'
                WHEN GROUPING_ID(deptno,JOB) = '1' THEN '部署ごと'
                WHEN GROUPING_ID(deptno,JOB) = '2' THEN 'jobごと'
                WHEN GROUPING_ID(deptno,JOB) = '3' THEN '全体'
            END AS 集約単位
            ,GROUPING_ID(deptno,JOB) AS aggflg
            ,deptno
            ,JOB
            ,SUM(sal) AS SUM
        FROM
            emp
        GROUP BY
            GROUPING SETS (
                            (deptno,JOB)--部署job単位で集約
                            ,(deptno)--部署単位で集約
                            ,(JOB)--job単位で集約
                            ,()--全量で集約
                            )
    ) sub
WHERE
    sub.aggflg IN ('2','3')
ORDER BY
    deptno
;

所感

unpivotしたらどうなるんだろうと思いました。
たぶん、rollup①と②の結果に帰着するのではと思います。

暗黙のグループ化があるので、すこし、なれていないunpivot。