連載
» 2005年11月26日 00時00分 公開

SQLクリニック(10):まだまだあるぞ! 分析関数の究極テクニック (1/2)

[中島益次郎,株式会社インサイトテクノロジー]

本連載はSQLの応用力を身に付けたいエンジニア向けに、さまざまなテクニックを紹介する。SQLの基本構文は平易なものだが、実務で活用するには教科書的な記述を理解するだけでは不十分だ。本連載は、著名なメールマガジン「おら!オラ! Oracle - どっぷり検証生活」を発行するインサイトテクノロジーのコンサルタントを執筆陣に迎え、SQLのセンス向上に役立つ大技小技を紹介していく。(編集局)

 今回も、前回「SQL分析関数をさらに深く追求してみよう」、前々回「極めよう!分析関数によるSQL高速化計画」に引き続き、分析関数についてのお話です。今回は、ラグ・リード関数を説明して、分析関数シリーズの最終回とします。

ラグ関数を使った分析

 それでは、ラグ・リード関数を利用して、分析してみましょう。まずは、ラグ関数から見ていきます。

 ラグ関数は、現在の行からのオフセットを指定するだけで、その位置より「前」にある指定された行へアクセスすることができます。ラグ関数を使用せずに同等の結果を取得するには、内部結合やファンクションを使用してデータを取得する必要があります。ラグ関数を使用すると、対象表に1度アクセスするだけで、簡単に前月との売り上げ比較などを行うことができ、SQL文のパフォーマンスが大幅に向上します。

 では、実際にラグ関数を使用した例を見て、理解を深めていきましょう。ラグ関数の構文は、以下のとおりです。

構文  LAG ( value_expr [,offset] [, default] )
OVER ( [query_partition_clause] order_by_clause )

 ラグ関数の引数について、説明をしておきましょう。value_exprは、分析する対象列名を指定します。offsetは、何行前のデータを表示するかを指定します。defaultは、offsetで指定されたデータが対象表またはグループに存在しなくなった場合に、ここで指定した値を戻します。例えば、対象表の1行目には「offsetで指定された前のデータ」が存在しないというような状況の場合です。defaultを指定しない場合は、NULLが返ります。

 それでは、リスト1のサンプル表を使用してラグ関数の動きを見てみましょう。

SQL> desc sales
 名前    NULL?    型
 ------- -------- -------------
 TIME             DATE
 REGION           VARCHAR2(100)
 SALES            NUMBER
SQL> select time, region, sales from sales ;
TIME     REGION  SALES
-------- ------ ------
05-05-26 九州     1760
05-06-25 九州     2160
05-07-25 九州     1980
05-08-24 九州     2740
05-09-23 九州     1300
05-10-23 九州     3500
05-05-26 関東     9890
05-06-25 関東    11340
05-07-25 関東    12780
05-08-24 関東    14610
05-09-23 関東    13470
05-10-23 関東    11290
05-05-26 関西     5500
05-06-25 関西     6370
05-07-25 関西     5900
05-08-24 関西     7130
05-09-23 関西     6910
05-10-23 関西     5820
18行が選択されました。
リスト1 サンプル表(sales):地域の月の売り上げを管理している表

 このsales表を使用して、地域(region)ごとに売上金額(sales)に関する前月比較を行い、売り上げの推移を確認してみましょう。

SQL> select time
  2       , region
  3       , sales
  4       , lag(sales, 1, 0)
  5           over(partition by region order by time) as lag_sales
  6       , (sales - lag(sales, 1, 0) over (partition by region
  7                                             order by time)) as comp_sales
  8    from sales  ;
TIME     REGION  SALES  LAG_SALES COMP_SALES
-------- ------ ------ ---------- ----------
05-05-26 関西     5500          0       5500
05-06-25 関西     6370       5500        870
05-07-25 関西     5900       6370       -470
05-08-24 関西     7130       5900       1230
05-09-23 関西     6910       7130       -220
05-10-23 関西     5820       6910      -1090
05-05-26 関東     9890          0       9890
05-06-25 関東    11340       9890       1450
05-07-25 関東    12780      11340       1440
05-08-24 関東    14610      12780       1830
05-09-23 関東    13470      14610      -1140
05-10-23 関東    11290      13470      -2180
05-05-26 九州     1760          0       1760
05-06-25 九州     2160       1760        400
05-07-25 九州     1980       2160       -180
05-08-24 九州     2740       1980        760
05-09-23 九州     1300       2740      -1440
05-10-23 九州     3500       1300       2200
18行が選択されました。
リスト2 ラグ関数を使った売り上げ推移の分析

 リスト2のSQL文のラグ関数では、売上金額(sales)に対して1つ前のデータを表示するように指定しています。また、1つ前のデータが存在しない場合には、0(ゼロ)を返すように指定しています。その月の売上金額(sales)とラグ関数で取得した前月の売上金額(lag_sales)を引き算することで、地域(region)ごとの売上金額の推移を取得できます。

 LAG_SALES列の結果より、ラグ関数で1つ前の月のデータを参照できていることが確認できると思います。内部結合を行わずに、単純なことを簡単にでき、パフォーマンスの向上にもつながるラグ関数を理解できたと思います。

 ちなみに、ラグ関数を使用したSQL文と同等の結果をラグ関数を使用せずに取得するには、リスト3のようなSQL文を書かなくてはいけません。大変ですね。

SQL> select a.time
  2       , a.region
  3       , a.sales
  4       , nvl(b.sales,0) as lag_sales
  5       , (a.sales - nvl(b.sales,0)) as comp_sales
  6    from sales a, sales b
  7   where a.region = b.region(+)
  8     and to_char(a.time-30,'YYYY-MM-DD') = to_char(b.time(+), 'YYYY-MM-DD')
  9   order by 2,1 ;
TIME     REGION  SALES  LAG_SALES COMP_SALES
-------- ------ ------ ---------- ----------
05-05-26 関西     5500          0       5500
05-06-25 関西     6370       5500        870
05-07-25 関西     5900       6370       -470
05-08-24 関西     7130       5900       1230
05-09-23 関西     6910       7130       -220
05-10-23 関西     5820       6910      -1090
05-05-26 関東     9890          0       9890
05-06-25 関東    11340       9890       1450
05-07-25 関東    12780      11340       1440
05-08-24 関東    14610      12780       1830
05-09-23 関東    13470      14610      -1140
05-10-23 関東    11290      13470      -2180
05-05-26 九州     1760          0       1760
05-06-25 九州     2160       1760        400
05-07-25 九州     1980       2160       -180
05-08-24 九州     2740       1980        760
05-09-23 九州     1300       2740      -1440
05-10-23 九州     3500       1300       2200
18行が選択されました。
リスト3 ラグ関数を使わずリスト2と同等の結果を得るSQL

次ページへ続く)

       1|2 次のページへ

Copyright © ITmedia, Inc. All Rights Reserved.

RSSについて

アイティメディアIDについて

メールマガジン登録

@ITのメールマガジンは、 もちろん、すべて無料です。ぜひメールマガジンをご購読ください。