まえがき
rollup, cube, grouping_id, grouping_setsを使用して
マトリクスをどこまで作成できるか挑戦したいと思い、実践します。
元データ
SCOTT/TIGERでやってみようと思います。
grouping_idについて
grouping_idの返却値は
引数に指定した列を集約単位として扱わない場合に１をたたせ、
それ以外を０にねかせた値を１０進数に変換した値です。
集約単位に応じて集約キーが表示されなくなる列を考えて書いています。
表示されなくなる列は１を立てます。
部署・jobごとに集約するのであれば、どちらも表示します。
０＊２＾１＋０＊２＾０⇒０＋０⇒０
部署ごとに集約するのであれば、
どのjobを表示するかは気にしていないので、jobは表示しません。
０＊２＾１＋１＊２＾０⇒０＊２＋１＊１⇒０＋１⇒１
jobごとに集約するのであれば、
どの部署を表示するかは気にしていないので、部署は表示しません。
１＊２＾１＋０＊２＾０⇒１＊２＋０＊１⇒２＋０⇒２
全体で集約するのであれば、
どの部署・どのjobを表示するかは気にしていないので、
jobも部署も表示しません。
１＊２＾１＋１＊２＾０⇒１＊２＋１＊１⇒２＋１⇒３
grouping_setsの場合
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)も１の値はありません。
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)も２の値はありません。
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、順序気にしません。。
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')で対象行を絞っています。
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。