こんにちは、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@38ta.com>:
>
> こんにちは。
> 宮田と申します。
>
> 頭の体操にちょっとやってみました。
>
> その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@38ta.com
>
> 2016年4月30日 0:13 HIRATSUKA Sadao <sh2@pop01.odn.ne.jp>:
>
>> ユメ吉さん
>> こんばんは、平塚といいます。
>>
>> なんとなくですが、株式などの市場データでしょうか。
>> 数値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@yahoo.co.jp>:
>> > 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@pop01.odn.ne.jp>
>
>