Oracle Database 11g：
DBAおよび開発者向けの主な機能
Oracle ACEディレクター、Arup Nanda氏
ピボットとアンピボット
シンプルなSQLを使用して、任意のリレーショナルテーブルからスプレッドシートタイプのクロス集計レポートに情報を表示して、クロス集計テーブルからリレーショナルテーブルにデータを格納します。
ピボット
ご存じのとおり、リレーショナルテーブルは表形式のため、列と値のペアで表示されます。CUSTOMERSという名前のテーブルの場合を検討します。
SQL> desc customers
Name Null? Type
----------------------------------------- -------- ---------------------------
CUST_ID NUMBER(10)
CUST_NAME VARCHAR2(20)
STATE_CODE VARCHAR2(2)
TIMES_PURCHASED NUMBER(3)
このテーブルが選択されている場合：
select cust_id, state_code, times_purchased
from customers
order by cust_id;
出力は次のとおりです。
CUST_ID STATE_CODE TIMES_PURCHASED
------- ---------- ---------------
1 CT 1
2 NY 10
3 NJ 2
4 NY 4
...
and so on ...
データは値の行として表されることに注意してください。: 各顧客のレコードには、顧客の居住地域と、顧客が店舗から購入した回数が表示されます。顧客がストアからさらに商品を購入すると、times_purchased列が更新されます。
次に、1回、2回、3回など、各地域から購入した顧客の数といった、各地域の購入頻度のレポートを作成する場合を考えてみます。通常のSQLでは、次のステートメントを発行できます。
select state_code, times_purchased, count(1) cnt
from customers
group by state_code, times_purchased;
出力は次のとおりです。
ST TIMES_PURCHASED CNT
-- --------------- ----------
CT 0 90
CT 1 165
CT 2 179
CT 3 173
CT 4 173
CT 5 152
...
and so on ...
これはご希望の情報ですが、少し読みづらいです。同じデータを表すのにもっと良い方法は、スプレッドシートのように、データを垂直に整理して、水平に状態を示すことができるクロス集計レポートを使用することです。
Times_purchased
CT NY NJ ...
and so on ...
1 0 1 0 ...
2 23 119 37 ...
3 17 45 1 ...
...
and so on ...
Oracle Database 11 g の前に、各値に対して何らかのデコード関数を使用してそれを行い、各個別の値を個別の列として書き込みます。ただし、この手法はあまりにも非直感的です。
幸い、適切に名前が付けられた新しい演算子を使用してクエリをクロス集計形式で表示するためのPIVOTと呼ばれる優れた新機能があります。pivot。クエリの記述方法は次のとおりです。
select * from (
select times_purchased, state_code
from customers t
)
pivot
(
count(state_code)
for state_code in ('NY','CT','NJ','FL','MO')
)
order by times_purchased
/
出力は次のとおりです。
. TIMES_PURCHASED 'NY' 'CT' 'NJ' 'FL' 'MO'
--------------- ---------- ---------- ---------- ---------- ----------
0 16601 90 0 0 0
1 33048 165 0 0 0
2 33151 179 0 0 0
3 32978 173 0 0 0
4 33109 173 0 1 0
... and so on ...
これは、pivotオペレーター力を示しています。state_codesは、列ではなくヘッダー行に表記されます。これは伝統的な表形式を絵で表したものです。
図1 従来の表形式の表示
クロス集計レポートでは、図2のように、Times Purchased列をヘッダー行に置き換えます。列が反時計回りに90度回転してヘッダー行になったように、列が行になります。この図形な回転にはピボットポイントが必要です。この場合、ピボットポイントはたまたまcount（state_code）式になります。
図2ピボット表示
この式はクエリの構文に含まれていなければなりません。
...
pivot
(
count(state_code)
for state_code in ('NY','CT','NJ','FL','MO')
)
...
「state_code ...」の2行目は、クエリをそれらの値のみに制限します。この行は必要なので、あいにく事前に可能な値を知っておく必要があります。この制限は、この記事の後半で説明するクエリのXML形式で緩和されています。
出力のヘッダー行に注意してください。
. TIMES_PURCHASED 'NY' 'CT' 'NJ' 'FL' 'MO'
--------------- ---------- ---------- ---------- ---------- ----------
列ヘッダーは、テーブル自体からのデータです。: 州コード。省略形は一目瞭然かもしれませんが、省略形の代わりに州名を表示したいとします（「CT」の代わりに「コネチカット」 ）?その場合、次に示すように、FOR句でクエリを少し調整する必要があります。
select * from (
select times_purchased as "Puchase Frequency", state_code
from customers t
)
pivot
(
count(state_code)
for state_code in ('NY' as "New York",'CT' "Connecticut",'NJ' "New Jersey",'FL' "Florida",'MO' as "Missouri")
)
order by 1
/
Puchase Frequency New York Connecticut New Jersey Florida Missouri
----------------- ---------- ----------- ---------- ---------- ----------
0 16601 90 0 0 0
1 33048 165 0 0 0
2 33151 179 0 0 0
3 32978 173 0 0 0
4 33109 173 0 1 0
...
and so on ...
FOR句には、列ヘッダーになる値のエイリアスを含めることができます。
アンピボット
物質には反物質があり、ピボットには「アンピボット」ですね?
ユーモアはさておき、ピボット操作の逆操作には真の必要性があります。以下に示すクロス集計レポートを表すスプレッドシートがあるとします。
| 購入頻度 | ニューヨーク州 | コネチカット州 | ニュージャージー | フロリダ州 | ミズーリ州 |
|---|---|---|---|---|---|
| 0 | 12 | 11 | 1 | 0 | 0 |
| 1 | 900 | 14 | 22 | 98 | 78 |
| 2 | 866 | 78 | 13 | 3 | 9 |
| ... | . |
次に、CUSTOMERSというリレーショナルテーブルにデータをロードします。
SQL> desc customers
Name Null? Type
----------------------------------------- -------- ---------------------------
CUST_ID NUMBER(10)
CUST_NAME VARCHAR2(20)
STATE_CODE VARCHAR2(2)
TIMES_PURCHASED NUMBER(3)
スプレッドシートデータは、リレーショナル形式に非正規化してから保存する必要があります。もちろん、DECODEを使用して複雑なSQL*:LoaderまたはSQLスクリプトを記述し、データをCUSTOMERSテーブルにロードできます。または、pivotアンピボットの逆の操作を使用でき、Oracle Database 11 g で可能なように、列を分割して行にできます。
例を使って説明した方が簡単かもしれません。まず、pivot操作を使ってクロス集計テーブルを作成します。
1 create table cust_matrix
2 as
3 select * from (
4 select times_purchased as "Puchase Frequency", state_code
5 from customers t
6 )
7 pivot
8 (
9 count(state_code)
10 for state_code in ('NY' as "New York",'CT' "Conn",'NJ' "New Jersey",'FL' "Florida",'MO' as "Missouri")
11 )
12* order by 1
データがテーブルにどのように格納されているかを確認できます。
SQL> select * from cust_matrix
2 /
Puchase Frequency New York Conn New Jersey Florida Missouri
----------------- ---------- ---------- ---------- ---------- ---------
1 33048 165 0 0 0
2 33151 179 0 0 0
3 32978 173 0 0 0
4 33109 173 0 1 0
... and so on ...
これは、データのスプレッドシートへの保存方法です。各州は表の列です（「ニューヨーク州」、「コネチカット州」など）。
SQL> desc cust_matrix
Name Null? Type
----------------------------------------- -------- ---------------------------
Puchase Frequency NUMBER(3)
New York NUMBER
Conn NUMBER
New Jersey NUMBER
Florida NUMBER
Missouri NUMBER
行に州コードとその州のカウントのみが表示されるように、テーブルを分割する必要があります。これは、次のunpivot操作によって行われます。
select *
from cust_matrix
unpivot
(
state_counts
for state_code in ("New York","Conn","New Jersey","Florida","Missouri")
)
order by "Puchase Frequency", state_code
/
出力は次のとおりです。
Puchase Frequency STATE_CODE STATE_COUNTS
----------------- ---------- ------------
1 Conn 165
1 Florida 0
1 Missouri 0
1 New Jersey 0
1 New York 33048
2 Conn 179
2 Florida 0
2 Missouri 0
...
and so on ...
各列の名前がSTATE_CODE列の値になっていることに注意してください。Oracleは、state_codeが列名であることがどのようにして分かりますか？クエリの次の句からわかりました。
州コード（「ニューヨーク州」、「コネチカット州」「ニュージャージー州」、「フロリダ州」、「ミズーリ州」）
ここでは、値の「ニューヨーク州」、「コネチカット州」 などは、state_codeと呼ばれるピボットを解除する新しい列の値であることを指定しました。元のデータの一部を見ます。
Purchase Frequency New York Conn New Jersey Florida Missouri
----------------- ---------- ---------- ---------- ---------- - ---------
1 33048 165 0 0 0
「ニューヨーク」列として、突然行の値になった場合、どの列の下に値33048をどう表示しますか?その質問は、上記のクエリのunpivot演算子にあるfor句のすぐ上の句によって回答されます。state_countsを指定したので、それは結果の出力で作成された新しい列の名前です。
Unpivotはpivotの反対のアクションかもしれませんが、前者が後者の処理を元に戻すことができるとは想定しないでください。たとえば、上記の例では、CUSTOMERSのテーブルでpivot操作を使って、新しいテーブルCUST_MATRIXを作成しました。後でテーブルCUST_MATRIXでunpivotを使いましたが、元のテーブルCUSTOMERSの詳細は返されませんでした。代わりに、リレーショナルテーブルにロードするために別の方法でクロス集計レポートが表示されました。したがって、unpivotはpivotが行ったことを元に戻すためのではなく、ピボットテーブルを作成して元のテーブルを削除する前に、慎重に検討する必要があります。
unpivotの非常に興味深い用途のいくつかは、前述に示した例のような通常の強力なデータ操作を超えます。Oracle ACE Amis TechnologiesのLucas Jellemaディレクターは、テスト目的で特定のデータの行を生成する方法を見せました。ここでは、少し変更した元のコードを使用して、英語のアルファベットの母音を生成します。
select value
from
(
(
select
'a' v1,
'e' v2,
'i' v3,
'o' v4,
'u' v5
from dual
)
unpivot
(
value
for value_type in
(v1,v2,v3,v4,v5)
)
)
/
出力は次のとおりです。
V
-
a
e
i
o
u
このモデルは、任意のタイプの行ジェネレータをカバーするように拡張できます。ルーカス、気の利いたトリックを見せてくれてありがとう。
SKUタイプ
上記の例では、有効なstate_codesの指定方法に注意してください。
for state_code in ('NY','CT','NJ','FL','MO')
この要件は、どの値がstate_code列に存在しているかわかっていることを前提としています。使用できる値がわからない場合は、どのようにクエリを作成しますか？
pivot操作には別の句があり、XMLは、ピボット出力をXMLとして作成でき、リテラル値の代わりに特別な句ANYを指定できます。例は次のとおりです。
select * from (
select times_purchased as "Purchase Frequency", state_code
from customers t
)
pivot xml
(
count(state_code)
for state_code in (any)
)
order by 1
/
出力はCLOBとして返されるため、クエリを実行する前に、LONGSIZEが大きな値に設定されていることを確認してください。
SQL> set long 99999
このクエリ（太字で表示）には、元pivot操作と比較して2つの異なる違いがあります。最初に、単にpivotの代わりに、句、ピボットxmlを指定しました。XMLで出力を作成します。次に、for句は、state_code値の長いリストの代わりに、（任意）のstate_codeを表します。XML表記では、ANYキーワードを使用でき、state_code値を入力する必要はありません。出力は次のとおりです。
Purchase Frequency STATE_CODE_XML
------------------ --------------------------------------------------
1 <PivotSet><item><column name = "STATE_CODE">CT</co
lumn><column name = "COUNT(STATE_CODE)">165</colum
n></item><item><column name = "STATE_CODE">NY</col
umn><column name = "COUNT(STATE_CODE)">33048</colu
mn></item></PivotSet>
2 <PivotSet><item><column name = "STATE_CODE">CT</co
lumn><column name = "COUNT(STATE_CODE)">179</colum
n></item><item><column name = "STATE_CODE">NY</col
umn><column name = "COUNT(STATE_CODE)">33151</colu
mn></item></PivotSet>
... and so on ...
ご覧の通り、STATE_CODE_XML列はXMLTYPEで、ルート要素は<PivotSet>となっています。各値は、名前と値の要素のペアとして表されます。任意のXMLパーサーで出力を使用して、より有用な出力を生成できます。
ANY句に加えて、サブクエリを作成できます。お好みの州のリストがあり、それらの州の行のみを選択するとします。お好みの州を、preferred_statesという新しいテーブルに配置しました。
SQL> create table preferred_states
2 (
3 state_code varchar2(2)
4 )
5 /
Table created.
SQL> insert into preferred_states values ('FL')
2> /
1 row created.
SQL> commit;
Commit complete.
pivot操作は次のようになります。
select * from (
select times_purchased as "Puchase Frequency", state_code
from customers t
)
pivot xml
(
count(state_code)
for state_code in (select state_code from preferred_states)
)
order by 1
/
for句のサブクエリは、何でもかまいません。たとえば、お好みの州は制限せずにすべてのレコードを選択する場合は、次のものをfor句として使用できます。
for state_code in (select distinct state_code from customers)
サブクエリは個別の値を返す必要があります。そうでない場合、クエリは失敗します。上記のDISTINCT句を指定したのはそのためです。
結論
PivotはSQL言語に非常に重要で実用的な機能を追加します。多くのデコード関数を含む複雑な非直感的コードを記述する代わりに、ピボット関数を使用して、任意のリレーショナルテーブルのクロス集計レポートを作成できます。同様に、クロス集計レポートを変換して、unpivot操作を使って通常のリレーショナルテーブルとして保存できます。Pivotは通常のテキストまたはXMLで出力を生成できます。後者のケースでは、ピボット操作で検索する必要がある値のドメインを指定する必要はありません。
ピボットおよびアンピボット操作の詳細については、Oracle Database 11g SQL言語リファレンスを参照してください。
シリーズTOCに戻る