イベントログのようなデータをRDBSに保存しており、1時間ごとのイベントの発生数をグラフにしたいとします。
GROUP BY句とCOUNT関数による集約処理を行えばよいのですが、
値が0となる場合、その値を含む行はデータとして取得できません。
そのためRなどでグラフを描画する際に不都合が生じます。
以下に例を示します。
MySQLに以下のようなテーブルがありデータがあるとします。
CREATE TABLE event_time (
time timestamp
);
これを1時間ごとに集約するには次のようにします。
select time, count(*)
from event_time
where day(time) = "XXXX-XX-XX"
group by time
order by time asc;
実行するとこのようなデータが得られます。
+------+----------+
| time | count(*) |
+------+----------+
| 0 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 5 |
| 8 | 2 |
| 9 | 1 |
| 10 | 1 |
| 11 | 1 |
| 12 | 1 |
| 16 | 2 |
| 17 | 9 |
| 18 | 29 |
| 19 | 59 |
| 20 | 132 |
| 21 | 29 |
| 22 | 10 |
| 23 | 4 |
+------+----------+
このように値が0となる行はデータとして得られないため、
このままではグラフを描画した際におかしなことになってしまいます。
解決方法 - 時間軸テーブルとJOINする
時間軸データを予め作成しておいて、そのテーブルと集約したデータをJOINします。
時間軸テーブルの作成
ここでは0から23までの24行の時間軸テーブルを作成します。
PostgreSQL
create table timeaxis SELECT generate_series(0, 23);
MySQL
create table timeaxis
SELECT 0 time
FROM DUAL WHERE (@num:=1-1)*0
UNION ALL
SELECT @num:=@num+1 FROM `information_schema`.COLUMNS LIMIT 23+1;
> SELECT * FROM timeaxis;
+------+
| time |
+------+
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
| 11 |
| 12 |
| 13 |
| 14 |
| 15 |
| 16 |
| 17 |
| 18 |
| 19 |
| 20 |
| 21 |
| 22 |
| 23 |
+------+
SELECT JOINによる結合
select timeaxis.time, timecount.cnt
from timeaxis left join
(select time, count(time) as cnt
from event_time
where day(time) = "XXXX-XX-XX"
group by time
order by time asc) as timecount
on timeaxis.time = timecount.time
order by timeaxis.time asc;
サブクエリは先に示したクエリと同じものです。
+------+----------+
| time | count(*) |
+------+----------+
| 0 | 2 |
| 1 | NULL |
| 2 | NULL |
| 3 | NULL |
| 4 | NULL |
| 5 | 1 |
| 6 | 2 |
| 7 | 5 |
| 8 | 2 |
| 9 | 1 |
| 10 | 1 |
| 11 | 1 |
| 12 | 1 |
| 13 | NULL |
| 14 | NULL |
| 15 | NULL |
| 16 | 2 |
| 17 | 9 |
| 18 | 29 |
| 19 | 59 |
| 20 | 132 |
| 21 | 29 |
| 22 | 10 |
| 23 | 4 |
+------+----------+
後処理
結合処理を行った場合、補完された行の集約値は0ではなくNULLとなっています。そのため、SQLの式を改良するか、グラフ描画の前で後処理を行う必要があります。
SQLのCASE式を使用
CASE timeaxis.time WHEN IS NULL THEN 0 ELSE access
RによるNA値の置き換え
access_time.isna[access_time] <- 0
RでRDBSからデータを取得した場合、NULLはRのNAという値に置き換わるので、それを0で置き換えます。