-- ■日次のサマリ作成
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
--
-- サーバのバージョン： 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;