2日前からの累計
次は移動累計を求めるSQLのアレンジ問題です。「分析関数の衝撃(中編)」では、「相関サブクエリで行と行を比較する」の移動累計を求めるSQLを扱いましたが、これをアレンジして、2日前からの累計を求めるSQLを考えます。
まずは、値を少し変更したテーブルのデータと、出力結果を考えます。
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として扱います)。
手続き型の言語であれば、
- prc_dateの昇順にソート
- 各値を、配列に保存
- 配列の添字の最小値からループ
- 配列の添字と値を元に、2日前からの累計を求める
といった処理を行うと思いますが、分析関数を使った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のイメージはこうなります。
分析関数の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となります。
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として扱います。
まずは、値を少し変更したテーブルのデータと、出力結果を考えます。
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 |
手続き型の言語であれば、
- prc_dateの昇順にソート
- 各値を、配列に保存
- 配列の添字の最小値からループ
- 配列の添字と値を元に、2日前のprc_amtを求める
といった処理を行うと思いますが、分析関数を使った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のイメージはこうなります。
分析関数の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となります。
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を扱います。
参考資料
- CodeZine 『SQLで数列を扱う』 ミック 著、2007年3月
- CodeZine 『相関サブクエリで行と行を比較する』 ミック 著、2007年2月
- OTN Japan 『Oracle8i データウェアハウス』
- OTN Japan 『分析ファンクション』
- US-OTN 『Analytic Functions』
- IBM 『SQL Reference Vol.1』
- 『SQLクックブック』 Anthony Molinaro 著、木下哲也・有限会社 福龍興業 訳、オライリー・ジャパン、2007年1月
- OracleSQLパズル 『大きさNのリージョンを求める(境界なし)』
- OracleSQLパズル 『大きさNのリージョンを求める(境界あり)』
- OracleSQLパズル 『最大のリージョンを求める(境界なし)』
- OracleSQLパズル 『前後の値で分岐』
- OracleSQLパズル 『ignore nullsをsum関数で代用』
- OracleSQLパズル 『過去1時間の平均』
- OracleSQLパズル 『2日前のデータも出力』