ユメ吉さん
こんばんは、平塚といいます。

なんとなくですが、株式などの市場データでしょうか。
数値1が最高値、数値2が最安値、それで直近1週間、1か月、
半年(180日ではなくて？)、1年の移動平均を出したい、
ということでイメージ合うでしょうか。

この、移動平均をかなりの力技で計算しているので、
MySQL的につらいことになっていると思われます。

EXPLAINを見てインデックスを追加するというMySQLの技術力よりも、
RDBMSで移動平均をどうやって計算すればよいのだろう、
といったところに工夫が求められる感じです。

それから、
過去にさかのぼって数値1、数値2が変化することはありますか？
もし市場データでしたら過去のデータは変わらないですよね。

4か月分の計算に96分かかったのことですが、前日までのデータは
1回計算すればそれを記録しておけばよい、といったことはないでしょうか。

昨日までのデータがすでに計算してあるのならば、
今日の分を9.6秒かけて計算して、末尾に追加するだけ、
といった運用ができるかもしれませんね。

とはいえMySQLの技術力でどうにかねじ伏せられるかどうかについては
思いついたらメールしてみようと思います。
＃今日は思いつきませんでした(^^;

2016年4月29日 23:28 ユメ吉 <yume_tana@xxxxxxxxxx>:
> MySQLをはじめて1ヶ月の初心者です。以前、他のRDBを少しかじった程度ですが

> 本格的にものにしようと勉強中です。

> ところが、MySQLの処理速度が遅いので困惑しており、メールをしたところです。

>

> テーブルは日付、 keycode、数値1、数値2と簡単ですが、データ量は毎日取込んでおりで

>  keycodeが2000種程になるので今では200万件を超えております。

> 今回MySQLに移植しなおして、ためしにselect * from テーブル名(tb)de開けたところ3.2秒かかりました。

> まだインデックスは付けていませんが、 keycodeだけ付けましたが2.2秒とあまり変わりません。

> その時、EXPLAINを見ましたが　type = ALL  Key=NULL Extra = NULL  になっていました。

> 今回は自己結合を使って日付、 keycodeごとに1週間分、1か月分、半年分、一年分の平均を算出していきたく

> クエリーを作りました。　A.日付  >=  '2016-04-29' にして直近だけで9.6秒かかりました。

> ちなみに今年から（A.日付  >=  '2016-01-01' )では　96分かかっていました。本当は2年分程を5〜6分以内に

> できないか思っております。

> この時の条件は　日付とkeycodeを単独でインデックスを作りました。

> explain は tb A  type = range ,key=日付    ,extra =Using index condition; Using

> MRR; Using temporary; Using filesort

>            tb B  type = ref   ,key=keycode ,extra =Using where

> MYSQLのVer=5.7  モメリー＝4Ｇ　　ＯＳ=WIN7pro

> explainは初めてで良くわかりませんが、速度アップのためのご指導頂けないでしょうか。

> ***************************************************************

> select

>    A.hizuke         as   "HIZUKE"  ,

>    A.keycode        as   "KEYCODE",

>   avg(case when  B.日付 >= date_add(A.日付,interval -7 day)　 then  B.数値1   else

> null end )  as  "7d_1_HEIKIN"  ,

>   avg(case when  B.日付 >= date_add(A.日付,interval -7 day)　 then  B.数値2   else

> null end )  as  "7d_2_HEIKIN"  ,

>   avg(case when  B.日付 >= date_add(A.日付,interval -31 day) then  B.数値1   else

> null end )  as  "31d_1_HEIKIN"  ,

>   avg(case when  B.日付 >= date_add(A.日付,interval -31 day) then  B.数値2   else

> null end )  as  "31d_2_HEIKIN"  ,

>   avg(case when  B.日付 >= date_add(A.日付,interval -150 day) then  B.数値1   else

> null end )  as  "180d_1_HEIKIN"  ,

>   avg(case when  B.日付 >= date_add(A.日付,interval -150 day) then  B.数値2   else

> null end )  as  "180d_2_HEIKIN"  ,

>   avg(case when  B.日付 >=  date_add(A.日付,interval -365 day)　then  B.数値1

> else null end )  as  "365d_1_HEIKIN"  ,

>   avg(case when  B.日付 >=  date_add(A.日付,interval -365 day)  then  B.数値2

> else null end )  as  "365d_2_HEIKIN"  ,

>   from   tb A , tb B

>

>  where  A.keycode   =  B.keycode

>  and    A.日付  >=  '2016-04-29'

>  and    B.日付  between    date_add(A.日付,interval -365 day)   and   A.日付

>

>  group by  A.keycode ,A.日付

>

> ******************************************************************




-- 
平塚貞夫 <sh2@xxxxxxxxxx>