まだまだあるぞ！ 分析関数の究極テクニック：SQLクリニック（10）（2/2 ページ）
リード関数を使った分析
次は、リード関数を利用して、分析してみましょう。リード関数は、現在の行からのオフセットを指定するだけで、その位置より「後」にある指定された行へアクセスすることができます。リード関数は、ラグ関数が「前」にあるデータにアクセスできたのと逆ですね。
リード関数もラグ関数と同様に、リード関数を使用せずに同等の結果を取得するには、内部結合やファンクションを使用してデータを取得する必要があります。リード関数を使用することで、対象表に1度アクセスするだけで、簡単に売り上げ比較などを行うことができSQL文のパフォーマンスが大幅に向上します。
リード関数は、アクセスできるデータが「前」「後」の違いだけなので、ラグ関数との違いさえ分かれば簡単に理解できると思います。
実際にリード関数を使用した例を見ていきましょう。リード関数の構文は、以下のとおりです。ラグ関数と同様なので、引数の詳細などは前ページのラグ関数を参照してください。
構文 LEAD ( value_expr [,offset] [, default] )
OVER ( [query_partition_clause] order_by_clause )
ラグ関数のときに使用したSALES表を使用して、リード関数の動きを見てみましょう。
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行が選択されました。
リード関数を使用して、地域（region）ごとに売上金額（sales）に関する比較を行い、売り上げの推移を確認してみましょう。
SQL> select time 2 , region 3 , sales 4 , lead(sales, 1, 0) 5 over(partition by region order by time) as lead_sales 6 , (sales - lead(sales, 1, 0) over (partition by region 7 order by time)) as comp_sales 8 from sales ; TIME REGION SALES LEAD_SALES COMP_SALES -------- ------ ------ ---------- ---------- 05-05-26 関西 5500 6370 -870 05-06-25 関西 6370 5900 470 05-07-25 関西 5900 7130 -1230 05-08-24 関西 7130 6910 220 05-09-23 関西 6910 5820 1090 05-10-23 関西 5820 0 5820 05-05-26 関東 9890 11340 -1450 05-06-25 関東 11340 12780 -1440 05-07-25 関東 12780 14610 -1830 05-08-24 関東 14610 13470 1140 05-09-23 関東 13470 11290 2180 05-10-23 関東 11290 0 11290 05-05-26 九州 1760 2160 -400 05-06-25 九州 2160 1980 180 05-07-25 九州 1980 2740 -760 05-08-24 九州 2740 1300 1440 05-09-23 九州 1300 3500 -2200 05-10-23 九州 3500 0 3500 18行が選択されました。
リスト4のSQL文のリード関数では、売上金額（sales）に対して1つ後のデータを表示するように指定しています。また、1つ後のデータが存在しない場合には、0（ゼロ）を返すように指定しています。その月の売上金額（sales）とリード関数で取得した1つ後の月の売上金額（lead_sales）を引き算することで、地域（region）ごとの売上金額の推移を取得することができます。
LEAD_SALES列の結果より、リード関数で1つ後の月のデータを参照できていることが確認できると思います。
ちなみに、リード関数を使用したSQL文と同等の結果をリード関数を使用せずに取得するには、リスト5のようなSQL文を書かなくてはいけません。
SQL> select a.time 2 , a.region 3 , a.sales 4 , nvl(b.sales,0) as lead_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 LEAD_SALES COMP_SALES -------- ------ ------ ---------- ---------- 05-05-26 関西 5500 6370 -870 05-06-25 関西 6370 5900 470 05-07-25 関西 5900 7130 -1230 05-08-24 関西 7130 6910 220 05-09-23 関西 6910 5820 1090 05-10-23 関西 5820 0 5820 05-05-26 関東 9890 11340 -1450 05-06-25 関東 11340 12780 -1440 05-07-25 関東 12780 14610 -1830 05-08-24 関東 14610 13470 1140 05-09-23 関東 13470 11290 2180 05-10-23 関東 11290 0 11290 05-05-26 九州 1760 2160 -400 05-06-25 九州 2160 1980 180 05-07-25 九州 1980 2740 -760 05-08-24 九州 2740 1300 1440 05-09-23 九州 1300 3500 -2200 05-10-23 九州 3500 0 3500 18行が選択されました。
今回で分析関数についての説明は終了です。これまでの説明で、分析関数を知らなかった方や、分析関数の存在は知っていたけど使い方がよく分からずに敬遠していた方が、分析関数を学ぶきっかけになれば幸いです。分析関数を使用すれば、現在運用中のアプリケーションのメンテナンスとパフォーマンスが数段によくなるかもしれませんよ。この機会にぜひ検討してみてください。SQL文は、奥が深いですね。自分でもつくづく感じた今日このごろです。（次回に続く）
筆者紹介
Oracleに特化した製品開発、コンサルティングを手掛けるエンジニア集団。大道隆久は緊迫したトラブル現場でも常に冷静沈着であり、スマートに解決へと導いていくシステムコンサルタント。
Copyright © ITmedia, Inc. All Rights Reserved.
注目のテーマ
ITmediaはアイティメディア株式会社の登録商標です。