全銘柄、全期間の日ごとの各種平均値、標準偏差を計算するSQL
stockdb のデータをもとに、全銘柄、全期間の日ごとの各種平均値、標準偏差を計算するSQLを書いたのでメモ。
- 終値、出来高、価格変動率のそれぞれについて、過去3,5,10,15,25,50,75日の平均値と標準偏差を一気に算出します。
- それなりに時間はかかります。確認した環境だと1時間くらいでした。
- Window関数すごい。
-- 市場が開いている日の一覧 CREATE MATERIALIZED VIEW days as ( WITH ds AS ( SELECT distinct date FROM rates ), x AS ( SELECT date, row_number() OVER (ORDER BY date) AS index, lag(date, 1, null) OVER (ORDER BY date) AS prev, lag(date, 3, null) OVER (ORDER BY date) AS before_3_days, lag(date, 5, null) OVER (ORDER BY date) AS before_5_days, lag(date, 10, null) OVER (ORDER BY date) AS before_10_days, lag(date, 15, null) OVER (ORDER BY date) AS before_15_days, lag(date, 25, null) OVER (ORDER BY date) AS before_25_days, lag(date, 50, null) OVER (ORDER BY date) AS before_50_days, lag(date, 75, null) OVER (ORDER BY date) AS before_75_days FROM ds ORDER BY date desc ) SELECT * FROM x WHERE prev IS NOT NULL ); -- 出来高0(volume=0)のデータが抜けているのでそれをを補完したビューを作る CREATE MATERIALIZED VIEW rates_filled AS ( WITH all_stock_and_days as ( SELECT d.*, s.id as stock_id FROM days as d, stocks as s ), x as ( SELECT a.stock_id, a.date, CASE WHEN r.date IS NOT NULL THEN r.date ELSE (SELECT max(date) FROM rates WHERE stock_id = a.stock_id and date <= a.date ) END as actual FROM all_stock_and_days AS a LEFT JOIN rates as r ON a.stock_id = r.stock_id and a.date = r.date ) SELECT x.stock_id, x.date, CASE WHEN x.actual = x.date THEN r.open ELSE r.close END as open, CASE WHEN x.actual = x.date THEN r.close ELSE r.close END as close, CASE WHEN x.actual = x.date THEN r.high ELSE r.close END as high, CASE WHEN x.actual = x.date THEN r.low ELSE r.close END as low, CASE WHEN x.actual = x.date THEN r.volume ELSE 0 END as volume FROM x LEFT JOIN rates as r ON x.stock_id = r.stock_id and x.actual = r.date ); CREATE UNIQUE INDEX rates_filled_stock_id_date_index ON rates_filled (stock_id, date); -- 前日からの価格変動率を計算 CREATE MATERIALIZED VIEW ratios AS ( WITH x AS ( SELECT d.*, s.id FROM days AS d, stocks AS s ) SELECT x.id as stock_id, x.date, (r1.close-r2.close)/r2.close as ratio FROM x LEFT JOIN rates_filled as r1 on x.id = r1.stock_id AND r1.date = x.date LEFT JOIN rates_filled as r2 on x.id = r2.stock_id AND r2.date = x.prev WHERE r2.close IS NOT NULL ); CREATE UNIQUE INDEX ratios_stock_id_date_index ON ratios (stock_id, date); -- 全銘柄、全期間の日ごとの各種平均値、標準偏差をを計算 -- 終値、出来高、価格変動率のそれぞれについて、過去3,5,10,15,25,50,75日の平均値と標準偏差を算出する CREATE MATERIALIZED VIEW ma AS ( SELECT r.stock_id, r.date, r.volume, avg(r.close) OVER from_3_days_ago as avg_close_3days, stddev(r.close) OVER from_3_days_ago as sd_close_3days, avg(r.close) OVER from_5_days_ago as avg_close_5days, stddev(r.close) OVER from_5_days_ago as sd_close_5days, avg(r.close) OVER from_10_days_ago as avg_close_10days, stddev(r.close) OVER from_10_days_ago as sd_close_10days, avg(r.close) OVER from_15_days_ago as avg_close_15days, stddev(r.close) OVER from_15_days_ago as sd_close_15days, avg(r.close) OVER from_25_days_ago as avg_close_25days, stddev(r.close) OVER from_25_days_ago as sd_close_25days, avg(r.close) OVER from_50_days_ago as avg_close_50days, stddev(r.close) OVER from_50_days_ago as sd_close_50days, avg(r.close) OVER from_75_days_ago as avg_close_75days, stddev(r.close) OVER from_75_days_ago as sd_close_75days, avg(ra.ratio) OVER from_3_days_ago as avg_ratio_3days, stddev(ra.ratio) OVER from_3_days_ago as sd_ratio_3days, avg(ra.ratio) OVER from_5_days_ago as avg_ratio_5days, stddev(ra.ratio) OVER from_5_days_ago as sd_ratio_5days, avg(ra.ratio) OVER from_10_days_ago as avg_ratio_10days, stddev(ra.ratio) OVER from_10_days_ago as sd_ratio_10days, avg(ra.ratio) OVER from_15_days_ago as avg_ratio_15days, stddev(ra.ratio) OVER from_15_days_ago as sd_ratio_15days, avg(ra.ratio) OVER from_25_days_ago as avg_ratio_25days, stddev(ra.ratio) OVER from_25_days_ago as sd_ratio_25days, avg(ra.ratio) OVER from_50_days_ago as avg_ratio_50days, stddev(ra.ratio) OVER from_50_days_ago as sd_ratio_50days, avg(ra.ratio) OVER from_75_days_ago as avg_ratio_75days, stddev(ra.ratio) OVER from_75_days_ago as sd_ratio_75days, avg(r.volume) OVER from_3_days_ago as avg_volume_3days, stddev(r.volume) OVER from_3_days_ago as sd_volume_3days, avg(r.volume) OVER from_5_days_ago as avg_volume_5days, stddev(r.volume) OVER from_5_days_ago as sd_volume_5days, avg(r.volume) OVER from_10_days_ago as avg_volume_10days, stddev(r.volume) OVER from_10_days_ago as sd_volume_10days, avg(r.volume) OVER from_15_days_ago as avg_volume_15days, stddev(r.volume) OVER from_15_days_ago as sd_volume_15days, avg(r.volume) OVER from_25_days_ago as avg_volume_25days, stddev(r.volume) OVER from_25_days_ago as sd_volume_25days, avg(r.volume) OVER from_50_days_ago as avg_volume_50days, stddev(r.volume) OVER from_50_days_ago as sd_volume_50days, avg(r.volume) OVER from_75_days_ago as avg_volume_75days, stddev(r.volume) OVER from_75_days_ago as sd_volume_75days FROM rates_filled as r LEFT JOIN ratios AS ra ON ra.stock_id = r.stock_id AND ra.date = r.date WINDOW from_3_days_ago AS ( PARTITION BY r.stock_id ORDER BY r.stock_id, r.date desc ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING ), from_5_days_ago AS ( PARTITION BY r.stock_id ORDER BY r.stock_id, r.date desc ROWS BETWEEN CURRENT ROW AND 4 FOLLOWING ), from_10_days_ago AS ( PARTITION BY r.stock_id ORDER BY r.stock_id, r.date desc ROWS BETWEEN CURRENT ROW AND 9 FOLLOWING ), from_15_days_ago AS ( PARTITION BY r.stock_id ORDER BY r.stock_id, r.date desc ROWS BETWEEN CURRENT ROW AND 14 FOLLOWING ), from_25_days_ago AS ( PARTITION BY r.stock_id ORDER BY r.stock_id, r.date desc ROWS BETWEEN CURRENT ROW AND 24 FOLLOWING ), from_50_days_ago AS ( PARTITION BY r.stock_id ORDER BY r.stock_id, r.date desc ROWS BETWEEN CURRENT ROW AND 49 FOLLOWING ), from_75_days_ago AS ( PARTITION BY r.stock_id ORDER BY r.stock_id, r.date desc ROWS BETWEEN CURRENT ROW AND 74 FOLLOWING ) order by r.date desc ); CREATE UNIQUE INDEX ma_stock_id_date_index ON ma(stock_id, date);
文字列を数値に変換する
「cast( <カラム> as <数値型> )」でテーブル内の文字列型のカラムを数値に変換して取り出すことができます。
↓のようなテーブルがあるとして、
$ db2 "describe table test" Column Type Type name schema name Length Scale Nulls ------------------------------ --------- ------------------ -------- ----- ------ STR SYSIBM VARCHAR 20 0 No 1 record(s) selected.
$ db2 "select * from test" STR -------------------- 1 2 02 x 5 record(s) selected.
テーブルのstr列をBIGINTに変換して取り出してみます。
$ db2 "select str, cast( str as BIGINT ) from test" STR 2 -------------------- -------------------- 1 1 2 2 02 2 SQL0420N Invalid character found in a character string argument of the function "BIGINT". SQLSTATE=22018
変換できな場合はエラーになるのか。ふむ。
参考:
Merge
Mergeを使うと、
- 複数のテーブルを比較して、
- マッチする行があれば、更新
- 行がなければ挿入
といった操作ができます。
例
以下は、
- テーブル「test」と「test2」を比較して(カラムのラインナップは同じ)
- テーブル「test」と「test2」と同じ「name」を持つ行があれば、「test2」の「value」を「test」の「value」と同期
- テーブル「test」と「test2」と同じ「name」を持つ行がなければ、「test2」に同じ「name」「value」を持つ行を挿入
するサンプルです。
MERGE INTO test2 AS target -- 更新するテーブル USING test AS src ON src.name=target.name -- 比較元のテーブルと結合条件 WHEN MATCHED THEN UPDATE SET value = src.value -- 対応する行があった場合の操作 WHEN NOT MATCHED THEN INSERT (name,value) VALUES (src.name, src.value) -- 対応する行がない場合の操作
実行結果は次の通り。まずは、挿入前のテーブル。
$ db2 "select * from test" ID NAME VALUE -------------------- -------------------- -------------------- 7 a aaa 8 b bbb 2 record(s) selected. $ db2 "select * from test2" ID NAME VALUE -------------------- -------------------- -------------------- 10 c ccc 9 b xxx 2 record(s) selected.
マージ実行。
$ db2 "MERGE INTO test2 AS target USING test AS src ON src.name=target.name WHEN MATCHED THEN UPDATE SET value = src.value WHEN NOT MATCHED THEN INSERT (name,value) VALUES (src.name, src.value)"
DB20000I The SQL command completed successfully.
実行結果です。
$ db2 "select * from test" ID NAME VALUE -------------------- -------------------- -------------------- 7 a aaa 8 b bbb 2 record(s) selected. $ db2 "select * from test2" ID NAME VALUE -------------------- -------------------- -------------------- 10 c ccc 11 a aaa 9 b bbb 3 record(s) selected.
再帰SQLの無限ループを指定階層辿ったら止めるようにする
昨日の続き。User's Forum for DB2 Japan - タイムスタンプを持つテーブルをyear,monthでgroupingしたも...を参考に、共通表式を使った再帰SQLの無限ループを指定階層辿ったら止めるようにしてみます。戦略的には、
- 再帰して見つけたデータを格納する中間表(X表)に、再帰の深さを格納する「depth」を追加。
- depthは最初は0として、配下データの探索の際に「親のdepth+1」を設定する。
- その上で、配下の探索条件として「深さが規定値より小さい」という条件をつける。
- →これにより、一定の階層以下の配下データは中間表に追加されなくなり、それ配下のデータの探索も行なわれません。
とすればOK。昨日のSQLをベースにこの対応を入れると以下のようなSQLになります。
WITH X( from, to, depth ) AS ( SELECT from, to, 0 FROM file_relation WHERE from = 'a' UNION ALL SELECT file_relation.from, file_relation.to, X.depth+1 FROM X, file_relation WHERE file_relation.from = X.to AND X.depth < 10 ) SELECT from, to, depth FROM X"
無限ループするデータに対して
$ db2 "select * from file_relation" FROM TO -------------------- -------------------- a b a c a d c e c f d g f a x y x z 9 record(s) selected.
実行。ちゃんと10階層で停止しています。あと、「このSQLだと無限ループになる場合があるよ」の警告も消えてます。
$ db2 "WITH X( from, to, depth ) AS ( SELECT from, to, 0 FROM file_relation WHERE from = 'a' UNION ALL SELECT file_relation.from, file_relation.to, X.depth+1 FROM X, file_relation WHERE file_relation.from = X.to AND X.depth < 10 ) SELECT from, to, depth FROM X" FROM TO DEPTH -------------------- -------------------- ----------- a b 0 a c 0 a d 0 c e 1 c f 1 d g 1 f a 2 a b 3 a c 3 a d 3 c e 4 c f 4 d g 4 f a 5 a b 6 a c 6 a d 6 c e 7 c f 7 d g 7 f a 8 a b 9 a c 9 a d 9 c e 10 c f 10 d g 10 27 record(s) selected.
後は必要な属性をdistinctで取り出せばOK。
$ db2 "WITH X( from, to, depth ) AS ( SELECT from, to, 0 FROM file_relation WHERE from = 'a' UNION ALL SELECT file_relation.from, file_relation.to, X.depth+1 FROM X, file_relation WHERE file_relation.from = X.to AND X.depth < 10 ) SELECT DISTINCT to FROM X" TO -------------------- a b c d e f g 7 record(s) selected.
まぁ、この戦略だと
- 辿る階層をいくつにするのか?
- 循環している場合、配下要素数が少なくても(規定数までは探索を行なうので)無駄に時間がかかるのでは?
とかいう懸念はあるわけですが。とはいえ、安全装置としてこういう仕組みは入れておいた方が良さそうですね。
共通表式を使った再帰SQL
DB2の共通表式中のFROM節では共通表自体を指定することができ、このとき共通表式は再帰共通表式となります。再帰共通表式を使うことで、階層構造を持つテーブルデータを再帰的に探索したりできます。
例
例えば、以下のようなファイルの関連があるとして、
これの関連情報を↓のようなテーブルで保持するとします。
create table file_relation ( from VARCHAR(20) NOT NULL, to VARCHAR(20) NOT NULL, PRIMARY KEY(from,to) ) in foo
$ db2 "select * from file_relation" FROM TO -------------------- -------------------- a b a c a d c e c f d g x y x z 8 record(s) selected.
ここで、以下の再帰共通表式を使うと「"a"配下のファイル一覧を再帰的に取り出す」ことができます。
WITH X(from,to) AS ( SELECT from, to FROM file_relation WHERE from = 'a' UNION ALL SELECT file_relation.from, file_relation.to FROM X, file_relation WHERE file_relation.from = X.to ) SELECT DISTINCT to FROM X"
実行結果です。「このSQLだと無限ループになる場合があるよ」という内容の警告が表示されていますが、a配下のファイルをちゃんと取得できています。
TO -------------------- SQL0347W The recursive common table expression "XFYADMIN.X" may contain an infinite loop. SQLSTATE=01605 b c d e f g 6 record(s) selected with 1 warning messages printed.
c,x,bの配下もちゃんととれます。
$ db2 "WITH X(from,to) AS ( SELECT from, to FROM file_relation WHERE from = 'x' UNION ALL SELECT file_relation.from, file_relation.to FROM X, file_relation WHERE file_relation.from = X.to ) SELECT DISTINCT to FROM X" TO -------------------- y z 2 record(s) selected with 1 warning messages printed. $ db2 "WITH X(from,to) AS ( SELECT from, to FROM file_relation WHERE from = 'c' UNION ALL SELECT file_relation.from, file_relation.to FROM X, file_relation WHERE file_relation.from = X.to ) SELECT DISTINCT to FROM X" TO -------------------- e f 2 record(s) selected with 1 warning messages printed. $ db2 "WITH X(from,to) AS ( SELECT from, to FROM file_relation WHERE from = 'b' UNION ALL SELECT file_relation.from, file_relation.to FROM X, file_relation WHERE file_relation.from = X.to ) SELECT DISTINCT to FROM X" TO -------------------- 0 record(s) selected with 1 warning messages printed.
内部的な動作は@IT - オブジェクト指向、Javaを取り入れた新しい業界標準「SQL99」詳細解説 が詳しいのですが、
- 1.共通表式内の「SELECT from, to FROM file_relation WHERE from = 'a' 」で「'a'と関連づけられているファイル一覧」を取り出して、X表に追加する。
- 2.さらに次に続く「SELECT file_relation.from, file_relation.to FROM X, file_relation WHERE file_relation.from = X.to」で、1で取り出したファイル一覧に関連づけられているファイル一覧を取り出し、X表に追加する。
- 3.1〜2を子がなくなるまで繰り返す。
- 4.最後に、X表から関連先ファイル名を取り出す。
となるらしい。ふむふむ。
循環するとどうなるか。
↑のSQLでは循環をチェックしていないので、データを以下のような構造にすると、さくっと無限ループします。
$ db2 "select * from file_relation" FROM TO -------------------- -------------------- a b a c a d c e c f d g f a x y x z 9 record(s) selected.
$ db2 "WITH X(from,to) AS ( SELECT from, to FROM file_relation WHERE from = 'a' UNION ALL SELECT file_relation.from, file_relation.to FROM X, file_relation WHERE file_relation.from = X.to ) SELECT DISTINCT to FROM X" ・・・帰ってこない・・・
回避策を30分くらい考えたけどさっぱり思いつきません。無念..。一般的にはどうするんだろー。「最大XX階層まで辿る」とか? > 今度ググる。
共通表式
共通表式を使うと、検索結果を名前付きの表として定義して、後に続くselect文で参照することができます。
例えば次のようなSQLは、
select * from ( select distinct * from A ) as X order by case when id is null then 0 else 1 end, id
共通表式を使って以下のように書くことができます。
with X as ( select distinct * from A ) select * from X order by case when id is null then 0 else 1 end, id
表の参照が1回だといまいちありがたみがわかりませんが、複数回使うような場合に便利そう。
なお、残念ながらMySQLでは未対応のようです。手近にあったMySQL 4.1.22では動作せず、DB2 V9.1で確認しました。
今日のバグ
↓みたいなテーブルがあって、
create table A ( id BIGINT NOT NULL AUTO_INCREMENT, name VARCHAR(20) NOT NULL DEFAULT '', PRIMARY KEY(id) ); create table B ( id BIGINT NOT NULL AUTO_INCREMENT, a_id BIGINT NOT NULL, name VARCHAR(20) NOT NULL DEFAULT '', FOREIGN KEY (a_id) REFERENCES A(id) ON DELETE CASCADE, PRIMARY KEY(id) );
Aテーブルのデータ一覧を取得する機能を作った訳です。この一覧APIでは絞り込み条件が指定できて、
- Aの属性での絞り込み
- Bの属性での絞り込み(指定された属性を持つBと関連づけられたAのみを返す)
- および、条件のAND,ORでの結合
をサポートする。
ここで、Bの属性での検索を以下のように行なっていたのだけど、これではダメなことに今日気付いた・・・。orz
select distinct -- 重複行をはじく A.id, -- 必要なAの属性を列挙 A.name from A join B on B.a_id = A.id -- AテーブルとBテーブルをjoin where B.name = 'aa'; -- 検索条件を並べる
テーブルに↓のようなデータが入っているとすると
mysql> select * from A; +----+------+ | id | name | +----+------+ | 1 | aaa | | 2 | bbb | | 3 | ccc | +----+------+ 3 rows in set (0.00 sec) mysql> select * from B; +----+------+------+ | id | a_id | name | +----+------+------+ | 1 | 1 | aa | | 2 | 2 | aa | | 3 | 2 | bb | +----+------+------+ 3 rows in set (0.00 sec)
一見うまいこと動いているように見えるけど
mysql> select distinct A.id, A.name from A join B on B.a_id = A.id where B.name = 'aa'; +----+------+ | id | name | +----+------+ | 1 | aaa | | 2 | bbb | +----+------+ 2 rows in set (0.01 sec) mysql> select distinct A.id, A.name from A join B on B.a_id = A.id where B.name = 'bb'; +----+------+ | id | name | +----+------+ | 2 | bbb | +----+------+ 1 row in set (0.00 sec)
検索条件にAの属性を追加すると期待と違う結果になる。
mysql> select distinct A.id, A.name from A join B on B.a_id = A.id where A.name = 'ccc' OR B.name = 'bb'; +----+------+ | id | name | +----+------+ | 2 | bbb | +----+------+ 1 row in set (0.00 sec) # あれ? cccも含まれるハズなんだが!
というのも、AとBを内部結合しているのでBから参照されていないAの行がJOINした結果に含まれてないわけですよ。試しにdistinctと検索条件を外して内部結合だけ行なった結果は以下の通り。ここから、絞り込みと重複行の排除をするので当然'ccc'の行は返されない。
mysql> select * from A join B on B.a_id = A.id; +----+------+----+------+------+ | id | name | id | a_id | name | +----+------+----+------+------+ | 1 | aaa | 1 | 1 | aa | | 2 | bbb | 2 | 2 | aa | | 2 | bbb | 3 | 2 | bb | +----+------+----+------+------+ 3 rows in set (0.00 sec)
まぁ、ANDやORで結合しなくてもAの属性で検索すればわかるはずなんですが、その場合そもそもJOINをしない実装にしていたので気付かなかったのです。むー。とりあえず、AとBの属性を混ぜての検索は今回は使わない(=急いで直さなくてもいい)ので、開発内試験期間にでもちょろっと直そうかな。
対策案
# その1 mysql> select distinct A.id, A.name from A left join B on B.a_id = A.id where A.name = 'ccc' or B.name = 'bb'; +----+------+ | id | name | +----+------+ | 2 | bbb | | 3 | ccc | +----+------+ 2 rows in set (0.00 sec) # その2 mysql> select A.id, A.name from A where A.name = 'ccc' or exists ( select id from B where B.a_id = A.id and B.name = 'bb'); +----+------+ | id | name | +----+------+ | 2 | bbb | | 3 | ccc | +----+------+ 2 rows in set (0.00 sec)
一般的にはどうするんだろう。
