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

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

[中島益次郎,株式会社インサイトテクノロジー]
前のページへ 1|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行が選択されました。
リスト1(再掲) サンプル表(sales):地域の月の売り上げを管理している表

 リード関数を使用して、地域(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 リード関数を使った売り上げ推移の分析

 リスト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行が選択されました。
リスト5 リード関数を使わずリスト4と同等の結果を得るSQL

 今回で分析関数についての説明は終了です。これまでの説明で、分析関数を知らなかった方や、分析関数の存在は知っていたけど使い方がよく分からずに敬遠していた方が、分析関数を学ぶきっかけになれば幸いです。分析関数を使用すれば、現在運用中のアプリケーションのメンテナンスとパフォーマンスが数段によくなるかもしれませんよ。この機会にぜひ検討してみてください。SQL文は、奥が深いですね。自分でもつくづく感じた今日このごろです。(次回に続く)

筆者紹介

株式会社インサイトテクノロジー

Oracleに特化した製品開発、コンサルティングを手掛けるエンジニア集団。大道隆久は緊迫したトラブル現場でも常に冷静沈着であり、スマートに解決へと導いていくシステムコンサルタント。



前のページへ 1|2       

Copyright © ITmedia, Inc. All Rights Reserved.

RSSについて

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

メールマガジン登録

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