ゆめ吉です。

昨日は不在で、返事が遅れました。

みなさんからのアドバイスから判断して、あまりおかしくなさそうです。
MySQLは高速と聞いていたので、もしかしておかしいのかな思っていました。

確かにデータは変わらないので毎日追加するほうが得策ですね！

宮田さんからのプロシージャを参考に考えて見ます。

今後もよろしくお願いします。


----- Original Message -----
>From: Masaki Miyata <catlover@xxxxxxxxxx>
>To: ml@xxxxxxxxxx 
>Date: 2016/5/1, Sun 09:25
>Subject: [mysql 16328] Re: [mysql 16327] Re: [mysql 16326] Re: [mysql 16325] Re: [mysql 16324] 処理速度が遅い
>  
>
>こんにちは。宮田です。
>
>
>> > avg(case when B.日付 >= date_add(A.日付,interval -7 day)　 then B.数値1  else null end ) as "7d_1_HEIKIN" ,
>> と、A.日付で終端されてないので、4/28のレコードを固定化しても4/29以降の値を含んで、固定化難しいんじゃないかなとか思ってました(間違い？)
>
>
>where B.hizuke between date_add(A.hizuke, interval -365 day) and A.hizuke
>
>
>上記のJOIN条件にて、between で範囲指定してあるので、
>A.hizuke 以降は含まれないようになっていますね。
>
>
>
>
>宮田 昌紀
>catlover@xxxxxxxxxx 
>
>2016年4月30日 14:10 yoku ts. <yoku0825@xxxxxxxxxx>:
>
>こんにちは、yoku0825といいます。 
>>これ、元の式が 
>>> avg(case when B.日付 >= date_add(A.日付,interval -7 day)　 then B.数値1 else null end ) as "7d_1_HEIKIN" , 
>>と、A.日付で終端されてないので、4/28のレコードを固定化しても4/29以降の値を含んで、固定化難しいんじゃないかなとか思ってました(間違い？) 
>>求められている回答とは違うんですが、InfiniDBでやったらまともな処理速度が出そうだったので、Window関数を持っている他のRDBMSが良いんじゃないかなと思います。
>> 
>>yoku0825, 
>> 
>>2016/04/30 10:42 "Masaki Miyata" <catlover@xxxxxxxxxx>:
>>>
>>> こんにちは。
>>> 宮田と申します。
>>>
>>> 頭の体操にちょっとやってみました。
>>>
>>> そのSQLで求めるなら、
>>> 最低限、「keycode,hizuke」の複合インデックスが必要ですね。
>>>
>>> 2013/1/1 から日次でサンプルデータを作って試してみました。
>>> WindowsのノートPC、Core i5-4200Uメモリ 8GB、MySQLのメモリーチューニング無しの状態で、
>>> 1日分で5〜10秒くらい。
>>>
>>> まぁ、自己結合で日付を最大1年とはいえクロス結合するので、
>>> 1日分で73万件（2000*365）処理することになるので、妥当じゃないでしょうか。
>>>
>>> 過去分が変わらないのであれば、1日分をサマリーテーブルに入れていけばよいです。
>>>
>>> -- ■日次のサマリ作成
>>> insert into tb_sum
>>>  (hizuke, keycode,
>>>  7d_1_HEIKIN, 7d_2_HEIKIN, 31d_1_HEIKIN, 31d_2_HEIKIN,
>>>  180d_1_HEIKIN, 180d_2_HEIKIN, 365d_1_HEIKIN, 365d_2_HEIKIN)
>>> select
>>>  A.hizuke,
>>>  A.keycode,
>>>  avg(case when B.hizuke >= date_add(A.hizuke, interval  -7 day) then B.num1 else null end ) as  '7d_1_HEIKIN',
>>>  avg(case when B.hizuke >= date_add(A.hizuke, interval  -7 day) then B.num2 else null end ) as  '7d_2_HEIKIN',
>>>  avg(case when B.hizuke >= date_add(A.hizuke, interval -31 day) then B.num1 else null end ) as '31d_1_HEIKIN',
>>>  avg(case when B.hizuke >= date_add(A.hizuke, interval -31 day) then B.num2 else null end ) as '31d_2_HEIKIN',
>>>  avg(case when B.hizuke >= date_add(A.hizuke, interval -150 day) then B.num1 else null end ) as '180d_1_HEIKIN',
>>>  avg(case when B.hizuke >= date_add(A.hizuke, interval -150 day) then B.num2 else null end ) as '180d_2_HEIKIN',
>>>  avg(case when B.hizuke >= date_add(A.hizuke, interval -365 day) then B.num1 else null end ) as '365d_1_HEIKIN',
>>>  avg(case when B.hizuke >= date_add(A.hizuke, interval -365 day) then B.num2 else null end ) as '365d_2_HEIKIN'
>>>  from tb A, tb B
>>> where A.keycode = B.keycode
>>>  and A.hizuke = '2016-04-29'
>>>  and B.hizuke between date_add(A.hizuke, interval -365 day) and A.hizuke
>>> group by A.keycode, A.hizuke;
>>>
>>> -- ■テーブルやテストデータ作成のプロシージャなど
>>>
>>> -- phpMyAdmin SQL Dump
>>> -- version 4.2.7.1
>>> -- http://www.phpmyadmin.net/
>>> --
>>> -- サーバのバージョン： 5.6.20
>>> -- PHP Version: 5.5.15
>>>
>>> SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
>>> SET time_zone = "+00:00";
>>>
>>> --
>>> -- Database: 'test1'
>>> --
>>>
>>> DELIMITER $$
>>> --
>>> -- プロシージャ
>>> --
>>> CREATE DEFINER=`root`@`localhost` PROCEDURE `ins`()
>>>   NO SQL
>>> BEGIN
>>> START TRANSACTION;
>>> SET @k = 1;
>>> SET @d = '2013-01-01';
>>>
>>> WHILE '2016-04-29' >= @d DO
>>>  WHILE 2001 > @k DO
>>>   insert into tb (`keycode`,`hizuke`,`num1`,`num2`)
>>>    values( @k, @d, CEIL(RAND() * 1000), CEIL(RAND() * 2000));
>>>   SET @k = @k +1;
>>>  END WHILE;
>>>  SET @k = 1;
>>>  SET @d = date_add(@d, interval +1 day);
>>> END WHILE;
>>> COMMIT;
>>> END$$
>>>
>>> DELIMITER ;
>>>
>>> -- --------------------------------------------------------
>>>
>>> --
>>> -- テーブルの構造 'tb'
>>> --
>>>
>>> CREATE TABLE IF NOT EXISTS tb (
>>> id int(10) unsigned NOT NULL,
>>>  keycode int(10) unsigned NOT NULL,
>>>  hizuke date NOT NULL,
>>>  num1 int(10) unsigned NOT NULL,
>>>  num2 int(10) unsigned NOT NULL
>>> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=2778524 ;
>>>
>>> -- --------------------------------------------------------
>>>
>>> --
>>> -- テーブルの構造 'tb_sum'
>>> --
>>>
>>> CREATE TABLE IF NOT EXISTS tb_sum (
>>> id int(10) unsigned NOT NULL,
>>>  keycode int(10) unsigned NOT NULL,
>>>  hizuke date NOT NULL,
>>>  7d_1_HEIKIN double unsigned NOT NULL,
>>>  7d_2_HEIKIN double unsigned NOT NULL,
>>>  31d_1_HEIKIN double unsigned NOT NULL,
>>>  31d_2_HEIKIN double unsigned NOT NULL,
>>>  180d_1_HEIKIN double unsigned NOT NULL,
>>>  180d_2_HEIKIN double unsigned NOT NULL,
>>>  365d_1_HEIKIN double unsigned NOT NULL,
>>>  365d_2_HEIKIN double unsigned NOT NULL
>>> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=2048 ;
>>>
>>> --
>>> -- Indexes for dumped tables
>>> --
>>>
>>> --
>>> -- Indexes for table tb
>>> --
>>> ALTER TABLE tb
>>> ADD PRIMARY KEY (keycode,hizuke), ADD UNIQUE KEY id (id), ADD UNIQUE KEY uq (hizuke,keycode);
>>>
>>> --
>>> -- Indexes for table tb_sum
>>> --
>>> ALTER TABLE tb_sum
>>> ADD PRIMARY KEY (keycode,hizuke), ADD UNIQUE KEY id (id), ADD UNIQUE KEY uq (hizuke,keycode);
>>>
>>> --
>>> -- AUTO_INCREMENT for dumped tables
>>> --
>>>
>>> --
>>> -- AUTO_INCREMENT for table tb
>>> --
>>> ALTER TABLE tb
>>> MODIFY id int(10) unsigned NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=2778524;
>>> --
>>> -- AUTO_INCREMENT for table tb_sum
>>> --
>>> ALTER TABLE tb_sum
>>> MODIFY id int(10) unsigned NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=2048;
>>>
>>>
>>>
>>> 宮田 昌紀
>>> catlover@xxxxxxxxxx
>>>
>>> 2016年4月30日 0:13 HIRATSUKA Sadao <sh2@xxxxxxxxxx>:
>>>
>>>> ユメ吉さん
>>>> こんばんは、平塚といいます。
>>>>
>>>> なんとなくですが、株式などの市場データでしょうか。
>>>> 数値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>
>>>
>>>
>> 
> 
>
>