data-get.comより株価データを買って、家庭内サーバに株価データベースを構築して遊んでいる。
最低限、4本値と、会社分割情報だけ入れられたらいいかなあと思っている。
mysql> describe tbl_dg_devide;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| s | char(4) | NO | PRI | | |
| d | char(8) | NO | PRI | | |
| f | float | YES | | NULL | |
| t | float | YES | | NULL | |
| mul | float | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
5 rows in set (0.01 sec)
分割情報を保持するテーブルは↑のような感じ
s はセキュリティコード（証券コード）
dは日付文字列にした。データタイム型にするとload data する時に面倒なのでやってない。
f は分割前～に当たり t ～個の株式を割り当てるみたいな。
f と t はあまり必要ない。
mul が重要で、分割修正乗数というべきもの。
とりあえず、Data get の devide.csv はそのままload data できる構造にした。
ところで、data-getの株価は分割情報は別テーブルだ。
これはこれで便利なのだけど。
普通バックテストや過去データの検証に必要なのは分割を調整した後の4本値だ。
だから
ふつうの4本値
＋
分割情報から
⇒ 分割を調整した後の4本値を記録した時系列データを
作らないといけない。
で、やり方にはいろいろあるが、SQLでできるのかどうかを考えてみる。
12.19.1 GROUP BY (集約) 関数
をみてうーんと考えてみたけどもどうにもできそうにない。
できない原因は集約関数にEXCELでいうPRODUCT的な動きをする関数や
Perlでいうmap 関数のようなものが無いからだと思う。
また集約関数を自作できそうな情報もなかったので、これはたぶん無理か難しいんだろうな。
でも、SQLで修正乗数のPRODUCTを返す数式なら返す事ができるなあと気が付いた。
まずソフトバンクの分割を調べてみる
mysql> select * from tbl_dg_devide where s="9984";
+------+----------+------+------+---------+
| s | d | f | t | mul |
+------+----------+------+------+---------+
| 9984 | 19960326 | 1 | 1.4 | 0.71429 |
| 9984 | 19960925 | 1 | 1.4 | 0.71429 |
| 9984 | 19970326 | 1 | 1.3 | 0.76923 |
| 9984 | 20000425 | 1 | 3 | 0.33333 |
| 9984 | 20051228 | 1 | 3 | 0.33333 |
+------+----------+------+------+---------+
5 rows in set (0.01 sec)
ソフトバンクの1996年3月2X日の終値を表示
mysql> select d, s,c from tbl_dg_ohlc where s="9984" and d LIKE "1996032%";
+----------+------+-------+
| d | s | c |
+----------+------+-------+
| 19960321 | 9984 | 26800 |
| 19960322 | 9984 | 26000 |
| 19960325 | 9984 | 26300 |
| 19960326 | 9984 | 19200 |
| 19960327 | 9984 | 19800 |
| 19960328 | 9984 | 20200 |
| 19960329 | 9984 | 21800 |
+----------+------+-------+
7 rows in set (0.01 sec)
ソフトバンクの1996年3月2X日の終値を表示↑の二つの表から修正値の数式を出力させてみる。
mysql> select a.s,tbl_dg_ohlc.d,GROUP_CONCAT(a.mul separator '*')
from (select s,d,mul from tbl_dg_devide where s="9984" ) as a,
tbl_dg_ohlc
where a.s = tbl_dg_ohlc.s and a.d > tbl_dg_ohlc.d
and tbl_dg_ohlc.d LIKE "1997032%" group by tbl_dg_ohlc.s,tbl_dg_ohlc.d ;
+------+----------+-----------------------------------+
| s | d | GROUP_CONCAT(a.mul separator '*') |
+------+----------+-----------------------------------+
| 9984 | 19970321 | 0.33333*0.76923*0.33333 |
| 9984 | 19970324 | 0.33333*0.33333*0.76923 |
| 9984 | 19970325 | 0.33333*0.33333*0.76923 |
| 9984 | 19970326 | 0.33333*0.33333 |
| 9984 | 19970327 | 0.33333*0.33333 |
| 9984 | 19970328 | 0.33333*0.33333 |
+------+----------+-----------------------------------+
6 rows in set (0.44 sec)
どうだろう。なぜか、CONCATの順番が入れ替わったりしているが概ね機能してそうな気がする。
おしいところまではできるのだが、あと一押し eval()関数が欲しいなあ。
あ、これだと分割情報の適用されないところが出てくるからRIGHT JOIN した方がいいのか。
select b.s,b.d, IFNULL(GROUP_CONCAT(a.mul separator '*'),1)
from
(select s,d,mul from tbl_dg_devide where s="9984" ) as a
right join
(select * from tbl_dg_ohlc where s="9984") as b
on a.s = b.s and a.d > b.d group by b.s,b.d ;
