Help us understand the problem. What is going on with this article?

グラフ描画のためのデータベース操作

グラフ描画のためのデータベース操作

どうやってデータを間引くか

剰余系を使って間引く

データベースに記録したデータをグラフにしたい場合、あまりにもデータが多いので間引く必要がある。
そもそも、データベースはスキップという操作があまり得意ではない。
それでもテーブルのIDを単純に剰余系で間引くSQLは、こんな感じ。

SELECT id,`CreateAt`,`機器ID`,`温度` 
      FROM data_table 
      WHERE MOD(id,100)=0

これだと、複数の機器がデータベースに記録されていた時に、データの最初と最後が欠けてしまう機器が出てくる。
そこで機器ごとの連続した番号が必要と思い、データベースに記録する時に連番を振っておくseq列を追加。

SELECT id,`CreateAt`,`機器ID`,`温度`
      FROM data_table 
      WHERE `機器ID`='XXXX-XXXX' AND MOD(seq,100)=0

一応もれなく計算出来るが、機器毎にSQLを発行して読み出すので、機種が増える毎に計算時間が遅くなって行く。

データベースの得意なのは集計

ある時、別件で1時間毎にデータを集計する機能を追加して気づいた。
スキップするのではなく、1時間毎の平均を取るSQLを書いてみた。

SELECT DATE_FORMAT(`CreateAt`, '%Y-%m-%d %H:00:00') AS hour,
      `機種ID`,AVG(`温度`) AS tempr 
      FROM data_table 
      GROUP BY `機種ID`,hour 
      HAVING tempr 

別の書き方で、こう書ける事も分かった。

SELECT FROM_UNIXTIME(TRUNCATE(UNIX_TIMESTAMP(`CreateAt`)/3600,0)*3600) as sec,
      `機器ID`,AVG(`温度`) AS tempr
      FROM data_table 
      GROUP BY `機種ID`,sec 
      HAVING tempr

この書き方だと任意の秒数で集計ができるのに気づいた。
グラフの横軸は時間なので、横軸に表示する範囲の日時を描画点数で割って、あらかじめ描画点の間隔の秒数を求めておく。

$start = strtotime(最初のdatetime); 
$end   = strtotime(最後のdatetime); 
$diff  = $end - $start;
$secPerPoint = intval($diff/描画点数);
SELECT FROM_UNIXTIME(TRUNCATE(UNIX_TIMESTAMP(`CreateAt`)/{$secPerPoint},0)*{$secPerPoint}) as sec,
      `機器ID`,AVG(`温度`) AS tempr
      FROM data_table
      GROUP BY `機種ID`,sec 
      HAVING tempr

間引くよりも平均を取る。そうすればGROUP BYが使えるので、一度のSQLで機器単位の平均を求めることができる。プログラムも単純になる。
肝心のスピードだが、多少早くなったが、グラフに送るデータの量が多いところがボトルネックになってた。例えばX軸の時間とかは、全機種共通なので1つだけ送るとかデータ転送量減らす予定。

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした