分析関数の衝撃（後編）

CodeZineに掲載されたSQLを分析関数で記述する 3

2日前からの累計

　次は移動累計を求めるSQLのアレンジ問題です。「分析関数の衝撃（中編）」では、「相関サブクエリで行と行を比較する」の移動累計を求めるSQLを扱いましたが、これをアレンジして、2日前からの累計を求めるSQLを考えます。

　まずは、値を少し変更したテーブルのデータと、出力結果を考えます。

Accountsテーブル

prc_date	prc_amt
2006/10/26	100
2006/10/28	200
2006/10/31	400
2006/11/03	800
2006/11/04	1,600
2006/11/06	3,200
2006/11/11	6,400

出力結果

prc_date	prc_amt	mvg_sum
2006/10/26	100	100
2006/10/28	200	300
2006/10/31	400	400
2006/11/03	800	800
2006/11/04	1,600	2,400
2006/11/06	3,200	4,800
2006/11/11	6,400	6,400

　2日前からの累計について補足すると、例えばprc_dateが2006/10/26のレコードは、2006/10/26の2日前の2006/10/24から2006/10/26のprc_amtが累計の計算対象となります（2006/10/24のレコードも2006/10/25のレコードも、存在しないので0として扱います）。

　手続き型の言語であれば、

1. prc_dateの昇順にソート
2. 各値を、配列に保存
3. 配列の添字の最小値からループ
4. 配列の添字と値を元に、2日前からの累計を求める

　といった処理を行うと思いますが、分析関数を使ったSQLでも似たような考え方を使います。

分析関数で書いたSQL

select prc_date,prc_amt,
sum(prc_amt)
over(order by prc_date range 2 Preceding) as mvg_sum
  from Accounts
order by prc_date;

--intervalを使う方法
select prc_date,prc_amt,
sum(prc_amt)
over(order by prc_date range interval '2' day Preceding) as mvg_sum
  from Accounts
order by prc_date;

　分析関数のrangeを使った、SQLのイメージはこうなります。

SQLのイメージ

　分析関数のwindowing_clauseでrangeを指定して、Precedingに2もしくは、interval '2' dayを指定して、2日前からの累計を求めてます。分析関数のrangeは、次のように解釈すると分かりやすいでしょう。

order by prc_date --prc_dateの昇順で、
range 2 Preceding --ソートキー(prc_date)が2小さい行から、現在の行まで

　下記（1）と下記（2）は、同じ意味なので、

order by prc_date range 2 Preceding                 --（1）
order by prc_date range 2 Preceding and current_row --（2）

　このように解釈しても良いです。

order by prc_date --prc_dateの昇順で、
range between     --行の範囲は、
    2 Preceding   --小さいほうは、ソートキー(prc_date)が2小さい行から
  and current_row --大きいほうは、現在の行まで

　DB2では、下記のSQLとなります。

分析関数で書いたSQL（DB2）

select prc_date,prc_amt,
sum(prc_amt) over(order by julian_day(prc_date)
                  range 2 Preceding) as mvg_sum
  from Accounts
order by prc_date;

2日前の値

　次も移動累計を求めるSQLのアレンジ問題です。次は2日前からの累計ではなく、2日前の値を求めるSQLを考えます。なお、2日前のデータがない場合は、0として扱います。

　まずは、値を少し変更したテーブルのデータと、出力結果を考えます。

Accountsテーブル

prc_date	prc_amt
2006/10/26	100
2006/10/28	200
2006/10/31	400
2006/11/03	800
2006/11/04	1,600
2006/11/06	3,200
2006/11/11	6,400

出力結果

prc_date	prc_amt	before_prc_amt
2006/10/26	100	0
2006/10/28	200	100
2006/10/31	400	0
2006/11/03	800	0
2006/11/04	1,600	0
2006/11/06	3,200	1,600
2006/11/11	6,400	0

　手続き型の言語であれば、

1. prc_dateの昇順にソート
2. 各値を、配列に保存
3. 配列の添字の最小値からループ
4. 配列の添字と値を元に、2日前のprc_amtを求める

　といった処理を行うと思いますが、分析関数を使ったSQLでも似たような考え方を使います。

