X

A blog about Oracle Technology Network Japan

Try Oracle Cloud Platform For Free
  • February 18, 2012

図でイメージするOracle DatabaseのSQL全集　第8回 PivotとUnPivot

Guest Author

図でイメージするOracle DatabaseのSQL全集　indexページ▶▶

 

Oracle SQLの各機能をイメージ図を交えて解説

Oracle ACE
山岸 賢治（やまぎし けんじ）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ビット版)

 

1 PivotとUnPivotとは

 

select文での行列変換

PivotとUnPivotはOracle11gR1の新機能で、select文での行列変換を容易に行うことができます。

英和辞典のPivotの意味の中で、select文でのPivotの意味に近いものを選ぶと、
動詞では、「旋回する,回転する」。名詞では、「旋回軸,回転軸」となります。

select文での評価順序において、PivotとUnPivotはfrom句の一部として評価されます。

 

2 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

 

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の部分) で黄緑線をイメージしてます。

Pivotのイメージ

下記のように、集約後列名の指定を省略することもできます。(集約条件値が列名になります)

-- 集約後列名の指定を省略
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文を使ったインラインビューが必要となります。

 

6 Pivotのサンプル集

 

Pivotの雛形

-- 基本的な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

 

7 UnPivotの使い方

 

列持ちデータを行持ちデータに変換

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の下に移動させる黄緑線を引いてます。

UnPivotのイメージ

下記のように、元列の識別値を指定することもできます。 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

 

8 UnPivotの代用法

 

union allなどでUnPivotを代用

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

 

9 UnPivotと、UnPivotの代用法を比較

 

UnPivotの構文はシンプル

UnPivotと、UnPivotの代用法の比較結果として、 UnPivotの構文はシンプルで読みやすいので、UnPivotが使えるOracle11gR1以降では、UnPivotを使い、 UnPivotが使えないOracle10gなどでは、前述したUnPivotの代用法を使い分けるのがいいと思われます。

 

10 UnPivotのサンプル集

 

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パズルの運営者。

ページトップへ戻る▲

 

図でイメージするOracle DatabaseのSQL全集　indexページ▶▶

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.

Recent Content