【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形式のデータをお渡しします。]

回答の条件
  • 1人2回まで
  • 13歳以上
  • 登録:2010/06/09 00:05:53
  • 終了:2010/06/09 03:57:04

ベストアンサー

id:koriki-kozou No.3

koriki-kozou回答回数480ベストアンサー獲得回数792010/06/09 02:27:29

ポイント2300pt

過去質問の 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();
?>
id:dekapurio

回答有難うございます。

うまく動作しましたが、計算が少し違っていました。

, t1.end + STDDEV(t2.end) * 2 SP2 以下4行、

のところで終値の標準偏差を終値にプラスしているからではないかと思います。

終値の標準偏差を25日の移動平均値にプラス(マイナス)しないといけないようです。

× 終値 + STDDEV(t2.end) * 2 SP2

○ 25日の移動平均値 + STDDEV(t2.end) * 2 SP2

になるかと思います。

2010/06/09 03:16:06

その他の回答(2件)

id:karuishi No.1

ニャンざぶろう回答回数764ベストアンサー獲得回数1282010/06/09 01:04:03

ポイント30pt

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

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

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

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

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

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

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

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

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

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

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

id:dekapurio

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

2010/06/09 01:21:35
id:polyamid No.2

polyamid回答回数152ベストアンサー獲得回数12010/06/09 02:16:48

ポイント500pt

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:dekapurio

説明不足で非常に申し訳ないのですが・・・idは銘柄コードです。

説明文のURLページでいえば1301です。

なので、idと日付で一意となります。

2010/06/09 02:27:58
id:koriki-kozou No.3

koriki-kozou回答回数480ベストアンサー獲得回数792010/06/09 02:27:29ここでベストアンサー

ポイント2300pt

過去質問の 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();
?>
id:dekapurio

回答有難うございます。

うまく動作しましたが、計算が少し違っていました。

, t1.end + STDDEV(t2.end) * 2 SP2 以下4行、

のところで終値の標準偏差を終値にプラスしているからではないかと思います。

終値の標準偏差を25日の移動平均値にプラス(マイナス)しないといけないようです。

× 終値 + STDDEV(t2.end) * 2 SP2

○ 25日の移動平均値 + STDDEV(t2.end) * 2 SP2

になるかと思います。

2010/06/09 03:16:06
  • id:dekapurio
    idは銘柄コードです。
    id(銘柄コード)とdate(日付)で一意となります。
  • id:koriki-kozou
    koriki-kozou 2010/06/09 03:06:14
    25日分に満たない場合は表示されない(表の見出しだけが出力される)という仕様は参考にした回答と同じ
    かんたんなテストしかしていないので、大量のデータ(特に「銘柄ごとの最新の1件」)ではどうなるかは判らない
    5日移動平均なら LIMIT 4, 1 75日移動平均なら LIMIT 74, 1 にそれぞれ変更


    ここからは逆質問(25日は例文を書くのが長くなるので5日としてます)
    (1)移動平均の計算対象として、例えば下記のデータがあった場合、5日移動平均であれば6/2から6/8までの5件で計算するという考え方であってる?
    2010年6月8日
    2010年6月7日
    2010年6月4日
    2010年6月3日
    2010年6月2日
    2010年6月1日
    (2)売買不成立でデータが歯抜けになったりする場合、5日移動平均であれば6/1から6/8までの5件で計算としているけれど、あってる?
    2010年6月8日 成立
    2010年6月7日 成立
    2010年6月4日 不成立でデータ無し
    2010年6月3日 成立
    2010年6月2日 成立
    2010年6月1日 成立
  • id:dekapurio
    1銘柄のみでは難なく表示できますが、銘柄ごとはさすがに結構な時間がかかっていますね。
    ただ10秒程度ですので全く問題なしです。

    >>ここからは逆質問
    1,2ともあってます!
  • id:koriki-kozou
    koriki-kozou 2010/06/09 03:26:01
    >計算が少し違っていました。

    ごめんなさい。下記のような具合に訂正してください
    , t1.end
    , avg(t2.end) av
    , avg(t2.end) + STDDEV(t2.end) * 2 SP2
    , avg(t2.end) + STDDEV(t2.end) SP1
    , avg(t2.end) - STDDEV(t2.end) SM1
    , avg(t2.end) - STDDEV(t2.end) * 2 SM2
    1つ列が増えるのでテーブルの見出し行も「終値」と「+2σ」の間に「移動平均」を追加してください
  • id:dekapurio
    有難うございます。完璧です!
  • id:polyamid
    既に動くものが出てるっぽいですが、投稿します。
    無理矢理連番をつけてみました。

    -------------------------------------------------
    select
    t1.id, t1.date,
    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
    (
    select @i:=@i+1 as rownum, data_table.*
    from (select @i:=0) as dummy, data_table
    where id = 1301
    order by date asc
    ) as t1,
    (
    select @j:=@j+1 as rownum, data_table.*
    from (select @j:=0) as dummy, data_table
    where id = 1301
    order by date asc
    ) as t2
    where
    t2.rownum <= t1.rownum
    and t2.rownum > t1.rownum - 25
    group by t1.rownum
    order by t1.rownum desc
    -------------------------------------------------

    「data_table」、「1301」、「25」となっている箇所を適宜変更してみてください。
  • id:dekapurio
    >koriki-kozou
    ごめんなさい。
    ポイントが足りなかったので後日メッセージにてお送りいたします。
  • id:dekapurio
    >polyamid
    回答有難うございます
    SQLだけですし、すごくいいですね。
  • id:koriki-kozou
    koriki-kozou 2010/06/09 04:03:28
    いえ、十分すぎるくらいです
    (移動平均を書いてなかったという大きな抜けがありましたしw)
    またの機会があれば、そのときにでも上乗せしてください

この質問への反応(ブックマークコメント)

トラックバック

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません