効率の良いsql文

Question

CREATE TABLE table (
cd int(11) NOT NULL,
date datetime NOT NULL,
value double NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

ALTER TABLE table
ADD PRIMARY KEY (cd),
ADD UNIQUE KEY date (date,value);

ALTER TABLE table
MODIFY cd int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=844117;

INSERT INTO table (cd, date, value) VALUES
(88, '2015-12-01 08:06:22', 123.1719),
(89, '2015-12-01 08:06:24', 123.1693),
(90, '2015-12-01 08:06:26', 123.17,),
(91, '2015-12-01 08:06:27', 123.1692),
(92, '2015-12-01 08:06:29', 123.17),
(93, '2015-12-01 08:06:30', 123.1693),
(94, '2015-12-01 08:06:31', 123.1717),
(95, '2015-12-01 08:06:32', 123.1721);

上記のtableに対してそれぞれ分単位で
その分の最終値のvalue
その分の最初値のvalue
その分の最大値のvalue
その分の最小値のvalue
を取得したいのですが一発でいけるのでしょうか？

また、tableには常にデータが入ってきているので、定期的にこれらを取得してtable2へ挿入していきたいと考えています。
この場合、ストアドプロシージャーで定期的に実行させたいと考えていますが、ほかによいアイデアなどがありますでしょうか？

ちなみに、tableのデータに対してupdateは行われないので、過去のデータが変更になることはありません。

Answer 1

テーブル名やカラム名に予約語は使わないでいただきたい。

SELECT 
	DATE_FORMAT(A.`date`, '%Y-%m-%d %H:%i') AS `dt`
	, SUBSTRING_INDEX( GROUP_CONCAT(CAST(`value` AS CHAR) ORDER BY `date`), ',', 1 ) AS `first`
	, SUBSTRING_INDEX( GROUP_CONCAT(CAST(`value` AS CHAR) ORDER BY `date` DESC), ',', 1 ) AS `last`
	, min(A.`VALUE`) AS `min`
	, max(A.`VALUE`) AS `max` 
FROM 
	`sample` A
GROUP BY 
	DATE_FORMAT(A.`DATE`, '%Y-%m-%d %H:%i')

Comments

[KiyoshiMotoki]

DATE_ORMAT における「分」の指定子は %I ではなく %i ですね。

https://mariadb.com/kb/en/mariadb/date_format/

[退会済みユーザー]

修正しておきましたー

[takagi.1994]

ありがとうございました。
とても参考になりました。

Answer 2

tableには常にデータが入ってきているので、定期的にこれらを取得してtable2へ挿入していきたいと考えています。

トリガーが使えるようでしたら検討してみてください。

Comments

[Koozy]

MariaDBはわかりませんがMySQLは文トリガなので今回の集計には向かない気がしますね。

Answer 3

ローソク足グラフの作成ですね。
SQL問い合わせではスマートに取れないので、SELECT文で工夫するよりは分足を表現するテーブルを別に作る方がよろしいかと思います。
分足テーブルは日時分が主キーで first, max, min, last のカラムを持つようなテーブルで、
メインテーブルに挿入する際には必ず同時にこちらにも書き込みます。
INSERT...ON DUPLICATE KEY UPDATE
命令が役に立つかと思います。

Answer 4

例示のINSERT文だと以下の結果を取得するイメージで良いでしょうか？
その分の最終値のvalue ： 2015/12/1 08:06における時系列で最後の値である123.1721
その分の最初値のvalue ： 2015/12/1 08:06における時系列で最初の値である123.1719
その分の最大値のvalue ： 2015/12/1 08:06の最大の値である123.1721
その分の最小値のvalue ： 2015/12/1 08:06の最小の値である123.1692

GROUP BYとHAVINGを使ったややコストの高いSQLになりますが一発で取得することは可能ですね。

ストプロですとMySQLはテーブルにロックが掛かったような気がします(うろ覚え)。
その際、「tableには常にデータが入ってきているので」というのと競合しそうで気になります。