まだまだあるぞ！ 分析関数の究極テクニック：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行が選択されました。
この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の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行が選択されました。
（次ページへ続く）
Copyright © ITmedia, Inc. All Rights Reserved.
注目のテーマ
ITmediaはアイティメディア株式会社の登録商標です。