人力検索はてな
モバイル版を表示しています。PC版はこちら
i-mobile

【3000PT進呈】SQL文をご教授願います。

随分考えたのですが、計算することが出来なかったので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形式のデータをお渡しします。]

●質問者: dekapurio
●カテゴリ:ウェブ制作 経済・金融・保険
✍キーワード:Low MySQL SQL カラム名 データ
○ 状態 :終了
└ 回答数 : 3/3件

▽最新の回答へ

1 ● ニャンざぶろう
●30ポイント

3000ポイント払うくらいでしたら

この本を買って参考にされるのはいかがでしょう

MySQL5.0ベースのSQLですし。

ぐんぐん実力がつく! 逆算式SQL教科書

ぐんぐん実力がつく! 逆算式SQL教科書

  • 作者: 小野 哲
  • 出版社/メーカー: 技術評論社
  • メディア: 単行本(ソフトカバー)

http://www.amazon.co.jp/product-reviews/4774133132

>特に応用編は秀逸な例題が満載されていて

>為替データの移動平均やボリンジャーバンドなどの

>テクニカル分析には「やられた!!」という感じ。

>さらに、データの自動作成をSQLだけで表現するとは驚き(◎o◎)

◎質問者からの返答

その本も先週買ったのですが、テーブル構成が違うためわたしには使い物になりませんでしたTT


2 ● polyamid
●500ポイント

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と日付で一意となります。


3 ● koriki-kozou
●2300ポイント ベストアンサー

過去質問の 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

になるかと思います。

関連質問


●質問をもっと探す●



0.人力検索はてなトップ
8.このページを友達に紹介
9.このページの先頭へ
対応機種一覧
お問い合わせ
ヘルプ/お知らせ
ログイン
無料ユーザー登録
はてなトップ