図でイメージするOracle DatabaseのSQL全集 indexページ▶▶
Oracle ACE
山岸 賢治(やまぎし けんじ)
SQLの初心者から上級者までを広く対象読者として、Oracle SQLの各機能の典型的な使用例を、学習効率が高いと思われる順序で、SQLのイメージ図を交えて解説します。
SQLをイメージつきで理解することで、素早くイメージからSQLを考えられるようになることを目標とします。
第1部 PivotとUnPivotとは
第2部 Pivotの使用例
第3部 UnPivotの使用例
今回は、下記のOracleのSQL文の評価順序においての、1番目のfrom句で行列変換を行うことができる、PivotとUnPivotの使用例と私のSQLのイメージを解説します。
1番目 | from句 |
2番目 | where句 (結合条件) |
3番目 | start with句 |
4番目 | connect by句 |
5番目 | where句 (行のフィルタ条件) |
6番目 | group by句 |
7番目 | having句 |
8番目 | model句 |
9番目 | select句 |
10番目 | union、minus、intersectなどの集合演算 |
11番目 | order by句 |
Oracle Database 11g Release 11.2.0.1.0 (Windows 32ビット版)
PivotとUnPivotはOracle11gR1の新機能で、select文での行列変換を容易に行うことができます。
英和辞典のPivotの意味の中で、select文でのPivotの意味に近いものを選ぶと、
動詞では、「旋回する,回転する」。名詞では、「旋回軸,回転軸」となります。
select文での評価順序において、PivotとUnPivotはfrom句の一部として評価されます。
PivotとUnPivotはfrom句の一部として評価されるので下記のようなselect文も実行できます。
PivotやUnPivotした結果に表別名を付けることもできます。
-- from句の一部として評価されている例1 select * from (select 1 as ColA,5 as ColB from dual) UnPivot(Vals1 for Cols1 in(ColA,ColA,ColA)) UnPivot(Vals2 for Cols2 in(ColB,ColB)) Pivot(count(*) for Vals1 in(1 as C1)) Pivot(count(*) for Vals2 in(5 as C2)) a Join dual b on a.C1 = 6;
出力結果 Cols1 Cols2 C1 C2 Dummy ----- ----- -- -- ----- COLA COLB 6 1 X
-- from句の一部として評価されている例2 with work(ColA,ColB) as( select 1,1 from dual union all select 2,2 from dual) select * from work Join dual on 1=1 UnPivot(Vals for Cols in(ColA,ColB));
出力結果 Dummy Cols Vals ----- ---- ---- X COLA 1 X COLB 1 X COLA 2 X COLB 2
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
-- 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を無視する性質を使ってます。
Pivotと、Pivotの代用法(集約関数とdecode関数)の比較結果として、両者は使い分けるのがいいと思われます。理由は下記です。
理由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文を使ったインラインビューが必要となります。
-- 基本的なPivot その1 with t(ID,Seq,Val) as( select 111,1,77 from dual union all select 111,2,66 from dual union all select 111,3,55 from dual union all select 222,1,44 from dual union all select 222,3,33 from dual union all select 333,2,22 from dual) select * from t Pivot(max(Val) for Seq in(1,2,3)) order by ID;
出力結果 ID 1 2 3 --- ---- ---- ---- 111 77 66 55 222 44 null 33 333 null 22 null
-- 基本的なPivot その2 with t(ID,Seq,Val) as( select 111,1,77 from dual union all select 111,2,66 from dual union all select 111,3,55 from dual union all select 222,1,44 from dual union all select 222,3,33 from dual union all select 333,2,22 from dual) select * from t Pivot(max(Val) for Seq in(1 as Seq1, 2 as Seq2, 3 as Seq3)) order by ID;
出力結果 ID Seq1 Seq2 Seq3 --- ---- ---- ---- 111 77 66 55 222 44 null 33 333 null 22 null
-- 複数列でPivot その1 with t(ID,Year,Month,Val) as( select 1,2012,1, 10 from dual union all select 1,2012,2, 20 from dual union all select 1,2012,3, 60 from dual union all select 2,2012,1,300 from dual union all select 2,2012,3,500 from dual union all select 3,2012,2,900 from dual) select * from t Pivot(max(Val) for (Year,Month) in ((2012,1) as Agg1, (2012,2) as Agg2, (2012,3) as Agg3));
出力結果 ID Agg1 Agg2 Agg3 -- ---- ---- ---- 1 10 20 60 2 300 null 500 3 null 900 null
-- 複数列でPivot その2 with t(ID,Year,Month,Val) as( select 1,2012,1, 10 from dual union all select 1,2012,1,700 from dual union all select 1,2012,2, 20 from dual union all select 1,2012,3, 60 from dual union all select 2,2012,1,300 from dual union all select 2,2012,1,999 from dual union all select 2,2012,3,500 from dual union all select 3,2012,2,900 from dual) select * from t Pivot(count(*) as cnt, max(Val) as max for (Year,Month) in ((2012,1) as Agg1, (2012,2) as Agg2, (2012,3) as Agg3));
出力結果 ID AGG1_CNT AGG1_MAX AGG2_CNT AGG2_MAX AGG3_CNT AGG3_MAX -- -------- -------- -------- -------- -------- -------- 1 2 700 1 20 1 60 2 2 999 0 null 1 500 3 0 null 1 900 0 null
create table UnPivotSample( ID number(1) primary key, Val1 number(2), Val2 number(2), Val3 number(2)); insert into UnPivotSample select 1,12, 11, 10 from dual union all select 3,30, 90,null from dual union all select 5,50,null,null from dual;
UnPivotを使って、列持ちデータを行持ちデータに変換してみます。
-- UnPivotのサンプル select ID,Vals,Cols from UnPivotSample UnPivot(Vals for Cols in(Val1,Val2,Val3));
出力結果 ID Vals Cols -- ---- ---- 1 12 VAL1 1 11 VAL2 1 10 VAL3 3 30 VAL1 3 90 VAL2 5 50 VAL1
上記の出力結果では、Valsがnullの行が出力されてないですね。 UnPivotは、デフォルトでExclude nullsなため、 UnPivot対象がnullの行も出力するには、Include nullsを指定する必要があります。
-- Include nullsを指定したUnPivot select ID,Vals,Cols from UnPivotSample UnPivot Include nulls (Vals for Cols in(Val1,Val2,Val3));
出力結果 ID Vals Cols -- ---- ---- 1 12 VAL1 1 11 VAL2 1 10 VAL3 3 30 VAL1 3 90 VAL2 3 null VAL3 5 50 VAL1 5 null VAL2 5 null VAL3
UnPivotの構文は、下記のように理解しておくといいでしょう。
UnPivot(列値を表示する列名 for 元列の識別値を表示する列名 in(元列1,元列2,元列3))
UnPivotのSQLのイメージは下記となります。 行ごとに区切る赤線を引いて、元列2と元列3を、元列1の下に移動させる黄緑線を引いてます。
下記のように、元列の識別値を指定することもできます。 Val1列の元列の識別値をMoto1,Val2列の元列の識別値をMoto2,Val3列の元列の識別値をMoto3としてみます。
-- 元列の識別値を指定 select ID,Vals,Cols from UnPivotSample UnPivot(Vals for Cols in(Val1 as 'Moto1', Val2 as 'Moto2', Val3 as 'Moto3'));
出力結果 ID Vals Cols -- ---- ----- 1 12 Moto1 1 11 Moto2 1 10 Moto3 3 30 Moto1 3 90 Moto2 5 50 Moto1
下記のように、元列の識別値として数値型を指定し、 order by句でのソートキーの指定に使うこともできます。
-- 数値型のソートキーを持たせたUnPivot select ID,Vals,SortKeys from UnPivotSample UnPivot(Vals for SortKeys in(Val1 as 1, Val2 as 2, Val3 as 3)) order by ID,SortKeys;
出力結果 ID Vals SortKeys -- ---- -------- 1 12 1 1 11 2 1 10 3 3 30 1 3 90 2 5 50 1
下記のように、元列の識別値として数値型と列名の両方を指定することもできます。 状況に応じて使い分けるといいでしょう。
-- 元列の識別値として数値型と列名の両方を指定 select ID,Vals,SortKeys,Moto from UnPivotSample UnPivot (Vals for (SortKeys,Moto) in(Val1 as (1,'Moto1'), Val2 as (2,'Moto2'), Val3 as (3,'Moto3'))) order by ID,SortKeys;
出力結果 ID Vals SortKeys Moto -- ---- -------- ----- 1 12 1 Moto1 1 11 2 Moto2 1 10 3 Moto3 3 30 1 Moto1 3 90 2 Moto2 5 50 1 Moto1
UnPivotの代用法としては、下記のようにunion allを使う方法があります。
-- UnPivotの代用 (union all) with tmp(ID,Vals,SortKeys) as( select ID,Val1,1 from UnPivotSample union all select ID,Val2,2 from UnPivotSample union all select ID,Val3,3 from UnPivotSample) select ID,Vals,SortKeys from tmp order by ID,SortKeys;
出力結果 ID Vals SortKeys -- ---- -------- 1 12 1 1 11 2 1 10 3 3 30 1 3 90 2 3 null 3 5 50 1 5 null 2 5 null 3
下記のように連番表とクロスジョインさせる方法も、UnPivotの代用法として使えます。
-- UnPivotの代用 (連番表とクロスジョイン) select a.ID, case b.Cnter when 1 then a.Val1 when 2 then a.Val2 when 3 then a.Val3 end as Vals,b.Cnter from UnPivotSample a, (select 1 as Cnter from dual union all select 2 from dual union all select 3 from dual) b order by a.ID,b.Cnter;
出力結果 ID Vals Cnter -- ---- ----- 1 12 1 1 11 2 1 10 3 3 30 1 3 90 2 3 null 3 5 50 1 5 null 2 5 null 3
下記のようにsys.odciNumberListも、UnPivotの代用法として使えます。 ただし、この代用法では、元々どの列の値だったかが分からなくなります。 例えば、下記のselect文では、各Vals列が元々は、Val1列,Val2列,Val3列のどれだったかが分からないです。 そのため、order by句でソート順序を明示できなくなってしまいます。
-- UnPivotの代用 (sys.odciNumberList) select ID,column_value as Vals from UnPivotSample,table(sys.odciNumberList(Val1,Val2,Val3));
出力結果 ID Vals -- ---- 1 12 1 11 1 10 3 30 3 90 3 null 5 50 5 null 5 null
UnPivotと、UnPivotの代用法の比較結果として、 UnPivotの構文はシンプルで読みやすいので、UnPivotが使えるOracle11gR1以降では、UnPivotを使い、 UnPivotが使えないOracle10gなどでは、前述したUnPivotの代用法を使い分けるのがいいと思われます。
-- 基本的なUnPivot その1 with t(ID,Val1,Val2) as( select 5,10,90 from dual union all select 6,20,80 from dual union all select 7,30,70 from dual) select * from t UnPivot(Vals for Cols in(Val1,Val2));
出力結果 ID Cols Vals -- ---- ---- 5 VAL1 10 5 VAL2 90 6 VAL1 20 6 VAL2 80 7 VAL1 30 7 VAL2 70
-- 基本的なUnPivot その2 with t(ID,Val1,Val2) as( select 5,10,90 from dual union all select 6,20,80 from dual union all select 7,30,70 from dual) select * from t UnPivot(Vals for SortKeys in(Val1 as 1, Val2 as 2)) order by ID,SortKeys;
出力結果 ID SortKeys Vals -- -------- ---- 5 1 10 5 2 90 6 1 20 6 2 80 7 1 30 7 2 70
-- 基本的なUnPivot その3 with t(ID,Val1,Val2) as( select 5,10,90 from dual union all select 6,20,80 from dual union all select 7,30,70 from dual) select * from t UnPivot(Vals for (SortKeys,Moto) in(Val1 as (1,'Moto1'), Val2 as (2,'Moto2'))) order by ID,SortKeys;
出力結果 ID SortKeys Moto Vals -- -------- ----- ---- 5 1 Moto1 10 5 2 Moto2 90 6 1 Moto1 20 6 2 Moto2 80 7 1 Moto1 30 7 2 Moto2 70
-- 複数列でUnPivot その1 with t(ID,Val1,Name1,Val2,Name2) as( select 1,11,'AA',66,'FF' from dual union all select 2,22,'BB',77,'GG' from dual union all select 3,33,'CC',88,'HH' from dual union all select 4,44,'DD',99,'II' from dual union all select 5,55,'EE',20,'JJ' from dual) select * from t UnPivot((Vals,Names) for Cols in((Val1,Name1) as 'V1N1', (Val2,Name2) as 'V2N2'));
出力結果 ID Cols Vals Names -- ---- ---- ----- 1 V1N1 11 AA 1 V2N2 66 FF 2 V1N1 22 BB 2 V2N2 77 GG 3 V1N1 33 CC 3 V2N2 88 HH 4 V1N1 44 DD 4 V2N2 99 II 5 V1N1 55 EE 5 V2N2 20 JJ
-- 複数列でUnPivot その2 with t(ID,Val1,Name1,Val2,Name2) as( select 1,11,'AA',66,'FF' from dual union all select 2,22,'BB',77,'GG' from dual union all select 3,33,'CC',88,'HH' from dual union all select 4,44,'DD',99,'II' from dual union all select 5,55,'EE',20,'JJ' from dual) select * from t UnPivot((Vals,Names) for (Col1,Col2) in((Val1,Name1) as ('V1','N1'), (Val2,Name2) as ('V2','N2')));
出力結果 ID Col1 Col2 Vals Names -- ---- ---- ---- ----- 1 V1 N1 11 AA 1 V2 N2 66 FF 2 V1 N1 22 BB 2 V2 N2 77 GG 3 V1 N1 33 CC 3 V2 N2 88 HH 4 V1 N1 44 DD 4 V2 N2 99 II 5 V1 N1 55 EE 5 V2 N2 20 JJ
-- UnPivotしてPivot with t(ID,Val1,Val2,Val3,Val4) as( select 'Sat',10,15,20,25 from dual union all select 'Sun',30,35,40,45 from dual union all select 'Mon',50,55,60,65 from dual) select * from t UnPivot(Vals for Cols in(Val1,Val2,Val3,Val4)) Pivot (max(Vals) for ID in('Sat' as Sat,'Sun' as Sun,'Mon' as Mon)) order by Cols;
出力結果 Cols Sat Sun Mon ---- --- --- --- VAL1 10 30 50 VAL2 15 35 55 VAL3 20 40 60 VAL4 25 45 65
マニュアル --- PivotおよびUnPivotの使用例 マニュアル --- pivot_clauseとunpivot_clause マニュアル --- ピボット操作 OracleSQLパズル 8-21 sys.odciVarchar2Listとsys.odciNumberList OracleSQLパズル 10-278 unpivotとpivot OracleSQLパズル 10-284 UnPivotして連番付与
"図でイメージするOracle DatabaseのSQL全集" インデックスに戻る
Copyright © 2012, Oracle Corporation Japan. All rights reserved. 無断転載を禁ず |
この文書はあくまでも参考資料であり、掲載されている情報は予告なしに変更されることがあります。日本オラクル社は本書の内容に関していかなる保証もいたしません。また、本書の内容に関連したいかなる損害についても責任を負いかねます。 Oracleは米国Oracle Corporationの登録商標です。文中に参照されている各製品名及びサービス名は米国Oracle Corporationの商標または登録商標です。その他の製品名及びサービス名はそれぞれの所有者の商標または登録商標の可能性があります。 |
山岸 賢治(やまぎし けんじ)
Oracle ACEの1人。
OracleSQLパズルの運営者。