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

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

剰余系を使って間引く

データベースに記録したデータをグラフにしたい場合、あまりにもデータが多いので間引く必要がある。
そもそも、データベースはスキップという操作があまり得意ではない。
それでもテーブルの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軸の時間とかは、全機種共通なので１つだけ送るとかデータ転送量減らす予定。