リレーショナル・データベースとSQLの基本に関するシリーズ記事のパート10
このシリーズ記事のパート9 "合計、平均、その他のグループ化されたデータについて"（Oracle Magazine、2013年1/2月）では、よく使用されるSQL集計関数とGROUP BY句、HAVING句を紹介し、よりわかりやすい結果を得るために、それらを利用して単一行の結果セット・データやグループ化された結果セット・データを操作する方法を説明しました。集計関数について考察した後は必然的に、データの集計やその他の固有ビューを利用する高度なSQL操作の話題に移ることができます。この記事より3回にわたって、よく使用される分析関数および関連する句を紹介していきます。分析関数は、複数行に基づいて動作するだけではありません。少し挙げるだけでも、データのランキング作成、中間結果（ランニング・トータル）の計算、異なる期間での変化の識別といった操作も実行できます。これらの機能を利用すれば、レポート生成の目的で業務上の疑問に回答するような問合せを容易に作成できるようになります。
このシリーズ記事の例を試すには、Oracle Databaseインスタンスにアクセスする必要があります。必要に応じて、お使いのオペレーティング・システムに対応したOracle Databaseエディションをダウンロードし、インストールしてください。筆者がインストールをお勧めするエディションは、Oracle Database, Express Edition 11g Release 2です。このOracle Databaseソフトウェアをインストールする場合は、データベースの作成と構成が可能なインストール・オプションを選択してください。サンプルのユーザー・アカウントと関連する新しいスキーマを含む新しいデータベースが作成されます（SQL_101は、このシリーズ記事の例で使用するユーザー・アカウントです。また、データベースの表やその他のオブジェクトが作成されるスキーマでもあります）。インストール・プロセスの実行中にスキーマのパスワードを指定するように求められたら、SYSおよびSYSTEMのパスワードを入力して確認し、そのパスワードを覚えておいてください。
最後に、このデータベース・ソフトウェアをゼロからインストールした場合でも、既存のOracle Databaseインスタンスにアクセスする場合でも、SQLスクリプトをダウンロードして解凍し、実行して、この記事の例で必要となるSQL_101スキーマ用の表を作成します（このスクリプトをテキスト・エディタで開き、実行方法の説明を確認してください）。
最終結果の向上
標準SQLを利用するだけでも、データに関するほとんどの疑問に回答できます。しかし、"従業員の給与の値を1行ずつ加算していった場合の中間結果は？"といった質問に回答するような純粋なSQL問合せは記述しづらく、長期的に見ればパフォーマンスが良くない場合もあります。分析関数はSQLを拡張する関数であり、そのような操作を高速化し、コーディングしやすくします。
リスト1の問合せは、SUM分析関数の使用例を示しています。この問合せの結果として、すべての従業員について、各従業員の給与の値とともに、給与の累計が表示されます。
コード・リスト1：すべての従業員について給与の累計を1行ずつ取得
SQL> set feedback on
SQL> set lines 32000
SQL> select last_name, first_name, salary,
2 SUM (salary)
3 OVER (ORDER BY last_name, first_name) running_total
4 from employee
5 order by last_name, first_name;
LAST_NAME FIRST_NAME SALARY RUNNING_TOTAL
————————— ——————————— ————————————————————————————— —————————————
Dovichi Lori
Eckhardt Emily 100000 100000
Friedli Roger 60000 160000
James Betsy 60000 220000
Jeffrey Thomas 300000 520000
Michaels Matthew 70000 590000
Newton Donald 80000 670000
Newton Frances 75000 745000
Wong Theresa 70000 815000
leblanc mark 65000 880000
peterson michael 90000 970000
11 rows selected. この累計は、問合せの次の行により得られます。
SUM (salary)
OVER (ORDER BY last_name, first_name) running_total 分析関数の構造
分析関数の構文を習得すれば、分析関数を利用した効率的な問合せ処理のほぼ大半を終えたと言えます。リスト1における問合せの分析関連行の構文は次のとおりです。
関数名( 列 | 式,列 | 式,...)
OVER
( ORDER BY句 ) リスト1の関数名はSUMです。SUM関数の引数はSALARY列です（ただし、引数は式の場合もありあます）。OVER句は、この関数コールが（集計関数ではなく）分析関数であることを示すものです。ORDER BY句には、この分析関数の実行の"対象となる（OVER）"データを指定します。
スカラー副問合せについては、今後の記事で取り上げます。この記事では、リスト1の結果を得るために、スカラー副問合せという方法も利用できるということを知っておけば十分でしょう。ただし、スカラー副問合せはパフォーマンスが非常に悪く、リスト1の問合せの分析問合せ行と比較して、構文もより難しくなります。
コード・リスト2：部門ごとに、累計の給与を1行ずつ取得
SQL> select last_name, first_name, department_id, salary,
2 SUM (salary)
3 OVER (PARTITION BY department_id ORDER BY last_name, first_name) department_total
4 from employee
5 order by department_id, last_name, first_name;
LAST_NAME FIRST_NAME DEPARTMENT_ID SALARY DEPARTMENT_TOTAL
————————— —————————— ———————————————— ————————— —————————————————
Dovichi Lori 10
Eckhardt Emily 10 100000 100000
Friedli Roger 10 60000 160000
James Betsy 10 60000 220000
Michaels Matthew 10 70000 290000
Newton Donald 10 80000 370000
leblanc mark 20 65000 65000
peterson michael 20 90000 155000
Jeffrey Thomas 30 300000 300000
Wong Theresa 30 70000 370000
Newton Frances 75000 75000
11 rows selected. リスト2の問合せは、各部門内で従業員行のSALARY値の累計を出しています。PARTITION句によって、分析関数がそれぞれの部門グループ（つまりパーティション）に対して個別に適用されるようになります。見てのとおり、部門が10から20へ、20から30へ、さらに30から部門IDのない従業員レコードへと変わった後、累計がリセットされています。PARTITION句を含む分析関数の構文は、リスト1の例で使用した構文から、さらに次のように拡張されます。
関数名( 引数,引数,… )
OVER
( PARTITION句 ORDER BY句 ) 別々のORDER
リスト1およびリスト2の問合せでは、返される行が従業員の姓、名でソートされます。これに対して、リスト3の問合せでは、分析関数計算に適した、少し異なる並べ替え基準が使用されます。
コード・リスト3：SALARY値に基づいた各行の計算
SQL> select last_name, first_name, department_id, salary,
2 SUM (salary)
3 OVER (PARTITION BY department_id ORDER BY salary) department_total
4 from employee
5 order by department_id, salary, last_name, first_name;
LAST_NAME FIRST_NAME DEPARTMENT_ID SALARY DEPARTMENT_TOTAL
————————— ——————————— ————————————— ————————— ————————————————
Friedli Roger 10 60000 120000
James Betsy 10 60000 120000
Michaels Matthew 10 70000 190000
Newton Donald 10 80000 270000
Eckhardt Emily 10 100000 370000
Dovichi Lori 10 370000
leblanc mark 20 65000 65000
peterson michael 20 90000 155000
Wong Theresa 30 70000 70000
Jeffrey Thomas 30 300000 370000
Newton Frances 75000 75000
11 rows selected. リスト3の分析関数は、部門別の給与の合計値を計算し、各パーティションで給与の昇順にソートして、SALARY値がNULLの場合は最後に評価しています。見てのとおり、Lori Dovichiのレコード（SALARY値がNULLである唯一のレコード）では、DEPARTMENT_TOTALの値が、同じ部門内でSALARY値が最大であるレコード（Emily Eckhardt）と同じになっています。
分析関数のORDER BY句は、分析関数を含む問合せ全体のORDER BY句とは独立して動作します。これら2種類のORDER BY句は、同じ列または式のリストを同じ順で使用しない限り、ほとんど、あるいはまったく関係しません。たとえば、リスト4では、返されるデータは（リスト1やリスト2の結果セットと同様に）部門/姓/名の順で表示されますが、DEPARTMENT_TOTAL式で返される値は、リスト3で返されるその値と一致しています。さらに、リスト3とリスト4ではBetsy JamesとLori Dovichiの順序が異なりますが、それぞれの部門合計の計算値は同じです。
コード・リスト4：リスト3の問合せとは異なるデータ・ソート方法
SQL> select last_name, first_name, department_id, salary,
2 SUM (salary)
3 OVER (PARTITION BY department_id ORDER BY salary) department_total
4 from employee
5 order by department_id, last_name, first_name;
LAST_NAME FIRST_NAME DEPARTMENT_ID SALARY DEPARTMENT_TOTAL
————————— ——————————— ————————————— ————————— ————————————————
Dovichi Lori 10 370000
Eckhardt Emily 10 100000 370000
Friedli Roger 10 60000 120000
James Betsy 10 60000 120000
Michaels Matthew 10 70000 190000
Newton Donald 10 80000 270000
leblanc mark 20 65000 65000
peterson michael 20 90000 155000
Jeffrey Thomas 30 300000 370000
Wong Theresa 30 70000 70000
Newton Frances 75000 75000
11 rows selected. ウィンドウの選択
分析関数には、ウィンドウ句を含めることもできます（含めなくてもかまいません）。ウィンドウ句とは、分析関数計算で評価される特定のパーティション内で、行のグループ（ウィンドウ）を定義するための一連のパラメータまたはキーワードです。リスト1の問合せでは、デフォルトのウィンドウ句が使用されます。これは、ORDER BY句が使用されているためです。ORDER BY句では、その他のウィンドウ句のパラメータがない場合に、デフォルトのウィンドウ句であるRANGE UNBOUNDED PRECEDINGが効果的に追加されます。この句は、"現在のパーティション内の現在の行およびそれ以前の行を、計算で使用する行とする"という意味になります。ORDER BY句がPARTITION句とともに使用されない場合、分析関数で使用される行セット全体が、デフォルトでの現在のパーティションとなります。
リスト3とリスト4の問合せにはPARTITION句がありますが、ウィンドウ句パラメータは指定されていません。計算結果では、Betsy JamesとRoger FriedliのDEPARTMENT_TOTALの値が同一です。ウィンドウ句パラメータがないと、問合せの分析関数がそのパーティション内で特定の列または式に基づいてデータを並べ替え、2つ以上の行についてその値が同じ場合に、分析関数がそれぞれの行に適用されるため同じ結果が返されます。分析関数で、評価すべき順序を確定できないためです。
リスト5の問合せでは、ROWS 2 PRECEDINGウィンドウ句を使用することで、現在の行のSALARY値を、直前の2行のSALARY値のみと合算しています。Matthew Michaelsの直前の従業員であるBetsy JamesのDEPARTMENT_TOTAL値は220000ですが、Matthew MichaelsのDEPARTMENT_TOTAL値は190000です。これは、Matthew MichaelsのSALARY値の70000が、直前の2行（Betsy JamesとRoger Friedliの行）のSALARY値のみと合算されているからです。
コード・リスト5：ROWSウィンドウ句の追加
SQL> select last_name, first_name, department_id, salary,
2 SUM (salary)
3 OVER (PARTITION BY department_id ORDER BY last_name, first_name
4 ROWS 2 PRECEDING) department_total
5 from employee
6 order by department_id, last_name, first_name;
LAST_NAME FIRST_NAME DEPARTMENT_ID SALARY DEPARTMENT_TOTAL
————————— ——————————— ————————————— ————————— ————————————————
Dovichi Lori 10
Eckhardt Emily 10 100000 100000
Friedli Roger 10 60000 160000
James Betsy 10 60000 220000
Michaels Matthew 10 70000 190000
Newton Donald 10 80000 210000
leblanc mark 20 65000 65000
peterson michael 20 90000 155000
Jeffrey Thomas 30 300000 300000
Wong Theresa 30 70000 370000
Newton Frances 75000 75000
11 rows selected. 分析関数に対してパラメータ付きのウィンドウ句を追加する場合の構文は次のようになります。
関数名( 引数,引数,… )
OVER
( PARTITION句 ORDER BY句 ウィンドウ句) データへの複数のウィンドウの適用
ウィンドウ句は、渡すパラメータに応じて、データのスライド・ビューまたはアンカー・ビューのいずれかを生成します。ORDER BY句のみを使用した問合せ（リスト1、2、3、4の問合せなど）は、データのアンカー・ビューを生成します。このビューはパーティションの1行目（最上部）から始まり、現在処理中の行で終わります。リスト5の問合せ結果は、データのスライド・ビューです。各行のDEPARTMENT_TOTAL値が、各パーティション内のデータのソート（並べ替え）方法によって変化するからです。
リスト5は、ウィンドウ句へのパラメータ入力としてROWS句を使用した例です。また、RANGE句を使用してデータのスライド・ビューを作成することもできます。ROWS句とは異なり、RANGEウィンドウ句は、データ型が数値または日付である列や式を含むORDER BY句でのみ使用できます。このようなデータ型の要件があるのは、この句が、現在の行の特定範囲内にあるすべての行に基づいて動作するからです。各パーティション内でデータ並べ替えの基準となる列や式の値が、現在の行を始点とした（数値単位または日付単位での）指定範囲内に収まります。
コード・リスト6：雇用日を基準としたパーティションのソートとRANGEウィンドウ句の使用
SQL> select last_name, first_name, department_id, hire_date, salary,
2 SUM (salary)
3 OVER (PARTITION BY department_id ORDER BY hire_date
4 RANGE 90 PRECEDING) department_total
5 from employee
6 order by department_id, hire_date;
LAST_NAME FIRST_NAME DEPARTMENT_ID HIRE_DATE SALARY DEPARTMENT_TOTAL
————————— —————————— —————————————————— ————————— —————— ————————————————
Eckhardt Emily 10 07-JUL-04 100000 100000
Newton Donald 10 24-SEP-06 80000 80000
James Betsy 10 16-MAY-07 60000 190000
Friedli Roger 10 16-MAY-07 60000 190000
Michaels Matthew 10 16-MAY-07 70000 190000
Dovichi Lori 10 07-JUL-11
peterson michael 20 03-NOV-08 90000 90000
leblanc mark 20 06-MAR-09 65000 65000
Jeffrey Thomas 30 27-FEB-10 300000 300000
Wong Theresa 30 27-FEB-10 70000 370000
Newton Frances 14-SEP-05 75000 75000
11 rows selected. リスト6の問合せでは、RANGE句の動作例を示しています。この問合せのパーティションはHIRE_DATEでソートされます。この後、この問合せでは次のウィンドウ句を指定しています。
RANGE 90 PRECEDING この行は、"現在の行のSALARY値を、現在の行のHIRE_DATE値からさかのぼって90日以内のHIRE_DATE値を持つ、前にあるすべての行のSALARY値と合算する"という意味になります。部門10では、DEPARTMENT_TOTAL値とSALARY値が異なる行が3行しかありません。この3行の従業員はみな同じ日に雇用されており、SALARY合算に必要となる日付値の範囲に収まっています。
また、部門30では、2人の従業員が同じ日に雇用されていますが、DEPARTMENT_TOTAL値とSALARY値が異なるのは1行だけです。この原因は、RANGE句のPRECEDINGキーワードにあります。このキーワードは、"現在の行よりも前にある行を探してから、ソート対象のHIRE_DATE単位が現在の行のHIRE_DATEの範囲内に収まるかを判断する"という意味になります。部門30にThomas Jeffreyよりも前の行はないため、この従業員のDEPARTMENT_TOTAL値の結果は変更されず、表示されるSALARY値と同じになるのです。
リスト7の問合せでは、データ型が日付または数値である列や式のみを使用することがいかに重要かを示しています。この問合せでは、従業員の姓と名で各パーティションをソートしようとしています。RANGEウィンドウ句で、適切な値の範囲を判断できるのは、数値または日付の範囲を使用する場合に限られます。テキストや文字列の範囲は使用できません。そのため、この問合せにより適切な範囲を判断できず、エラーが発生します。
次のステップ
その他の記事 SQLの基礎、パート1～9
リレーショナル・データベースの
設計と概念に関する詳細の確認
Oracle Database Concepts 11g Release 2 (11.2)
Oracle Database SQL言語リファレンス11g リリース1（11.1）
Oracle Databaseデータ・ウェアハウス・ガイド11gリリース2（11.2）
Oracle SQL Developerユーザーズ・ガイド リリース3.1
ダウンロード この記事のサンプル・スクリプト
コード・リスト7：正しくないデータ型を使用するRANGEウィンドウ句
SQL> select last_name, first_name, department_id, hire_date, salary,
2 SUM (salary)
3 OVER (PARTITION BY department_id ORDER BY last_name, first_name
4 RANGE 90 PRECEDING) department_total
5 from employee
6 order by department_id, hire_date;
SUM (salary)
*
ERROR at line 2:
ORA-30486: invalid window aggregation group in the window specificationまた、問合せの分析関数でRANGEウィンドウ句を使用している場合、ORDER BY句には列または式を1つのみ使用できます。つまり、範囲は1次元的です。これらの制約はROWSウィンドウ句には適用されません。ROWS句は任意のデータ型に適用でき、ORDER BY句で使用できる列や式が1つに制限されることもありません。
視点の絞り込み
もっとも基本的な形式のウィンドウは、相互に排他的な3種類の方法のいずれかで指定できます。表1に、ウィンドウ句のROWSまたはRANGEに渡すことのできるパラメータのタイプを示します。
| ウィンドウ句のパラメータ | 説明 |
|---|---|
| 現在の行 | ウィンドウは現在処理中の行で始まり、その行で終わります。 |
| UNBOUNDED PRECEDING | ウィンドウは現在のパーティションの1行目で始まり、現在処理中の行で終わります。 |
| 数値式 PRECEDING | ROWS句 – ウィンドウは現在の行の前にある数値式の行で始まり、現在処理中の行で終わります。 RANGE句 – ウィンドウは、ORDER BY値が現在の行よりも小さい（または現在の行よりも前にある）数値式の行で始まり、現在処理中の行で終わります。 |
表1：ウィンドウ句のパラメータ
この記事でこれまで紹介したウィンドウはすべて、現在の行で終わり、計算時には前の行または範囲の値を使用するものです。一方、BETWEEN演算子を使用して、結果セット内の中間に現在の行が収まるようなウィンドウを指定することもできます。リスト8の問合せでは、ROWS句またはRANGE句で、ウィンドウが前の行の値で始まり現在処理中の行で終わることを指定するのに加えて、FOLLOWINGパラメータも使用して、現在処理中の行の後の行を探し、それらの行の値に基づいて評価できます。
コード・リスト8：BETWEENパラメータとFOLLOWINGパラメータを使用したRANGEウィンドウ句を含む問合せ
SQL> select last_name, first_name, department_id, hire_date, salary,
2 SUM (salary)
3 OVER (PARTITION BY department_id ORDER BY hire_date
4 RANGE BETWEEN 365 PRECEDING AND 365 FOLLOWING) department_total
5 from employee
6 order by department_id, hire_date;
LAST_NAME FIRST_NAME DEPARTMENT_ID HIRE_DATE SALARY DEPARTMENT_TOTAL
————————— ———————————— ————————————————— —————————— ———————— ————————————————
Eckhardt Emily 10 07-JUL-04 100000 100000
Newton Donald 10 24-SEP-06 80000 270000
James Betsy 10 16-MAY-07 60000 270000
Friedli Roger 10 16-MAY-07 60000 270000
Michaels Matthew 10 16-MAY-07 70000 270000
Dovichi Lori 10 07-JUL-11
peterson michael 20 03-NOV-08 90000 155000
leblanc mark 20 06-MAR-09 65000 155000
Jeffrey Thomas 30 27-FEB-10 300000 370000
Wong Theresa 30 27-FEB-10 70000 370000
Newton Frances 14-SEP-05 75000 75000
11 rows selected. 結論
分析関数は、他の方法では複雑でおそらくはパフォーマンスの悪いSQLが必要になるようなデータについて、疑問に対する回答を得られる優れた方法です。どの分析関数を使用すべきか、さらにはユーザーにとってもっともわかりやすい結果が得られるデータのレポート・ビューを生成するのはどのウィンドウ句（使用する場合）であるかは、各ユーザーのレポート・ニーズによって変わります。この記事では、よく使用される分析関数（SUM）やPARTITION句とOVER句、ROWSとRANGEの各ウィンドウ句に関する使用例と、いくつかのよく使用される基本的なウィンドウ句のパラメータの指定について取り上げました。このSQLの基礎に関する次回の記事では、引き続き分析関数について説明します。Oracle分析関数を使用して収集できるデータに関して詳しくは、bit.ly/yWtbz1 およびbit.ly/R4cZyqを確認してください。
