はじめに
自宅に設定してある複数の温度計センサーの値をDBに格納しているのですが、いざBIツールに読み込ませてグラフ化を行おうとしたところ全てのセンサーの温度/湿度が同じ列に格納されていてグラフ化には適していないことが判明しました。
しかしながら既に数日分の温度情報を蓄積済みであり今更DBの構成を変更したくはなかったため、SQLでテーブル構成を分析に適した形に変換することで対処できましたので、その備忘です。
やりたいこと
DBのテーブル構成は下図の左「元のテーブル構成」の通りです。
温度測定用のデバイスが増えてもテーブル構成を変えなくて済むようにデバイスID列で対象を区別できるような列構成としています。
ただし、このテーブル構成では全ての部屋の温度/湿度は同じ列であるため分析には不向きであり、右「行を列に展開した構成」のようにデバイス毎に列を分けることが今回やりたいことになります。
行を列に展開する(集約関数+CASE)
集約関数を利用することでGROUP BYで指定した列で集計を行いますが、この時CASEで条件を付与することで一意の対象のデータのみ返すことができます。（下の例ではデバイスIDで対象を指定することで、特定のデバイスIDに対する温度/湿度のみ抽出）
SELECT
日時,
MAX(CASE WHEN デバイスID = '書斎のデバイスID' THEN 温度 END) AS 書斎の温度,
MAX(CASE WHEN デバイスID = '書斎のデバイスID' THEN 湿度 END) AS 書斎の湿度,
MAX(CASE WHEN デバイスID = '寝室のデバイスID' THEN 温度 END) AS 寝室の温度,
MAX(CASE WHEN デバイスID = '寝室のデバイスID' THEN 湿度 END) AS 寝室の湿度
FROM テーブル名
GROUP BY 日時
ORDER BY 日時
このデータをBIツールに読み込ませることで、以下のような複数系列のグラフを作成できます。
おわりに
SQLで行を列に展開する方法について記載しました。
集計用の中間テーブルの作成が不要で、SQL1クエリで実現できるため使えるシーンが多そうです。