該当する結果がありません

一致する検索結果がありませんでした。

お探しのものを見つけるために、以下の項目を試してみてください。

  • キーワード検索のスペルを確認してください。
  • 入力したキーワードの同義語を使用してください。たとえば、「ソフトウェア」の代わりに「アプリケーション」を試してみてください。
  • 下記に示すよく使用される検索語句のいずれかを試してみてください。
  • 新しい検索を開始してください。
急上昇中の質問

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文を使ったインラインビューが必要となります。