3 Pivotの使い方
行持ちデータを列持ちデータに変換
create table PivotSample(
ID number(1),
Year number(4),
Val number(3),
primary key (ID,Year));
insert into PivotSample
select 1,2010, 1 from dual union all
select 1,2011, 2 from dual union all
select 1,2012, 6 from dual union all
select 2,2010, 70 from dual union all
select 2,2011, 80 from dual union all
select 3,2012, 90 from dual union all
select 4,2010,300 from dual union all
select 4,2012,500 from dual;
Pivotを使って、行持ちデータを列持ちデータに変換してみます。
-- Pivotのサンプル
select *
from PivotSample
Pivot (max(Val) for Year in(2010 as Agg2010,
2011 as Agg2011,
2012 as Agg2012))
order by ID;
出力結果
ID Agg2010 Agg2011 Agg2012
-- ------- ------- -------
1 1 2 6
2 70 80 null
3 null null 90
4 300 null 500
Pivotの構文は、下記のように理解しておくといいでしょう。
Pivot(集約関数 for 集約条件列 in(集約条件値1 as 集約後列名1,
集約条件値2 as 集約後列名2,
集約条件値3 as 集約後列名3))
Pivotでは、 集約関数で使用している列でなく、かつ集約条件列で使用している列でもない列で、 暗黙のgroup byが実行されます。
上記のselect文においては、 集約関数で使用している列は、Val列です。max(Val)といった形でVal列を使用しているからです。 そして、集約条件列で使用している列は、Year列です。 よって、集約関数で使用している列でなく、かつ集約条件列で使用している列でもない、 ID列で暗黙のgroup byが実行されます。
PivotのSQLのイメージは下記となります。 暗黙のgroup byによる赤線をイメージし、 for 集約条件列 (上記のselect文では、for Yearの部分) で黄緑線をイメージしてます。
下記のように、集約後列名の指定を省略することもできます。(集約条件値が列名になります)
-- 集約後列名の指定を省略
select *
from PivotSample
Pivot (max(Val) for Year in(2010,2011,2012))
order by ID;
出力結果
ID 2010 2011 2012
-- ---- ---- ----
1 1 2 6
2 70 80 null
3 null null 90
4 300 null 500
4 Pivotの代用法
集約関数とdecode関数の組み合わせ
-- Pivotの代用 (集約関数とdecode関数)
select ID,
max(decode(Year,2010,Val)) as Agg2010,
max(decode(Year,2011,Val)) as Agg2011,
max(decode(Year,2012,Val)) as Agg2012
from PivotSample
group by ID
order by ID;
出力結果
ID Agg2010 Agg2011 Agg2012
-- ------- ------- -------
1 1 2 6
2 70 80 null
3 null null 90
4 300 null 500
Pivotは、上記のように、集約関数とdecode関数を組み合わせることで代用できます。
decode関数で集約対象外のデータをnullに変換し、集約関数がnullを無視する性質を使ってます。
5 Pivotと、Pivotの代用法を比較
暗黙のgroup byはイメージしにくい
Pivotと、Pivotの代用法(集約関数とdecode関数)の比較結果として、両者は使い分けるのがいいと思われます。理由は下記です。
- 理由1 Pivotでは、暗黙のgroup byが実行され、暗黙のgroup byはイメージしにくい
- 理由2 Pivotで、不要な列を暗黙のgroup byの対象外にするには、インラインビューが必要
- 理由3 Pivotで計算式を使用するには、インラインビューが必要
理由1の暗黙のgroup byは、3 Pivotの使い方で説明したので、 理由2と3の例として、月ごとのValの合計を表示するselect文を比較します。
create table PivotCompare(
DayCol date primary key,
Val number(3),
bikou VarChar2(6));
insert into PivotCompare
select date '2012-01-05', 10,'bikou1' from dual union all
select date '2012-01-16', 20,'bikou2' from dual union all
select date '2012-01-28', 60,'bikou3' from dual union all
select date '2012-02-11',200,null from dual union all
select date '2012-02-22',300,null from dual union all
select date '2012-03-30',700,'bikou4' from dual;
-- Pivotの代用 (集約関数とdecode関数)
select
sum(decode(extract(month from DayCol),1,Val)) as sum1,
count(decode(extract(month from DayCol),1,Val)) as cnt1,
sum(decode(extract(month from DayCol),2,Val)) as sum2,
count(decode(extract(month from DayCol),2,Val)) as cnt2,
sum(decode(extract(month from DayCol),3,Val)) as sum3,
count(decode(extract(month from DayCol),3,Val)) as cnt3
from PivotCompare;
出力結果
sum1 cnt1 sum2 cnt2 sum3 cnt3
---- ---- ---- ---- ---- ----
90 3 500 2 700 1
-- Pivotを使用
select *
from (select extract(month from DayCol) as month,Val
from PivotCompare)
Pivot (sum(Val) as sum,
count(*) as cnt
for month in(1,2,3));
出力結果
1_SUM 1_CNT 2_SUM 2_CNT 3_SUM 3_CNT
----- ----- ----- ----- ----- -----
90 3 500 2 700 1
extract(month from DayCol)といった計算式を使ってPivotを行うには、インラインビューが必要となります。 下記のように、文法エラーになるからです。
-- 文法エラー ORA-01738: INキーワードがありません。
select *
from PivotCompare
Pivot (sum(Val) as sum,
count(*) as cnt
for extract(month from DayCol) in(1,2,3));
また、bikou列が、暗黙のgroup byのグループ化のキーの1つになることを防ぐためにも、 bikou列を除いたselect文を使ったインラインビューが必要となります。