mysql:16328

こんにちは。宮田です。

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

> >

> >

>
添付ファイル
