リレーショナル・データベースとSQLの基本に関するシリーズ記事のパート11
この記事は、よく使用されるSQL分析関数および関連する句を紹介する、3回にわたるシリーズの第2回です。分析関数はSQLを拡張する関数であり、複雑な問合せをコーディングしやすくし、その実行を高速化します。このシリーズ記事のパート10 "分析関数の世界への扉を開く"（Oracle Magazine、2013年3/4月）では、SUM分析関数、PARTITION句とOVER句、ROWSとRANGEの各ウィンドウ句、およびウィンドウ句の各種パラメータの指定方法について説明しました。これらの機能を利用すれば、業務レポート生成の目的で結果セット・データを操作できます。この記事では、問合せで次の操作を実行するための分析関数を紹介します。
-
データのランキング—例：部門ごとに給与の高い上位3人の従業員を表示する
-
グループの最初の値と最後の値を返す—例：ある部門内のすべての従業員の給与を、その部門で最近雇用された従業員の給与と比較する
-
現在処理中の行の前（LEAD）または後（LAG）にある行に関するレポートを生成する—例：ある従業員の雇用日から、その従業員の直前に雇用された従業員の雇用日までの日数を算出する
-
グループ内の割合を取得する—例：ある部門での所属する全従業員への年間総支払額に対する、特定の従業員が受け取った額の割合を算出する
次のステップ
その他の記事 SQLの基礎、パート1～10
リレーショナル・データベースの 設計と概念に関する詳細の確認
Oracle Database Concepts 11g Release 2 (11.2)
Oracle Database SQL言語リファレンス11gリリース1（11.1）
Oracle Databaseデータ・ウェアハウス・ガイド11gリリース2（11.2）
Oracle Database SQL言語リファレンス11gリリース2（11.2）
ダウンロード この記事のサンプル・スクリプト
このシリーズ記事の例を試すには、Oracle Databaseインスタンスにアクセスする必要があります。必要に応じて、お使いのオペレーティング・システムに対応したOracle Databaseエディションをダウンロードし、インストールしてください。筆者がインストールをお勧めするエディションは、Oracle Database, Express Edition 11g Release 2です。このOracle Databaseソフトウェアをインストールする場合は、データベースの作成と構成が可能なインストール・オプションを選択してください。サンプルのユーザー・アカウントと関連する新しいスキーマを含む新しいデータベースが作成されます（SQL_101は、このシリーズ記事の例で使用するユーザー・アカウントです。また、データベースの表やその他のオブジェクトが作成されるスキーマでもあります）。インストール・プロセスの実行中にスキーマのパスワードを指定するように求められたら、SYSおよびSYSTEMのパスワードを入力して確認し、そのパスワードを覚えておいてください。
最後に、このデータベース・ソフトウェアをゼロからインストールした場合でも、既存のOracle Databaseインスタンスにアクセスする場合でも、SQLスクリプトをダウンロードして解凍し、実行して、この記事の例で必要となるSQL_101スキーマ用の表を作成します（このスクリプトをテキスト・エディタで開き、実行方法の説明を確認してください）。
上位にランキングされる
あるデータベース表から、特定の条件を満たす上位または下位N行（複数可）を取得する問合せは、上位N番までの問合せと呼ばれることがあります。たとえば、給与額が上位の従業員は誰か、売上額が下位の部門はどこかを求めたいことがあるでしょう。そのような疑問に回答するための簡単な方法は、分析関数のRANKまたはDENSE_RANKを使用することです。これらの関数はいずれも、ある値グループ内で、ある特定の値の数値的なランクを計算して表示します。リスト1の例では、部門ごとにパーティション化してソートし、さらに給与の降順でソートした上で、すべての従業員とその給与の値を並べて表示します。この例では、DENSE_RANK分析関数を使用して、各部門内で給与に対して数値で表すランクを割り当てています。
コード・リスト1：コード・リスト1：従業員を部門、給与によってランキングして表示
SQL> set feedback on
SQL> set lines 32000
SQL> select department_id, last_name, first_name, salary,
2 DENSE_RANK() over (partition by department_id
3 order by salary desc) dense_ranking
4 from employee
5 order by department_id, salary desc, last_name, first_name;
DEPARTMENT_ID LAST_NAME FIRST_NAME SALARY DENSE_RANKING
————————————— ——————————— ————————————————————————— —————— —————————————
10 Dovichi Lori 1
10 Eckhardt Emily 100000 2
10 Newton Donald 80000 3
10 Michaels Matthew 70000 4
10 Friedli Roger 60000 5
10 James Betsy 60000 5
20 peterson michael 90000 1
20 leblanc mark 65000 2
30 Jeffrey Thomas 300000 1
30 Wong Theresa 70000 2
Newton Frances 75000 1
11 rows selected.リスト1の結果には、分析関数の興味深い現象が見られます。それは、問合せで降順のソートを使用する場合に、使用する分析関数の結果にNULL値が影響を及ぼしていることです。降順のソートの場合に、SQLのデフォルトでは、NULLが最大であると見なされます。リスト1では、従業員Lori DovichiのレコードにはSALARY値がありませんが、DENSE_RANK分析関数によってこの従業員の給与ランクの値が、部門10の中で1（つまり1位）とされています。
次のようなWHERE句を追加することで、NULLを検討対象から除外できます。
WHERE SALARY IS NOT NULLまたは、リスト2に示すように、ウィンドウ句内のORDER BY句に対してNULLS LAST拡張を使用することもできます。この場合にも、問合せ全体のORDER BY句により給与の降順で並べ替えられることからLori Dovichiのレコードは部門10の最初に登場します。しかし、このレコードのランク値は、部門10での最下位の5に変わります。他の注意点として、DENSE_RANK関数では、部門10の結果にある2つのレコード（Roger FriedliとBetsy James）に対して同じランク値4を割り当てています。これは、両方の従業員のSALARY値が同じであるためです。
コード・リスト2：NULLS LAST付きで従業員を部門、給与によってランキングして表示
SQL> select department_id, last_name, first_name, salary,
2 DENSE_RANK() over (partition by department_id
3 order by salary desc NULLS LAST) dense_ranking
4 from employee
5 order by department_id, salary desc, last_name, first_name;
DEPARTMENT_ID LAST_NAME FIRST_NAME SALARY DENSE_RANKING
————————————— ——————————— ————————————————————————— —————— —————————————
10 Dovichi Lori 5
10 Eckhardt Emily 100000 1
10 Newton Donald 80000 2
10 Michaels Matthew 70000 3
10 Friedli Roger 60000 4
10 James Betsy 60000 4
20 peterson michael 90000 1
20 leblanc mark 65000 2
30 Jeffrey Thomas 300000 1
30 Wong Theresa 70000 2
Newton Frances 75000 1
11 rows selected.リスト3では、リスト2と同じような問合せを実行しますが、DENSE_RANKの代わりにRANK分析関数を使用します。この結果には、部門10に5というランク値がありません。その理由は、DENSE_RANKとRANKにおいて、レコードへのランク値の適用方法が異なることです。DENSE_RANKが返すランクの数値には欠落がありません。ORDER BYウィンドウ句内の式の値が同じレコードがあったとしても、欠落は発生しません。これに対して、RANK分析関数により複数の行の値が同じであることが検出され、それらの行に同じランクが割り当てられた場合は、後続のランクの数値でこのことが考慮されて、その分ランクがスキップされます。リスト3の結果からわかるとおり、RANKにより2つのレコードにランク値4が割り当てられ、その部門の最後のレコードではランク値が最下位の6にまでスキップされています。
コード・リスト3：DENSE_RANK分析関数の代わりにRANK分析関数を使用
SQL> select department_id, last_name, first_name, salary,
2 RANK() over (partition by department_id
3 order by salary desc NULLS LAST) regular_ranking
4 from employee
5 order by department_id, salary desc, last_name, first_name;
DEPARTMENT_ID LAST_NAME FIRST_NAME SALARY REGULAR_RANKING
————————————— ——————————— ——————————————————————— —————— ———————————————
10 Dovichi Lori 6
10 Eckhardt Emily 100000 1
10 Newton Donald 80000 2
10 Michaels Matthew 70000 3
10 Friedli Roger 60000 4
10 James Betsy 60000 4
20 peterson michael 90000 1
20 leblanc mark 65000 2
30 Jeffrey Thomas 300000 1
30 Wong Theresa 70000 2
Newton Frances 75000 1
11 rows selected.1位または最下位で終える
レポート生成の目的では、問合せ結果を表示する際に、特定のグループまたはウィンドウの最初の取得値を含めると役に立つ場合もあります。この目的で、FIRST_VALUE分析関数を使用できます（リスト4を参照）。リスト4の問合せは、部門ごとにパーティション化され、各パーティション内で雇用日により並べ替えられたウィンドウが返されます。返されたそれぞれのSALARY値とともに、ウィンドウごとに最初に取得したSALARY値も表示されます。この情報は、部門内のすべての従業員のSALARY値を、その部門で最初に雇用された従業員のSALARY値と比較する場合に便利です。
コード・リスト4：FIRST_VALUEを使用してウィンドウごとに最初に返された値を表示
SQL> select last_name, first_name, department_id, hire_date, salary,
2 FIRST_VALUE(salary)
3 over (partition by department_id order by hire_date) first_sal_by_dept
4 from employee
5 order by department_id, hire_date;
LAST_NAME FIRST_NAME DEPARTMENT_ID HIRE_DATE SALARY FIRST_SAL_BY_DEPT
————————— —————————————— —————————————— ————————— ——————— —————————————————
Eckhardt Emily 10 07-JUL-04 100000 100000
Newton Donald 10 24-SEP-06 80000 100000
James Betsy 10 16-MAY-07 60000 100000
Friedli Roger 10 16-MAY-07 60000 100000
Michaels Matthew 10 16-MAY-07 70000 100000
Dovichi Lori 10 07-JUL-11 100000
peterson michael 20 03-NOV-08 90000 90000
leblanc mark 20 06-MAR-09 65000 90000
Jeffrey Thomas 30 27-FEB-10 300000 300000
Wong Theresa 30 27-FEB-10 70000 300000
Newton Frances 14-SEP-05 75000 75000
11 rows selected.リスト4の問合せ結果を、リスト5の結果と比較してみましょう。リスト5の問合せではLAST_VALUE分析関数を使用していますが、この使用法は誤っています。LAST_VALUE分析関数を単純にFIRST_VALUE分析関数と置き換えるだけでは、ウィンドウごとに最後の値を返すという結果は期待できません。すでに説明したとおり、パーティション内のウィンドウ句を伴わないORDER BY句のデフォルトの動作として、デフォルト・ウィンドウが現在の行とその前のすべての行に基づいて動作するスライド・ビューになります。そのため、リスト5では、LAST_VALUE関数コールにより返される値は、常に現在の行のSALARY値となります。LAST_VALUE関数のコールを意味あるものにするには、パーティション内のORDER BY句にウィンドウ句を追加する必要があります（リスト6を参照）。
コード・リスト5：LAST_VALUEの使用法が正しくないため、ウィンドウごとの最後の値を取得できない例
SQL> select last_name, first_name, department_id, hire_date, salary,
2 LAST_VALUE(salary)
3 over (partition by department_id order by hire_date) last_sal_by_dept
4 from employee
5 order by department_id, hire_date;
LAST_NAME FIRST_NAME DEPARTMENT_ID HIRE_DATE SALARY LAST_SAL_BY_DEPT
————————— ——————————— ——————————————— ————————— ——————— ————————————————
Eckhardt Emily 10 07-JUL-04 100000 100000
Newton Donald 10 24-SEP-06 80000 80000
James Betsy 10 16-MAY-07 60000 70000
Friedli Roger 10 16-MAY-07 60000 70000
Michaels Matthew 10 16-MAY-07 70000 70000
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 70000
Newton Frances 14-SEP-05 75000 75000
11 rows selected.リスト6の問合せによって、各部門パーティション内の従業員レコードが雇用日によってソートされ、各パーティション内の最後のSALARY値や、その値に関連する従業員の姓とともに表示されます。NULLのSALARY値を対象とすると有効な比較を行えないので、この問合せでは、WHERE SALARY IS NOT NULLを指定しています。従業員Lori DovichiのレコードはNULLであるため、部門10のパーティションには含まれていません。部門10のパーティションの他のレコードには、そのパーティション内の最後の従業員レコードとして、Matthew Michaelsの姓とSALARY値が表示されます。従業員Frances NewtonのLAST_EMPとLAST_SALの値は、この従業員レコードの対応する値と同じです。他に部門IDがNULL値である従業員レコードはないからです。NULL値の存在有無にかかわらず、すべてのレコードを含める場合は、IGNORE NULLS拡張を使用して、LAST_VALUE分析関数コールにおいてNULLを検討対象から除外できます。そのためには、LAST_VALUE (salary)コールをLAST_VALUE (salary IGNORE NULLS)へと変更します。
コード・リスト6：LAST_VALUEを正しく使用してウィンドウごとに最後の値を表示
SQL> select last_name, first_name, department_id dept_id, hire_date, salary,
2 LAST_VALUE(last_name)
3 over (partition by department_id order by hire_date
4 ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) last_emp,
5 LAST_VALUE(salary)
6 over (partition by department_id order by hire_date
7 ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) last_sal
8 from employee
9 where salary is not null
10 order by department_id, hire_date, last_name, first_name;
LAST_NAME FIRST_NAME DEPT_ID HIRE_DATE SALARY LAST_EMP LAST_SAL
—————————— ————————————— ————————— ————————— ————————— ———————— ——————————
Eckhardt Emily 10 07-JUL-04 100000 Michaels 70000
Newton Donald 10 24-SEP-06 80000 Michaels 70000
Friedli Roger 10 16-MAY-07 60000 Michaels 70000
James Betsy 10 16-MAY-07 60000 Michaels 70000
Michaels Matthew 10 16-MAY-07 70000 Michaels 70000
peterson michael 20 03-NOV-08 90000 leblanc 65000
leblanc mark 20 06-MAR-09 65000 leblanc 65000
Jeffrey Thomas 30 27-FEB-10 300000 Wong 70000
Wong Theresa 30 27-FEB-10 70000 Wong 70000
Newton Frances 14-SEP-05 75000 Newton 75000
10 rows selected.前後の行
比較の目的でよくあるレポート要件は、現在確認中の行だけではなく、その現在の行の前または後にある行のデータにもアクセスできることです。リスト7の問合せを見てみましょう。LAG分析関数を使用すれば、部門ごとに現在の従業員の雇用日と、その直前に雇用された従業員の雇用日を並べて表示できます。たとえば、従業員Donald Newtonのレコードには、その前に雇用された従業員の雇用日が07-JUL-04であると示されます。Donald Newtonの直前のレコードであるEmily Eckhardtのレコードを見ると、Emilyの雇用日はたしかに07-JUL-04です。
コード・リスト7：LAG分析関数を使用して、現在の行の前にある行データを取得
SQL> select last_name, first_name, department_id, hire_date,
2 LAG(hire_date, 1, null) over (partition by department_id
3 order by hire_date) prev_hire_date
4 from employee
5 order by department_id, hire_date, last_name, first_name;
LAST_NAME FIRST_NAME DEPARTMENT_ID HIRE_DATE PREV_HIRE
————————— —————————————— —————————————————————————— ————————— —————————
Eckhardt Emily 10 07-JUL-04
Newton Donald 10 24-SEP-06 07-JUL-04
Friedli Roger 10 16-MAY-07 24-SEP-06
James Betsy 10 16-MAY-07 16-MAY-07
Michaels Matthew 10 16-MAY-07 16-MAY-07
Dovichi Lori 10 07-JUL-11 16-MAY-07
peterson michael 20 03-NOV-08
leblanc mark 20 06-MAR-09 03-NOV-08
Jeffrey Thomas 30 27-FEB-10
Wong Theresa 30 27-FEB-10 27-FEB-10
Newton Frances 14-SEP-05
11 rows selected.LAG分析関数の構文は次のとおりです。
LAG(<em>列</em> | <em>式</em>, <em>オフセット</em>, <em>デフォルト</em>)オフセットは正の整数で、デフォルト値は1です。このパラメータにより、何行前に戻るかを指定します。値が1の場合、"現在のウィンドウ内で現在の行の直前にある行を参照する"という意味になります。デフォルトは、オフセット値（インデックス）が現在のウィンドウの範囲外である場合に返す値です。グループ内の最初の行の場合、デフォルト値が返されます。
LEAD分析関数の構文もLAG分析関数とほとんど同じですが、次の2点の違いがあります。
-
オフセット・パラメータにより、現在の行から何行後に進むかを指定する。
-
グループ内の最後の行の場合、デフォルト値が返される。
リスト8の問合せを見てみましょう。リスト8からわかるとおり、LEAD分析関数は、現在の行の直後にある行を参照し、レポートします。各部門の最後にある従業員レコードのFOLLOWING_HIRE_DATE列の値はNULLです。これは、各部門グループ内では、そのレコードより後にレコードがないためです。同様に、新しい部門グループが表示されるたびに、最初にある従業員レコードのPREV_HIRE_DATE列の値もNULLになります。これは、各グループ内では、そのレコードの前にレコードがないためです。
コード・リスト8：LAGとLEADを使用して、現在の行の前後にある行データを取得
SQL> select last_name, first_name, department_id, hire_date,
2 LAG(hire_date, 1, null) over (partition by department_id
3 order by hire_date) prev_hire_date,
4 LEAD(hire_date, 1, null) over (partition by department_id
5 order by hire_date) following_hire_date
6 from employee
7 order by department_id, hire_date, last_name, first_name;
LAST_NAME FIRST_NAME DEPARTMENT_ID HIRE_DATE PREV_HIRE FOLLOWING
——————————— ———————————— —————————————————— ————————— ————————— —————————
Eckhardt Emily 10 07-JUL-04 24-SEP-06
Newton Donald 10 24-SEP-06 07-JUL-04 16-MAY-07
Friedli Roger 10 16-MAY-07 24-SEP-06 16-MAY-07
James Betsy 10 16-MAY-07 16-MAY-07 16-MAY-07
Michaels Matthew 10 16-MAY-07 16-MAY-07 07-JUL-11
Dovichi Lori 10 07-JUL-11 16-MAY-07
peterson michael 20 03-NOV-08 06-MAR-09
leblanc mark 20 06-MAR-09 03-NOV-08
Jeffrey Thomas 30 27-FEB-10 27-FEB-10
Wong Theresa 30 27-FEB-10 27-FEB-10
Newton Frances 14-SEP-05
11 rows selected.割合を上げる
ビジネス・ユーザーが割合（%）に関するレポートを作成しなければならないこともよくあります。売上額、総費用、年間給与などは、割合の計算が必要になる可能性の高い数字のごく一部として挙げられます。リスト9の問合せでは、RATIO_TO_REPORT分析関数を使用して、"年間給与分配総額に対する、各従業員が受け取る給与の割合は？"という疑問に回答しています。RATIO_TO_REPORT分析関数の構文は次のとおりです。
RATIO_TO_REPORT( 列 | 式)コード・リスト9：RATIO_TO_REPORTを使用して給与の割合を取得
SQL> select last_name, first_name, department_id, hire_date, salary,
round(RATIO_TO_REPORT(salary) over ()*100, 2) sal_percentage
2 from employee
3 order by department_id, salary desc, last_name, first_name;
LAST_NAME FIRST_NAME DEPARTMENT_ID HIRE_DATE SALARY SAL_PERCENTAGE
——————————— ———————————— —————————————— —————————— —————— ——————————————
Dovichi Lori 10 07-JUL-11
Eckhardt Emily 10 07-JUL-04 100000 10.31
Newton Donald 10 24-SEP-06 80000 8.25
Michaels Matthew 10 16-MAY-07 70000 7.22
Friedli Roger 10 16-MAY-07 60000 6.19
James Betsy 10 16-MAY-07 60000 6.19
peterson michael 20 03-NOV-08 90000 9.28
leblanc mark 20 06-MAR-09 65000 6.7
Jeffrey Thomas 30 27-FEB-10 300000 30.93
Wong Theresa 30 27-FEB-10 70000 7.22
Newton Frances 14-SEP-05 75000 7.73
11 rows selected.この分析関数の優れた機能の1つは、結果の割合の値を取得するために使用される式の値について、自動的に合計が算出されるところです。集計関数コールを明示的に追加する必要はありません。また、この問合せの例における分析関数コールでは、ORDER BY句や追加のウィンドウ句を指定していないため、行セット全体がウィンドウとして使用されます。リスト9とリスト10の問合せの結果を比較してみてください。リスト10の問合せでは、OVER句にPARTITION句を追加して、部門ごとの給与総額に対して各従業員が受け取る給与の割合を計算します。
コード・リスト10：RATIO_TO_REPORTを使用して部門ごとに給与の割合を取得
SQL> select last_name, first_name, department_id, hire_date, salary,
round(ratio_to_report(salary)
2 over(partition by department_id)*100, 2) sal_dept_pct
3 from employee
4 order by department_id, salary desc, last_name, first_name;
LAST_NAME FIRST_NAME DEPARTMENT_ID HIRE_DATE SALARY SAL_DEPT_PCT
—————————— ————————————— ——————————————— ————————— —————— ————————————
Dovichi Lori 10 07-JUL-11
Eckhardt Emily 10 07-JUL-04 100000 27.03
Newton Donald 10 24-SEP-06 80000 21.62
Michaels Matthew 10 16-MAY-07 70000 18.92
Friedli Roger 10 16-MAY-07 60000 16.22
James Betsy 10 16-MAY-07 60000 16.22
peterson michael 20 03-NOV-08 90000 58.06
leblanc mark 20 06-MAR-09 65000 41.94
Jeffrey Thomas 30 27-FEB-10 300000 81.08
Wong Theresa 30 27-FEB-10 70000 18.92
Newton Frances 14-SEP-05 75000 100
11 rows selected.結論
この記事では、このシリーズのパート10で紹介した分析関数について引き続き説明しました。結果の表示方法を操作するためによく使用される分析関数について、新たに7つの使用例を示しました。分析関数のRANKとDENSE_RANKを使用して、上位N番までの問合せの結果を取得する方法を確認し、これら2つの関数の違いを理解しました。また、グループ内部でのデータ比較の目的で、分析関数のFIRST_VALUEとLAST_VALUEをレポート内で使用する方法についても確認しました。さらに、LEADとLAGによって、現在の行の値の前後にある行の値を表示して、データの比較をしやすくする方法についても理解しました。さらに、この記事のオンライン版をご覧の場合は、RATIO_TO_REPORT分析関数を使用して、グループ内で割合を取得する方法について確認しました。
すべてのケースで、これらの分析関数を存分に活用することで、同じ結果を取得するために複雑なSQLを記述しなければならない状況が大幅に減ることになるでしょう。詳しくは、Oracle Database SQL言語リファレンスとOracle Databaseデータ・ウェアハウス・ガイドを確認してください。この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年）の共著者でもあります。