テクノロジー：SQLの基礎
分析関数:データへのピボット・アクセス
著者：Melanie Caffrey
リレーショナル・データベースとSQLの基本に関するシリーズ記事のパート12
このシリーズ記事のパート11 "分析関数:ランキング、LEAD/LAG、レポート"（Oracle Magazine、2013年5/6月）では、パート10で始まった分析関数の説明の続編をお届けしました。上位N番までの問合せの結果を取得する、データの比較を評価する、グループ内の割合を計算するといったさまざまな操作が可能になる分析関数の例を示しました。この記事では、分析関数シリーズの最後の話題として、次の点を取り上げます。
- 列データを行データに変換する、あるいは行データを列データに変換するピボット問合せによる結果について新しい見解を得る方法
- インライン・ビューを導入して、WHERE句内で分析関数を使用する方法
このシリーズ記事の例を試すには、Oracle Databaseインスタンスにアクセスする必要があります。必要に応じて、お使いのオペレーティング・システムに対応したOracle Databaseエディションをダウンロードし、インストールしてください。筆者がインストールをお勧めするエディションは、Oracle Database, Express Edition 11g Release 2です。このOracle Databaseソフトウェアをインストールする場合は、データベースの作成と構成が可能なインストール・オプションを選択してください。サンプルのユーザー・アカウントと関連する新しいスキーマを含む新しいデータベースが作成されます（SQL_101は、このシリーズ記事の例で使用するユーザー・アカウントです。また、データベースの表やその他のオブジェクトが作成されるスキーマでもあります）。インストール・プロセスの実行中にスキーマのパスワードを指定するように求められたら、SYSおよびSYSTEMのパスワードを入力して確認し、そのパスワードを覚えておいてください。
最後に、このデータベース・ソフトウェアをゼロからインストールした場合でも、既存のOracle Databaseインスタンスにアクセスする場合でも、SQLスクリプトをダウンロードして解凍し、実行して、この記事の例で必要となるSQL_101スキーマ用の表を作成します（このスクリプトをテキスト・エディタで開き、実行方法の説明を確認してください）。
データを横向きに置く
よくある業務レポート要件として、レポートを読みやすくするために、列内のデータを垂直ではなく水平に表示するというものがあります。たとえば、リスト1とリスト2の結果セットを比較してみましょう。リスト1の問合せでは、すべての従業員を所属する部門IDと並べて表示し、部門と従業員名でソートします。リスト2で返されるデータはリスト1で返されるデータと同じですが、表示方法が異なります。
コード・リスト1：従業員のリストを取得し、部門と従業員名でソート
SQL> set feedback on
SQL> set lines 32000
SQL> select department_id, last_name, first_name
2 from employee
3 order by department_id, last_name, first_name;
DEPARTMENT_ID LAST_NAME FIRST_NAME
—————————————— ————————————— ———————————
10 Dovichi Lori
10 Eckhardt Emily
10 Friedli Roger
10 James Betsy
10 Michaels Matthew
10 Newton Donald
20 leblanc mark
20 peterson michael
30 Jeffrey Thomas
30 Wong Theresa
Newton Frances
コード・リスト2：従業員を部門と従業員名でソートし、1部門を1行で表示
SQL> select department_id,
2 LISTAGG(first_name||' '||last_name, ', ')
3 WITHIN GROUP
4 (order by last_name, first_name) employees
5 from employee
6 group by department_id
7 order by department_id;
DEPARTMENT_ID EMPLOYEES
————————————— —————————————————————————————————————————————————————————
10 Lori Dovichi, Emily Eckhardt, Roger Friedli, Betsy James,
Matthew Michaels, Donald Newton
20 mark leblanc, michael peterson
30 Thomas Jeffrey, Theresa Wong
Frances Newton
4 rows selected.
リスト2ではLISTAGG関数（Oracle Database 11gで導入された関数）を使用して、部門ごとにカンマ区切りの従業員リストを作成し、リスト1のような従来型の結果セットの表示をピボット（変換）しています。LISTAGGは、単一グループの集計関数、複数グループの集計関数、分析関数のいずれでも使用可能です。
LISTAGGを単一グループの集計関数として呼び出した場合は、他の単一グループの集計関数と同様に、WHERE句の条件を満たすすべての行に基づいて動作した上で、単一行の出力を返します。リスト2の例は、複数グループの集計関数としてのLISTAGG使用例です。GROUP BY句によって定義されたグループごとに行を返しています。
LISTAGG関数の構文は次のとおりです。
LISTAGG ( 列 | 式, デリミタ ) WITHIN GROUP (ORDER BY 列 | 式)
LISTAGGは、次のOVER句を追加した場合には分析関数として実行されます。
OVER (PARTITION BY 列 | 式)
集計対象の列または式とWITHIN GROUPキーワードを記述し、各WITHIN GROUPキーワードの直後にORDER BY句（グループ内でのソート方法）を記述することが、LISTAGGの3種類のユースケースすべてで必須です。
リスト3の問合せでは、LISTAGGを分析関数として使用します。この問合せでは、部門ごとに、金額が高い順に給与のリストを取得します。各SALARY値の横には、そのSALARY値を得た従業員名を姓/名のアルファベット順で表示します。さらに、現在の行で示す部門に所属するすべての従業員が、給与の高い順、姓/名のアルファベット順で返されます。
コード・リスト3：分析関数としてのLISTAGG関数の呼出し
SQL> select department_id, salary, first_name||’ ‘||last_name earned_by,
2 listagg(first_name||’ ‘||last_name, ‘, ‘)
3 within group
4 (order by salary desc nulls last, last_name, first_name)
5 over (partition by department_id) employees
6 from employee
7 order by department_id, salary desc nulls last, last_name, first_name;
DEPARTMENT_ID SALARY EARNED_BY EMPLOYEES
————————————— —————— —————————————— ———————————————————————————————
10 100000 Emily Eckhardt Emily Eckhardt, Donald Newton,
Matthew Michaels, Roger Friedli,
Betsy James, Lori Dovichi
10 70000 Matthew Michaels Emily Eckhardt, Donald Newton,
Matthew Michaels, Roger Friedli,
Betsy James, Lori Dovichi
10 60000 Roger Friedli Emily Eckhardt, Donald Newton,
Matthew Michaels, Roger Friedli,
Betsy James, Lori Dovichi
10 60000 Betsy James Emily Eckhardt, Donald Newton,
Matthew Michaels, Roger Friedli,
Betsy James, Lori Dovichi
10 Lori Dovichi Emily Eckhardt, Donald Newton,
Matthew Michaels, Roger Friedli,
Betsy James, Lori Dovichi
20 90000 michael peterson michael peterson, mark leblanc
20 65000 mark leblanc michael peterson, mark leblanc
30 300000 Thomas Jeffrey Thomas Jeffrey, Theresa Wong
30 70000 Theresa Wong Thomas Jeffrey, Theresa Wong
75000 Frances Newton Frances Newton
11 rows selected.
より少なく、より広い表示への変換
PIVOT句を使用すれば、行を列に変換して、データをクロス集計形式で表示できます。PIVOT句の構文は次のとおりです。
SELECT … FROM … PIVOT ( 集計 関数 列 | 式 ) FOR ピボット対象の列 | 式 IN (値1, … 値N) ) AS 別名
リスト4とリスト5の結果セットを比較しましょう。リスト4の問合せは、各部門の従業員給与総額のサマリーをクロス集計レポート形式で表示します。リスト5の問合せは、リスト4と同じ部門ごとの給与サマリーを返しますが、縦欄式に表示しており、ユーザーにとっては読みづらい可能性があります。
コード・リスト4：PIVOT関数を使用してクロス集計形式の結果を取得
SQL> select *
2 from (select department_id, salary
3 from employee) total_department_sals
4 PIVOT (SUM(salary)
5 FOR department_id IN (10 AS Accounting, 20 AS Payroll, 30 AS IT,
6 NULL AS Unassigned_Department));
ACCOUNTING PAYROLL IT UNASSIGNED_DEPARTMENT
—————————— ——————— —————————— —————————————————————
370000 155000 370000 75000
1 row selected.
コード・リスト5：従来の縦欄式表示による、部門ごとにグループ化された給与サマリー
SQL> select department_id, sum(salary)
2 from employee
3 group by department_id
4 order by department_id nulls last;
DEPARTMENT_ID SUM(SALARY)
————————————— ———————————
10 370000
20 155000
30 370000
75000
4 rows selected.
リスト6の問合せは、複数の列に基づいてピボットできることを示しています。この問合せの結果には、部門ごとの給与総額が表示されますが、特定の年に雇用された従業員のみのデータが含まれます。また、リスト7のように、複数の集計値に基づいてピボットして表示することもできます。リスト7の問合せでは、部門ごとの給与総額と最新の従業員雇用日を並べて取得しています。
コード・リスト6：特定の年に関する部門ごとの従業員給与総額を表示
SQL> select *
2 from (select department_id,
3 to_char(trunc(hire_date, 'YYYY'), 'YYYY') hire_date, salary
4 from employee)
5 PIVOT (SUM(salary)
6 FOR (department_id, hire_date) IN
7 ((10, '2007') AS Accounting_2007,
8 (20, '2008') AS Payroll_2008,
9 (30, '2010') AS IT_2010
10 )
11 );
ACCOUNTING_2007 PAYROLL_2008 IT_2010
——————————————— ———————————— ————————
190000 90000 370000
1 row selected.
コード・リスト7：複数の集計列に基づいてピボットして表示
SQL> select *
2 from (select department_id, hire_date, salary
3 from employee)
4 PIVOT (SUM(salary) AS sals,
5 MAX(hire_date) AS latest_hire
6 FOR department_id IN (10, 20, 30, NULL));
10_SALS 10_LATEST 20_SALS 20_LATEST 30_SALS 30_LATEST NULL_SALS NULL_LATE
——————— ————————— ——————— ————————— ——————— —————————— ————————— —————————
370000 07-JUL-11 155000 06-MAR-09 370000 27-FEB-10 75000 14-SEP-05
1 row selected.
複数の集計関数を使用する場合には、各集計関数に対して別名を指定することをお勧めします。出力される列ヘッダーはピボット値（またはピボットの別名）、アンダースコア、そして集計関数の別名（指定した場合）を連結したものになります。たとえば、リスト7の列名は、10_SALS、10_LATESTなどとなっています。部門ごとの最新の雇用日に関する列（10_LATESTなど）は、実際にはLATEST_HIREという別名を使用した列です。LATEST_HIREという別名の前に部門IDを付加した場合、本来は問合せで10_LATEST_HIREのような列が返されるはずです。
しかし、SQL*Plusでは、DATEデータ型の列に対して表示される列ヘッダーは、返される値のデフォルト書式よりも常に短くなります。HIRE_DATE列の書式はDD-MON-RRであるため、ヘッダーの先頭から9文字のみが表示されます。10_LATEST_HIREのようにヘッダー全体を表示するには、TO_CHARを使用して列に日付書式マスクを適用することを検討してください。
集計関数に対して別名を指定しない場合、リスト8の例のように、エラー・メッセージが表示されることがあります。リスト8では、いずれの集計関数にも別名が指定されていないため、PIVOT演算子でピボット値（例の場合はDEPARTMENT_ID値）の列ヘッダーをどちらに適用すべきかを判断できません。そのため、PIVOT演算子で単純にデフォルトの列ヘッダーを使用できず、"column ambiguously defined"というエラー・メッセージが表示されて問合せが失敗します。このエラーを回避するには、各集計関数に対して別名を作成します。PIVOT操作を利用する際には、デフォルトの列ヘッダーだけに頼らないようにしてください。
コード・リスト8："column ambiguously defined"エラーの発生
SQL> select *
2 from (select department_id, hire_date, salary
3 from employee)
4 PIVOT (SUM(salary),
5 MAX(hire_date)
6 FOR department_id IN (10, 20, 30, NULL));
select *
*
ERROR at line 1:
ORA-00918: column ambiguously defined
垂直的データの水平表示
レポートの行を列に変換する必要があるのと同様に、列を行に変換する必要がある場合もあります。これまで、LISTAGG関数を使用した方法を確認しました。そのほかにも、UNPIVOT演算子をこの目的で使用することができます。注意点として、UNPIVOT演算子は、PIVOT演算子によって実行される操作と逆の操作を実行するわけではありません。むしろ、ピボット済みとしてすでに保管されているデータに対して動作します。
リスト9のCREATE TABLE文を見てみましょう。この文は、リスト7と同様の問合せを使用して、ピボット後のデータを含む表を作成します。この後、リスト10のようにUNPIVOT演算子を使用して、このデータを問い合わせることができます。リスト10の問合せにより返される値と、リスト7の問合せにより返される値を比較してみてください。見てのとおり、値は同じですが表示方法が異なります。
コード・リスト9：ピボット後のデータを含む表の作成
SQL> CREATE TABLE pivoted_emp_data AS
2 select *
3 from (select department_id, hire_date, salary
4 from employee)
5 PIVOT (SUM(salary) sum_sals,
6 MAX(hire_date) latest_hire
7 FOR department_id IN (10 AS Acc, 20 AS Pay, 30 AS IT, NULL));
Table created.
コード・リスト10：UNPIVOT演算子を使用して行を列に変換
SQL> select hire_date, salary
2 from pivoted_emp_data
3 UNPIVOT INCLUDE NULLS
4 ((hire_date, salary)
5 FOR department_id IN (
6 (acc_latest_hire, acc_sum_sals) AS 'Accounting',
7 (pay_latest_hire, pay_sum_sals) AS 'Payroll',
8 (it_latest_hire, it_sum_sals) AS 'IT',
9 (null_latest_hire, null_sum_sals) AS 'Unassigned'
10 ))
11 order by hire_date, salary;
HIRE_DATE SALARY
—————————— ——————
14-SEP-05 75000
06-MAR-09 155000
27-FEB-10 370000
07-JUL-11 370000
4 rows selected.
リスト7の結果は長い1つのレコード（1行）で返され、HIRE_DATEとSALARYがそれぞれ組み合わされて、部門ごとにピボットされた上で、横並びで表示されました。一方、リスト10の問合せでは、これらの組合せのそれぞれが重複のない個別の行として返され、HIRE_DATE値とSALARY値が別々の列に表示されます。リスト10の問合せでは異なるデータ型の値のペアがアンピボットされて返されている点に注意してください。HIRE_DATEではDATEデータ型を、SALARYではNUMBERデータ型を使用します。そのため、これらの値のペアに対して使用する別名は、一重引用符で囲む必要があります。一重引用符で囲まなければ、リスト11に示すようなエラー・メッセージが表示されます。
コード・リスト11：異なるデータ型の値のペアに対する別名の使用
SQL> select hire_date, salary
2 from pivoted_emp_data
3 UNPIVOT INCLUDE NULLS
4 ((hire_date, salary)
5 FOR department_id IN (
6 (acc_latest_hire, acc_sum_sals) AS Accounting,
7 (pay_latest_hire, pay_sum_sals) AS Payroll,
8 (it_latest_hire, it_sum_sals) AS IT,
9 (null_latest_hire, null_sum_sals) AS Unassigned
10 ))
11 order by hire_date, salary;
(acc_latest_hire, acc_sum_sals) AS Accounting,
*
ERROR at line 6:
ORA-56901: non-constant expression is not allowed for pivot|unpivot values
分析的な条件を記述する状況とその方法
最後のORDER BY句を除けば、分析関数は、問合せで実行される最後の操作セットです。SELECT構文のリストとORDER BY句でしか記述できないため、WHERE句やHAVING句などの条件内で直接使用することはできません。分析関数を適用した結果に基づく結果セットから選択操作を行う必要がある場合は、インライン・ビューを使用できます。インライン・ビューとは、別のSELECT文のFROM句内にあるSELECT文のことです。インライン・ビューは、TABLE（別名FROM）句のように振る舞います。インライン・ビュー機能の例については、すでにこの記事のリスト4、6、7、8、9で確認しました。
コード・リスト12：インライン・ビューにより分析関数を条件として利用
SQL> select *
2 from (select department_id, last_name||', '||first_name, salary,
3 dense_rank() over (partition by department_id
4 order by salary desc nulls last) d_rank
5 from employee)
6 where d_rank < 3
7 order by department_id, salary desc nulls last;
DEPARTMENT_ID LAST_NAME||','||FIRST_NAME SALARY D_RANK
————————————— —————————————————————————————————————— —————— ——————
10 Eckhardt, Emily 100000 1
10 Newton, Donald 80000 2
20 peterson, michael 90000 1
20 leblanc, mark 65000 2
30 Jeffrey, Thomas 300000 1
30 Wong, Theresa 70000 2
Newton, Frances 75000 1
7 rows selected.
分析関数を使用して、部門ごとに上位給与取得者者2人を取得することにしましょう。その場合、リスト12に示すように、インライン・ビュー内に分析関数操作を配置して別名を付けることができます。リスト12のインライン・ビューに指定された別名はD_RANKです（DENSE_RANK分析関数の適用結果という意味で名付けられています）。インライン・ビュー内の問合せ（内部問合せ）が解決されるまでは、それを取り囲む問合せ（外部問合せ）で使用できません。インライン・ビューの処理が完了後、外部問合せの条件内でその結果を使用できます。リスト12の外部問合せ内の条件句は次のようになります。
WHERE d_rank < 3
良いパフォーマンスのために
分析関数は、レポート要件に対して、より洗練されたパフォーマンスの良いSQLの解を作成するのに役立ちますが、良いコードを記述することの代わりになるものではありません。保守しやすく、長期にわたって良いパフォーマンスを発揮するような良いSQLを、常に一貫して記述することを目標にすべきです。処理を容易にするSQLテクニックを誤用することはよくあります。正しく使用しなければ、SQLテクニックは稚拙な書き方になり、システムの阻害要因になるのです。
特に、データのソートや移動によりシステム・リソースを使い果たすこともよくあります（たとえば、リスト13の問合せには、可能性として3回のソート操作が含まれます）。このことにより、かならずしも分析関数の利用をやめるべきだということにはなりません。しかし、効率的で洗練され、保守しやすい解をもたらす問合せを記述するのと同じくらい簡単に、システムをダウンさせるような問合せを記述する可能性があるということに注意してください。分析関数の力を利用するには、責任も伴います。
コード・リスト13：システムのパフォーマンス問題を引き起こす可能性のある分析関数を含む問合せ
次のステップ
その他の記事 SQLの基礎、パート1～11
リレーショナル・データベースの 設計と概念に関する詳細の確認
Oracle Database Concepts 11g Release 2 (11.2)
Oracle Database SQL言語リファレンス11g リリース1（11.1）
Oracle Databaseデータ・ウェアハウス・ガイド11gリリース2（11.2）
ダウンロード この記事のサンプル・スクリプト
SQL> select first_name||' '||last_name, department_id, hire_date,
2 sum(salary) over (order by department_id,
3 first_name||' '||last_name) sum_dept_emp,
4 avg(salary) over (order by hire_date, department_id) avg_dept_hire_dt
5 from employee
6 order by department_id, hire_date, first_name||' '||last_name;
FIRST_NAME||''||LAST_NAME D...MENT_ID HIRE_DATE SUM_DEPT_EMP AVG_DEPT_HIRE_DT
————————————————————————— ——————————— ————————— ———————————— ————————————————
Emily Eckhardt 10 07-JUL-04 240000 100000
Donald Newton 10 24-SEP-06 140000 85000
Betsy James 10 16-MAY-07 60000 74166.6667
Matthew Michaels 10 16-MAY-07 310000 74166.6667
Roger Friedli 10 16-MAY-07 370000 74166.6667
Lori Dovichi 10 07-JUL-11 240000 97000
michael peterson 20 03-NOV-08 525000 76428.5714
mark leblanc 20 06-MAR-09 435000 75000
Thomas Jeffrey 30 27-FEB-10 895000 100000
Theresa Wong 30 27-FEB-10 595000 97000
Frances Newton 14-SEP-05 970000 87500
11 rows selected.
結論
この記事では、このシリーズのパート10とパート11で紹介した分析関数について最後の話題を取り上げました。LISTAGG、PIVOT、UNPIVOTを使用してデータの表示方法を操作する例を示しました。列を行に変換する方法や行を列に変換する方法、それらの表示方法の違いについて確認しました。個々のデータ項目を取得して、デリミタ付きリストとして返すことで、読みやすいレポートを作成する方法を学習しました。さらに、これらの関数を使用する場合に適用される固有の注意事項についても確認しました。
最後に重要なこととして、分析関数を使用する問合せは、気をつけなければ、すべてとは言わないまでも大量のシステム・リソースを消費するということを学習しました。すべてのケースで、分析関数を利用することで、同じ結果を取得するために複雑なSQLを記述しなければならない状況が大幅に減ることになるでしょう。しかし同時に、分析関数は、熱中しながらも警戒も怠らないようにする必要のあるツールなのです。詳しくは、Oracle Database SQL言語リファレンスとOracle Databaseデータ・ウェアハウス・ガイドを確認してください。
この記事で、SQLの基礎に関するシリーズ記事は終了です。これまで、リレーショナル・データベースの基本的な概念や多くのSQLコーディング構文を学習してきましたが、このシリーズ記事ではOracle SQLの豊富な機能の一部を垣間見たに過ぎません。今後も各種ドキュメントを確認し、Oracle Databaseの既存機能や新機能を試していってください。Oracle MagazineおよびSQLの基礎に関するシリーズ記事をお読みいただき、ありがとうございました。SQLの記述を続ける皆さんが、私と同じくらい楽しんでくれることを願います。
Melanie Caffreyはオラクルの上級開発マネージャーです。Expert PL/SQL Practices for Oracle Developers and DBAs（Apress、2011年）およびExpert Oracle Practices: Oracle Database Administration from the Oak Table（Apress、2010年）の共著者でもあります。