記事一覧へ戻る

掲載元
Oracle Magazine
2013年5/6月

テクノロジー:SQLの基礎

  

分析関数:ランキング、LEAD/LAG、レポート

著者:Melanie Caffrey

 

リレーショナル・データベースと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( | , オフセット, デフォルト)

 

オフセットは正の整数で、デフォルト値は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の顔写真

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年)の共著者でもあります。

▲ ページTOPに戻る

記事一覧へ戻る