随分考えたのですが、計算することが出来なかったのでSQL文をご教授ください。
http://stocks.finance.yahoo.co.jp/stocks/history/?code=1301.T
MYSQLに上記テーブルと同様のデータがあるとして、SQLにて移動平均(25日)とボリンジャーバンド(+2σ,+1σ,-1σ,-2σ)を計算してください。
■ほしいデータ
・ある1つの銘柄の移動平均(25日)とボリンジャーバンドの連続データ
・銘柄ごとの最新の1件の移動平均(25日)とボリンジャーバンドデータ
・データテーブルのカラム名は(id,date,start,high,low,end2,volume,end)を想定。
・データテーブルのstart,high,low,end2,volumeは使用しないと思います。
・テーブル構成の変更は不可。
(参考)
仕様:MYSQL5.1
日付:UNIXTIMEのINT型
[ご要望があればsql形式のデータをお渡しします。]
過去質問の http://q.hatena.ne.jp/1265472962#a990879 をベースにして作成してみた
テーブル名は今回の質問IDでつけた(tableAではどのプログラム用のテストデータか判らなくなるので勝手につけた)ので、それぞれ直しが必要
T1276009550A 銘柄の名称が入っているテーブル(id, name) T1276009550B 株価データが入っているテーブル(id, date, start, high, low, end2, volume, end)
呼び出し方は下記のいずれか
ある1つの銘柄の移動平均(25日)とボリンジャーバンドの連続データ http://localhost/a.php?id=銘柄の番号 銘柄ごとの最新の1件の移動平均(25日)とボリンジャーバンドデータ http://localhost/a.php
<?php echo '<p>動作開始</p><hr />'; // // データベース接続 $con = mysql_connect('localhost', 'root') or die ('MySQL接続エラー'); mysql_select_db('test') or die ('データベース接続エラー:' . mysql_error($con)); // // 出力用文字コード指定(要不要は環境による) mysql_query('SET NAMES SJIS;') or die ('文字コード指定エラー' . mysql_error($con)); // // ストアドファンクション作成 mysql_query('DROP FUNCTION IF EXISTS fn1') or die('ストアド作成準備エラー:' . mysql_error($con)); mysql_query(' CREATE FUNCTION fn1 (w_id INT, w_date INT) RETURNS INT READS SQL DATA RETURN ( SELECT date FROM T1276009550B WHERE id = w_id AND date <= w_date ORDER BY date DESC LIMIT 24, 1 )') or die('ストアド作成エラー:' . mysql_error($con)); // // SQL作成 - パラメータとしてidがあればidで指定された銘柄、無ければ全銘柄の最新日付 if (isset($_GET['id'])) { $where = 't1.id =' . $_GET['id']; } else { $res = mysql_query('SELECT date FROM T1276009550B ORDER BY date DESC LIMIT 0,1') or die('最新日付取得エラー:' . mysql_error($con)); $where = 't1.date =' . mysql_result($res,0,0); } $sqlstr = " SELECT t1.id , (SELECT DISTINCT name FROM T1276009550A WHERE id = t1.id) AS name , FROM_UNIXTIME(t1.date,'%Y-%m-%d') date2 , t1.end , t1.end + STDDEV(t2.end) * 2 SP2 , t1.end + STDDEV(t2.end) SP1 , t1.end - STDDEV(t2.end) SM1 , t1.end - STDDEV(t2.end) * 2 SM2 FROM T1276009550B AS t1 LEFT JOIN T1276009550B AS t2 USING(id) WHERE $where AND t2.date BETWEEN fn1(id, t1.date) AND t1.date GROUP BY t1.id, t1.date ORDER BY id, t1.date DESC "; // 生成されたSQLの確認用(本番では不要) echo '<br />' . str_replace("\n", '<br />', $sqlstr) . '<br />'; // // テーブルの形で出力 $res = mysql_query($sqlstr) or die('データ取得エラー'); echo '<table border="1">'; echo '<thead><tr><th>id</th><th>銘柄</th><th>日付</th><th>終値</th><th>+2σ</th><th>+1σ</th><th>-1σ</th><th>-2σ</th></tr></thead>'; echo '<tbody>'; while ($rows = mysql_fetch_array($res, MYSQL_NUM)) { echo '<tr><td>' . join('</td><td>', $rows); } echo '</tbody></table>'; // // 終了処理 echo '<hr /><p>動作終了</p>'; mysql_close(); ?>
3000ポイント払うくらいでしたら
この本を買って参考にされるのはいかがでしょう
MySQL5.0ベースのSQLですし。
http://www.amazon.co.jp/product-reviews/4774133132
>特に応用編は秀逸な例題が満載されていて
>為替データの移動平均やボリンジャーバンドなどの
>テクニカル分析には「やられた!!」という感じ。
>さらに、データの自動作成をSQLだけで表現するとは驚き(◎o◎)
その本も先週買ったのですが、テーブル構成が違うためわたしには使い物になりませんでしたTT
idカラムが日付ごとに+1されていくと仮定して作成しました。
ご確認ください。
------------------------
select t1.id,
avg(t2.end) as average,
avg(t2.end) + 2 * stddev(t2.end) as p2sigma,
avg(t2.end) + stddev(t2.end) as p1sigma,
avg(t2.end) - stddev(t2.end) as n1sigma,
avg(t2.end) - 2 * stddev(t2.end) as n2sigma
from data_table t1,
data_table t2
where t2.id <= t1.id
and t2.id > t1.id - 25
group by t1.id
order by t1.id desc
------------------------
(!注意!)
当方、株式のテクニカル分析の知識は皆無なので
出力結果がOKかどうかが判断できてません。
説明不足で非常に申し訳ないのですが・・・idは銘柄コードです。
説明文のURLページでいえば1301です。
なので、idと日付で一意となります。
過去質問の http://q.hatena.ne.jp/1265472962#a990879 をベースにして作成してみた
テーブル名は今回の質問IDでつけた(tableAではどのプログラム用のテストデータか判らなくなるので勝手につけた)ので、それぞれ直しが必要
T1276009550A 銘柄の名称が入っているテーブル(id, name) T1276009550B 株価データが入っているテーブル(id, date, start, high, low, end2, volume, end)
呼び出し方は下記のいずれか
ある1つの銘柄の移動平均(25日)とボリンジャーバンドの連続データ http://localhost/a.php?id=銘柄の番号 銘柄ごとの最新の1件の移動平均(25日)とボリンジャーバンドデータ http://localhost/a.php
<?php echo '<p>動作開始</p><hr />'; // // データベース接続 $con = mysql_connect('localhost', 'root') or die ('MySQL接続エラー'); mysql_select_db('test') or die ('データベース接続エラー:' . mysql_error($con)); // // 出力用文字コード指定(要不要は環境による) mysql_query('SET NAMES SJIS;') or die ('文字コード指定エラー' . mysql_error($con)); // // ストアドファンクション作成 mysql_query('DROP FUNCTION IF EXISTS fn1') or die('ストアド作成準備エラー:' . mysql_error($con)); mysql_query(' CREATE FUNCTION fn1 (w_id INT, w_date INT) RETURNS INT READS SQL DATA RETURN ( SELECT date FROM T1276009550B WHERE id = w_id AND date <= w_date ORDER BY date DESC LIMIT 24, 1 )') or die('ストアド作成エラー:' . mysql_error($con)); // // SQL作成 - パラメータとしてidがあればidで指定された銘柄、無ければ全銘柄の最新日付 if (isset($_GET['id'])) { $where = 't1.id =' . $_GET['id']; } else { $res = mysql_query('SELECT date FROM T1276009550B ORDER BY date DESC LIMIT 0,1') or die('最新日付取得エラー:' . mysql_error($con)); $where = 't1.date =' . mysql_result($res,0,0); } $sqlstr = " SELECT t1.id , (SELECT DISTINCT name FROM T1276009550A WHERE id = t1.id) AS name , FROM_UNIXTIME(t1.date,'%Y-%m-%d') date2 , t1.end , t1.end + STDDEV(t2.end) * 2 SP2 , t1.end + STDDEV(t2.end) SP1 , t1.end - STDDEV(t2.end) SM1 , t1.end - STDDEV(t2.end) * 2 SM2 FROM T1276009550B AS t1 LEFT JOIN T1276009550B AS t2 USING(id) WHERE $where AND t2.date BETWEEN fn1(id, t1.date) AND t1.date GROUP BY t1.id, t1.date ORDER BY id, t1.date DESC "; // 生成されたSQLの確認用(本番では不要) echo '<br />' . str_replace("\n", '<br />', $sqlstr) . '<br />'; // // テーブルの形で出力 $res = mysql_query($sqlstr) or die('データ取得エラー'); echo '<table border="1">'; echo '<thead><tr><th>id</th><th>銘柄</th><th>日付</th><th>終値</th><th>+2σ</th><th>+1σ</th><th>-1σ</th><th>-2σ</th></tr></thead>'; echo '<tbody>'; while ($rows = mysql_fetch_array($res, MYSQL_NUM)) { echo '<tr><td>' . join('</td><td>', $rows); } echo '</tbody></table>'; // // 終了処理 echo '<hr /><p>動作終了</p>'; mysql_close(); ?>
回答有難うございます。
うまく動作しましたが、計算が少し違っていました。
, t1.end + STDDEV(t2.end) * 2 SP2 以下4行、
のところで終値の標準偏差を終値にプラスしているからではないかと思います。
終値の標準偏差を25日の移動平均値にプラス(マイナス)しないといけないようです。
× 終値 + STDDEV(t2.end) * 2 SP2
○ 25日の移動平均値 + STDDEV(t2.end) * 2 SP2
になるかと思います。
回答有難うございます。
うまく動作しましたが、計算が少し違っていました。
, t1.end + STDDEV(t2.end) * 2 SP2 以下4行、
のところで終値の標準偏差を終値にプラスしているからではないかと思います。
終値の標準偏差を25日の移動平均値にプラス(マイナス)しないといけないようです。
× 終値 + STDDEV(t2.end) * 2 SP2
○ 25日の移動平均値 + STDDEV(t2.end) * 2 SP2
になるかと思います。