分析関数で書いたSQL

select prc_date,prc_amt,
nvl(max(prc_amt)
    over(order by prc_date
         range between 2 Preceding
                   and 2 Preceding),0) as mvg_sum
  from Accounts
order by prc_date;

--intervalを使う方法
select prc_date,prc_amt,
nvl(max(prc_amt)
    over(order by prc_date
         range between interval '2' day Preceding
                   and interval '2' day Preceding),0) as mvg_sum
  from Accounts
order by prc_date;

　分析関数のrangeを使った、SQLのイメージはこうなります。

SQLのイメージ

　分析関数のrangeは、次のように解釈すると分かりやすいでしょう。

order by prc_date --prc_dateの昇順で、
range between     --行の範囲は、
    2 Preceding   --小さいほうは、ソートキー(prc_date)が2小さい行から
    2 Preceding   --大きいほうは、2小さい行まで

　SQLについて解説しますと、「prc_date」が2小さい（prc_dateが2日前の）レコードの、「prc_amt」の値を、分析関数のmax関数で求めています。

　「prc_date」が2小さいレコードは、1レコードもしくは0レコードで、1レコードならば、分析関数のmax関数は「prc_date」が2小さいレコードの「prc_amt」を返し、0レコードならば、分析関数のmax関数はnullを返します（nvl関数でnullを0に変換してます）。

　ちなみに、分析関数のmax関数の代わりに、分析関数のmin関数を使ってもいいです。

　DB2では、下記のSQLとなります。

分析関数で書いたSQL（DB2）

select prc_date,prc_amt,
coalesce(max(prc_amt)
         over(order by julian_day(prc_date)
              range between 2 Preceding
                        and 2 Preceding),0) as mvg_sum
  from Accounts
order by prc_date;

最後に

　今回は、今までの分析関数の応用例と、分析関数のorder by句でrangeを使った分析関数を挙げました。rangeの仕様は、Oracleの公式マニュアルを読むと理解しやすいと思います。

　分析関数の衝撃（完結編）では、もう少し難しいSQLと、数学を使ったSQLを扱います。

参考資料

1. CodeZine 『SQLで数列を扱う』 ミック 著、2007年3月
こちらで記載されたSQLを題材にさせていただきました。

2. CodeZine 『相関サブクエリで行と行を比較する』 ミック 著、2007年2月
こちらで記載されたSQLを題材にさせていただきました。

3. OTN Japan 『Oracle8i データウェアハウス』
Oracleの分析関数についての公式資料です。分析関数を使うことによる、可読性とパフォーマンスの向上例が紹介されてます。

4. OTN Japan 『分析ファンクション』
分析関数のOracleの公式マニュアルです。（日本語）

5. US-OTN 『Analytic Functions』
分析関数のOracleの公式マニュアルです。（英語）

6. IBM 『SQL Reference Vol.1』
DB2のSQLの公式マニュアルです。

7. 『SQLクックブック』 Anthony Molinaro 著、木下哲也・有限会社 福龍興業 訳、オライリー・ジャパン、2007年1月
「レシピ10.3　連続する値の範囲の最初と最後を求める」に
本稿の「最大何人まで座れますか？」で使った考え方が掲載されてます。

8. OracleSQLパズル 『大きさNのリージョンを求める（境界なし）』
本稿の「3人なんですけど座れますか？ その1：行の折り返しを考慮しない」で扱ったSQLと別解を置いてます。

9. OracleSQLパズル 『大きさNのリージョンを求める（境界あり）』
本稿の「3人なんですけど座れますか？ その2：行の折り返しも考慮する」で扱ったSQLと別解を置いてます。

10. OracleSQLパズル 『最大のリージョンを求める（境界なし）』
11. OracleSQLパズル 『前後の値で分岐』
12. OracleSQLパズル 『ignore nullsをsum関数で代用』
本稿の「最大何人まで座れますか？」で扱ったSQLの別解と類題を置いてます。

13. OracleSQLパズル 『過去1時間の平均』
本稿の「2日前からの累計」で扱ったSQLの類題を置いてます。

14. OracleSQLパズル 『2日前のデータも出力』
本稿の「2日前の値」で扱ったSQLの類題を置いてます。