最近会社で聞かれて答えたことをまとめてみました。
Oracleに格納されている数値データを抽出しつつ、その数値の累計を計算したい、と聞かれました。
質問を題材としながら、分析ファンクションのウィンドウを中心にまとめてみました。
分析ファンクションのPARTITION句、ORDER BY句については既に知っている前提としています。
対象のデータベースは11gR1以上です。10gで使えるかどうかは確認できませんでした。動作確認は、先日作成した11gXEで行っています。
データと抽出イメージの例
以下のようなデータを想定します。
| ねん | がつ | にち | ことがら | にゅうきん | しゅっきん |
|---|---|---|---|---|---|
| 2019 | 07 | 01 | おこづかい | 1,000 | |
| 2019 | 07 | 07 | たなばたかい おかしだい | 300 | |
| 2019 | 07 | 15 | しおひがり おかしだい | 300 | |
| 2019 | 07 | 25 | おとまりかい おかしだい | 300 | |
| 2019 | 08 | 01 | おこづかい | 1,000 | |
| 2019 | 08 | 10 | ゲーム | 800 | |
| 2019 | 08 | 15 | りんじしゅうにゅう しんせきのおじさん | 1,000 |
このデータに、月別の残高を計算した項目を付けて抽出するといったケースです。抽出イメージは以下のようになります。
| ねん | がつ | にち | ことがら | にゅうきん | しゅっきん | ざんだか(追加項目) |
|---|---|---|---|---|---|---|
| 2019 | 07 | 01 | おこづかい | 1,000 | 1,000 | |
| 2019 | 07 | 07 | たなばたかい おかしだい | 300 | 700 | |
| 2019 | 07 | 15 | しおひがり おかしだい | 300 | 400 | |
| 2019 | 07 | 25 | おとまりかい おかしだい | 300 | 100 | |
| 2019 | 08 | 01 | おこづかい | 1,000 | 1,000 | |
| 2019 | 08 | 10 | ゲーム | 800 | 200 | |
| 2019 | 08 | 15 | りんじしゅうにゅう しんせきのおじさん | 1,000 | 1,200 |
回答
WITH DATA AS (
SELECT 0 AS 年, 0 AS 月, 0 AS 日, ' ' AS 事柄, 0 AS 入金, 0 AS 出金 FROM DUAL WHERE 0 = 1
UNION ALL SELECT 2019, 7, 1, 'おこづかい', 1000,NULL FROM DUAL
UNION ALL SELECT 2019, 7, 7, 'たなばたかい おかしだい', NULL, 300 FROM DUAL
UNION ALL SELECT 2019, 7, 15, 'しおひがり おかしだい', NULL, 300 FROM DUAL
UNION ALL SELECT 2019, 7, 25, 'おとまりかい おかしだい', NULL, 300 FROM DUAL
UNION ALL SELECT 2019, 8, 1, 'おこづかい', 1000,NULL FROM DUAL
UNION ALL SELECT 2019, 8, 10, 'ゲーム', NULL, 800 FROM DUAL
UNION ALL SELECT 2019, 8, 15, 'りんじしゅうにゅう しんせきのおじさん', 1000,NULL FROM DUAL
)
SELECT
DATA.年
, DATA.月
, DATA.日
, DATA.事柄
, DATA.入金
, DATA.出金
, '■' AS " "
, COALESCE ( 入金, 0 ) - COALESCE ( 出金, 0 )
AS 入出金
, SUM ( COALESCE ( 入金, 0 ) - COALESCE ( 出金, 0 ) )
OVER (
PARTITION BY DATA.年, DATA.月
ORDER BY DATA.日
ROWS UNBOUNDED PRECEDING
)
AS 残高
FROM
DATA
ORDER BY
DATA.年
, DATA.月
, DATA.日
解説
概説
WITH句の部分は、テストデータ作成です。
本質は分析関数"SUM"のOVER内に書かれている"ROWS UNBOUNDED PRECEDING"です。
"ROWS UNBOUNDED PRECEDING"がない場合を考えます。この場合、"PARTITION BY"で区切られた範囲の全レコードに対して"SUM"を行います。意味合いを考えると、レコードと同月の入出金合計が計算されます。
今回欲しい合計は累計です。言い換えると、レコードと同月の、月初からその日までの入出金合計です。前者との違いは、"SUM"の集計範囲が変わるだけです。この、"SUM"の集計範囲を変えるのが"ROWS UNBOUNDED PRECEDING"の役割です。
ウィンドウ
説明に先立ち、"ウィンドウ"を説明します。
"ウィンドウ"とは、分析関数が適用される集計範囲のことです。今回のケースでは"月初からその日まで"が集計範囲で、それを"ウィンドウ"と言います。
この例のように、レコードをある順序で並べて開始点と終了点で指定する形でウィンドウを指定します。
今回のケースでは、日付順に並べて、先頭(月初)からカレントレコード(その日)までという集計範囲を指定しています。
OVER内の"ORDER BY"
集計範囲の開始点や終了点を指定するため、ウィンドウ指定がある"SUM"には、OVER内に"ORDER BY"を指定します。
逆に、ウィンドウ指定がない"SUM"には、OVER内に"ORDER BY"を指定しません。
意味合いを考えれば、"PARTITION BY"で指定された範囲の全レコードを"SUM"するために、"ORDER BY"で並び順を指定する必要はないとわかるでしょう。
分析関数次第では"ORDER BY"が必要なものもあります。FIRST_VALUE()やLAST_VALUE()などがそうです。分析関数そのものに順序に意味がある場合、"ORDER BY"が必要です。
そして、分析関数そのものには順序に意味がなくても、ウィンドウ指定したい場合には、集計範囲を示すために"ORDER BY"が必要です。
ウィンドウの指定方法
ウィンドウの指定にはいろいろあります。大別すると以下のようになります。
- 基準指定
- ROW ( 行基準 )
- RANGE ( 論理オフセット基準 )
- 範囲指定方法
- BETWEEN ～ AND ～ ( 開始点と終了点を指定 )
- BETWEENなし ( 開始点のみ指定、終了点は"CURRENT ROW" )
- 開始点や終了点の指定
- 絶対指定
- UNBOUND PRECEDING ( 開始点でのみ使用可能で、先頭からを意味する )
- UNBOUND FOLLOWING ( 終了点でのみ使用可能で、末尾までを意味する )
- 相対指定
- n PRECEDING ( カレント行のnレコード前 )
- CURRENT ROW ( カレント行 )
- n FOLLOWING ( カレント行のnレコード後 )
- 絶対指定
基準指定は、行を基準とするか、同じ値を持つもの同士を一括りにして指定するか、を意味します。RANGEの使いどころがよくわからないので、説明は省略します。
範囲指定方法は、開始点と終了点の指定方法です。"BETWEEN"を省略すると、開始点のみを指定し、終了点には"CURRENT ROW"が指定されたことになります。
開始点や終了点の指定には、絶対指定と相対指定があります。絶対指定は、"PARTITION BY"で区切られた範囲の先頭や末尾を指定する方法です。相対指定は、カレントレコードやその前後のレコードを指定する方法です。
使いそうなウィンドウ
以下、よく使いそうな組み合わせを挙げてみました。
ROWS UNBOUNDED PRECEDING
上の例で使っているものです。パーティション範囲の中で、先頭から現在行までを集計するものです。"ROWS BETWEEN UNBOUND PRECEDING AND CURRENT ROW" の 省略記法です。
累計を集計する場合などが、この集計方法になると思います。
ROWS BETWEEN ( CURRENT ROW | 1 FOLLOWING ) AND UNBOUND FOLLOWING
前項の逆です。パーティション範囲の中で、現在行から末尾までを集計するものです。
前受残など、"残り"を合計する場合にこの集計方法を使用することもあると思います。当月を含めるなら"CURRNET ROW"、含めないなら"1 FOLLOWING"といった形になるでしょう。
ROWS BETWEEN n PRECEDING AND CURRENT ROW
投資の分析方法に、テクニカル分析＞トレンド分析＞移動平均線＞単純移動平均線(SMA)と呼ばれるものがあります。
単純移動平均線は、チャートに表示する線の一つで、"ある一定期間の終値の平均値"をグラフに落とし込んだものです。例えば、チャートを日足で表示させた場合の、5日単純移動平均線とは、直近5日間の終値の平均値を繋ぎ合わせたものを意味します。
それぞれの用語の意味はさておき、5日単純移動平均線を描くうえで必要な値は、過去5件のレコード平均です。これは
AVG ( 終値 ) OVER ( ORDER BY 日 ROWS BETWEEN 4 PRECEDING AND CURRENT ROW )
といった感じになります。現在行を含んで直前5レコードなので、4レコード前から現在行まで、となります。
投資のテクニカル分析は未来予測のため、終了点に今 ( CURRENT ROW ) を使うことが多いようです。そして、上記の例のように"過去n件のレコード"を AVG や SUM する計算が頻出します。
- AVG = 単純移動平均(SMA)などで登場
- SUM = 相対力指数(RSI)の「上昇幅の累計」「下落幅の累計」などで登場
平均する際も"単純な相加平均"("単純移動平均")だけでありません。"指数平滑移動平均"や"加重移動平均"といったように、直近に重きを置く計算方法もあります。こういった込み入った計算をする際には、ユーザー定義集計関数を作成することになるだろうと思います。
利用する側の立場ではこういった式を使うことはありません。しかし、プログラムを作る立場ではこういった式を使うことになります。こういったときに、サクサクとSQLを書けるようになりたいものです。
まとめ
すぐ思いつく使用ケースは、今のところ上記のようなケースです。しかし、もっと使いどころはあるだろうと思います。使える場面が来たとき、使えることに気付けることが大切です。気づかないと、集計結果となる副問合せをJOINするような、非効率なSQLになってしまいます。
纏める中で見直したものの、"RANGE"の使いどころ、"BETWEEN"なしの"CURRENT ROW"の使いどころはわかりませんでした。
”RANGE"の使いどころは、LAST_VALUEの説明に書かれています。ただ、これを必要とするようなシチュエーションは思いつかなかったです。
多分、例の3つ目のSELECTのように、"ORDER BY"を追加することで明示的な形でLAST_VALUEを決定づける書き方をすることになるだろうと思います。そして、そのようにする限り、RANGEを使うことはなさそうです。
"BETWEEN"なしの"CURRENT ROW"は、"BETWEEN CURRENT ROW AND CURRENT ROW"？そうするとカレント行そのままでは？
"ROWS CURRENT ROW"は意味がないかもしれません。しかし"RANGE CURRENT ROW"なら"カレント行と同じ値を持つレコードたちの中で"という計算になります。これなら意味があるかもしれません。ただ、身近なところでこれを有効活用できるケースはまだ思いつきません。