2024 年度 OSS リテラシ 3 : 代表値・分散 + 移動平均

はじめに

本資料では，データ処理の基礎として，代表値，分散，移動平均を取り扱う．

代表値・分散

集団の中心的傾向を示す値を「代表値」という． 代表値としては、一般に平均値が使われるが，分布の形によっては 最頻値や中央値を代表値にする場合もある．

• 総務省統計局 なるほど統計学園 5-1

一方で，データ集団は代表値だけではデータ全体の様子をとらえる事ができない． データの散らばり具合にも着目しなければならず， そのために最大値・最小値・偏差・度数分布などが用いられる．

• 総務省統計局 なるほど統計学園 5-2

この演習では，センサーのデータから代表値およびデータの散らばり具合を示す指標を可視化することを目指す．

移動平均

時系列データを扱う場合，代表値は「移動平均」することが一般的である． 例えば，1 時間平均と言った時には，9:00～10:00，10:00～11:00，11:00～12:00，と いった具合に，平均をとる時間をずらしながら計算を行う．

日々のニュースで流れる気象データは，まさに移動平均している． 詳細は気象観測統計の解説 を参照のこと.

この演習では，SQL:2003 で規定された「ウィンドウ関数」を用いて移動平均を行うことにする．

SQL ウィンドウ関数の利用方法

具体的な SQL 文は次節で挙げるとして，まずは平均を取る部分に 平均を取る範囲の指定の仕方としては，「行数 (rows) を指定」する形となる．以下のように，

avg(temp) over (order by time rows between 2 preceding and current row)

と書けば，2 つ前の行 (2 preceding) から現在の行 (current row) まで (between) の温度の平均 (avg(temp)) を取ることができる．

また，以下のように，

avg(temp) over (order by time rows between current row and 2 following)

と書けば，現在の行 (current row) から2 つ後ろの行 (2 following) まで (between) の温度の平均 (avg(temp)) を取ることができる．

ウィンドウ関数を用いる場合は，「時間」ではなく「行数」で平均の対象となる範囲を指定するため， 時系列データを扱う場合は前処理として欠損値処理が必須である．

SQL ウィンドウ関数の利用の例

以下のようにデータが入っているものとする．この例では 21:00 と 22:00 のデータが欠損している．

MariaDB [iotex]> select name, time, temp from j4oss3 where name like 'hoge';

  +------+---------------------+------+
  | name | time                | temp |
  +------+---------------------+------+
  | hoge | 2024-11-08 00:00:00 |   10 |
  | hoge | 2024-11-08 01:00:00 |   11 |
  | hoge | 2024-11-08 02:00:00 |   12 |
  | hoge | 2024-11-08 03:00:00 |   13 |
  | hoge | 2024-11-08 04:00:00 |   14 |
  | hoge | 2024-11-08 05:00:00 |   15 |
  | hoge | 2024-11-08 06:00:00 |   16 |
  | hoge | 2024-11-08 07:00:00 |   17 |
  | hoge | 2024-11-08 08:00:00 |   18 |
  | hoge | 2024-11-08 09:00:00 |   19 |
  | hoge | 2024-11-08 10:00:00 |   20 |
  | hoge | 2024-11-08 11:00:00 |   21 |
  | hoge | 2024-11-08 12:00:00 |   22 |
  | hoge | 2024-11-08 13:00:00 |   23 |
  | hoge | 2024-11-08 14:00:00 |   24 |
  | hoge | 2024-11-08 15:00:00 |   25 |
  | hoge | 2024-11-08 16:00:00 |   26 |
  | hoge | 2024-11-08 17:00:00 |   27 |
  | hoge | 2024-11-08 18:00:00 |   28 |
  | hoge | 2024-11-08 19:00:00 |   29 |
  | hoge | 2024-11-08 20:00:00 |   30 |
  | hoge | 2024-11-08 23:00:00 |   33 | <-- 20:00 の次が 23:00 !
  +------+---------------------+------+
  22 rows in set (0.002 sec)

自分自身と前 2 点 (rows between 2 preceding and current row) を用いて平均を作ってみると 以下のような結果となる．欠損値があるために，平均するのに使う時間幅が 揃っていないことが分かる．具体的には， 時刻 23:00:00 に対して求めた平均値は，19:00:00 と 20:00:00 の 値を使っている． 計測という意味では，他の時刻と同様，23:00:00 の値は 21:00:00 ～ 23:00:00 の値の 平均値になっているべきである．

MariaDB [iotex]> select name, time, temp, avg(temp) over (order by time rows between 2 preceding and current row) as moving_avg from j4oss3 where name like 'hoge';

  +------+---------------------+------+--------------------+
  | name | time                | temp | moving_avg         |
  +------+---------------------+------+--------------------+
  | hoge | 2024-11-08 00:00:00 |   10 |                 10 | <-- 10 / 1 (前に値が無いので)
  | hoge | 2024-11-08 01:00:00 |   11 |               10.5 | <-- ( 10 + 11 ) / 2
  | hoge | 2024-11-08 02:00:00 |   12 |                 11 | <-- ( 10 + 11 + 12 ) / 3
  | hoge | 2024-11-08 03:00:00 |   13 |                 12 |
  | hoge | 2024-11-08 04:00:00 |   14 |                 13 |
  | hoge | 2024-11-08 05:00:00 |   15 |                 14 |
  | hoge | 2024-11-08 06:00:00 |   16 |                 15 |
  | hoge | 2024-11-08 07:00:00 |   17 |                 16 |
  | hoge | 2024-11-08 08:00:00 |   18 |                 17 |
  | hoge | 2024-11-08 09:00:00 |   19 |                 18 |
  | hoge | 2024-11-08 10:00:00 |   20 |                 19 |
  | hoge | 2024-11-08 11:00:00 |   21 |                 20 |
  | hoge | 2024-11-08 12:00:00 |   22 |                 21 |
  | hoge | 2024-11-08 13:00:00 |   23 |                 22 |
  | hoge | 2024-11-08 14:00:00 |   24 |                 23 |
  | hoge | 2024-11-08 15:00:00 |   25 |                 24 |
  | hoge | 2024-11-08 16:00:00 |   26 |                 25 |
  | hoge | 2024-11-08 17:00:00 |   27 |                 26 |
  | hoge | 2024-11-08 18:00:00 |   28 |                 27 |
  | hoge | 2024-11-08 19:00:00 |   29 |                 28 | <-- ( 27 + 28 + 29 ) / 3
  | hoge | 2024-11-08 20:00:00 |   30 |                 29 | <-- ( 28 + 29 + 30 ) / 3
  | hoge | 2024-11-08 23:00:00 |   33 | 30.666666666666668 | <-- ( 29 + 30 + 33 ) / 3
  +------+---------------------+------+--------------------+
  22 rows in set (0.001 sec)

自分自身と後ろ 2 点 (rows between current row and 2 following) を用いて平均を作ってみると 以下のような結果となる．欠損値があるために，前の例と同様に，平均するのに使う時間幅が 揃っていないことが分かる．

MariaDB [iotex]> select name, time, temp, avg(temp) over (order by time rows between current row and 2 following) as moving_avg from j4oss3 where name like 'hoge';

+------+---------------------+------+--------------------+
| name | time                | temp | moving_avg         |
+------+---------------------+------+--------------------+
| hoge | 2024-11-08 00:00:00 |   10 |                 11 | <-- ( 10 + 11 + 12 ) / 3
| hoge | 2024-11-08 01:00:00 |   11 |                 12 | <-- ( 11 + 12 + 13 ) / 3
| hoge | 2024-11-08 02:00:00 |   12 |                 13 |
| hoge | 2024-11-08 03:00:00 |   13 |                 14 |
| hoge | 2024-11-08 04:00:00 |   14 |                 15 |
| hoge | 2024-11-08 05:00:00 |   15 |                 16 |
| hoge | 2024-11-08 06:00:00 |   16 |                 17 |
| hoge | 2024-11-08 07:00:00 |   17 |                 18 |
| hoge | 2024-11-08 08:00:00 |   18 |                 19 |
| hoge | 2024-11-08 09:00:00 |   19 |                 20 |
| hoge | 2024-11-08 10:00:00 |   20 |                 21 |
| hoge | 2024-11-08 11:00:00 |   21 |                 22 |
| hoge | 2024-11-08 12:00:00 |   22 |                 23 |
| hoge | 2024-11-08 13:00:00 |   23 |                 24 |
| hoge | 2024-11-08 14:00:00 |   24 |                 25 |
| hoge | 2024-11-08 15:00:00 |   25 |                 26 |
| hoge | 2024-11-08 16:00:00 |   26 |                 27 |
| hoge | 2024-11-08 17:00:00 |   27 |                 28 |
| hoge | 2024-11-08 18:00:00 |   28 |                 29 | <-- ( 28 + 29 + 30 ) / 3
| hoge | 2024-11-08 19:00:00 |   29 | 30.666666666666668 | <-- ( 29 + 30 + 33 ) / 3
| hoge | 2024-11-08 20:00:00 |   30 |               31.5 | <-- ( 30 + 33 ) / 2
| hoge | 2024-11-08 23:00:00 |   33 |                 33 | <-- 33 / 1 (後ろに値が無いので)
+------+---------------------+------+--------------------+
22 rows in set (0.003 sec)

次に欠損値処理したデータを用いて同じことを行ってみる． 以下の例のように，21:00:00 と 22:00:00 の温度として NULL を入力しておく．

MariaDB [iotex]> select name, time, temp from j4oss3 where name like 'hoge';

  +------+---------------------+------+
  | name | time                | temp |
  +------+---------------------+------+
  | hoge | 2024-11-08 00:00:00 |   10 |
  | hoge | 2024-11-08 01:00:00 |   11 |
  | hoge | 2024-11-08 02:00:00 |   12 |
  | hoge | 2024-11-08 03:00:00 |   13 |
  | hoge | 2024-11-08 04:00:00 |   14 |
  | hoge | 2024-11-08 05:00:00 |   15 |
  | hoge | 2024-11-08 06:00:00 |   16 |
  | hoge | 2024-11-08 07:00:00 |   17 |
  | hoge | 2024-11-08 08:00:00 |   18 |
  | hoge | 2024-11-08 09:00:00 |   19 |
  | hoge | 2024-11-08 10:00:00 |   20 |
  | hoge | 2024-11-08 11:00:00 |   21 |
  | hoge | 2024-11-08 12:00:00 |   22 |
  | hoge | 2024-11-08 13:00:00 |   23 |
  | hoge | 2024-11-08 14:00:00 |   24 |
  | hoge | 2024-11-08 15:00:00 |   25 |
  | hoge | 2024-11-08 16:00:00 |   26 |
  | hoge | 2024-11-08 17:00:00 |   27 |
  | hoge | 2024-11-08 18:00:00 |   28 |
  | hoge | 2024-11-08 19:00:00 |   29 |
  | hoge | 2024-11-08 20:00:00 |   30 |
  | hoge | 2024-11-08 21:00:00 | NULL | <-- 欠損値!!
  | hoge | 2024-11-08 22:00:00 | NULL | <-- 欠損値!!
  | hoge | 2024-11-08 23:00:00 |   33 |
  +------+---------------------+------+
  24 rows in set (0.002 sec)

自分自身と前 2 点 (rows between 2 preceding and current row) を用いて平均を作ってみると 以下のような結果となる．期待通りとなっていると思う．

MariaDB [iotex]> select name, time, temp, avg(temp) over (order by time rows between 2 preceding and current row) as moving_avg from j4oss3 where name like 'hoge';

  +------+---------------------+------+------------+
  | name | time                | temp | moving_avg |
  +------+---------------------+------+------------+
  | hoge | 2024-11-08 00:00:00 |   10 |         10 | <-- 10 / 1 (前に値が無いので)
  | hoge | 2024-11-08 01:00:00 |   11 |       10.5 | <-- ( 10 + 11 ) / 2
  | hoge | 2024-11-08 02:00:00 |   12 |         11 | <-- ( 10 + 11 + 12 ) / 3
  | hoge | 2024-11-08 03:00:00 |   13 |         12 |
  | hoge | 2024-11-08 04:00:00 |   14 |         13 |
  | hoge | 2024-11-08 05:00:00 |   15 |         14 |
  | hoge | 2024-11-08 06:00:00 |   16 |         15 |
  | hoge | 2024-11-08 07:00:00 |   17 |         16 |
  | hoge | 2024-11-08 08:00:00 |   18 |         17 |
  | hoge | 2024-11-08 09:00:00 |   19 |         18 |
  | hoge | 2024-11-08 10:00:00 |   20 |         19 |
  | hoge | 2024-11-08 11:00:00 |   21 |         20 |
  | hoge | 2024-11-08 12:00:00 |   22 |         21 |
  | hoge | 2024-11-08 13:00:00 |   23 |         22 |
  | hoge | 2024-11-08 14:00:00 |   24 |         23 |
  | hoge | 2024-11-08 15:00:00 |   25 |         24 |
  | hoge | 2024-11-08 16:00:00 |   26 |         25 |
  | hoge | 2024-11-08 17:00:00 |   27 |         26 |
  | hoge | 2024-11-08 18:00:00 |   28 |         27 |
  | hoge | 2024-11-08 19:00:00 |   29 |         28 | <-- ( 27 + 28 + 29 ) / 3
  | hoge | 2024-11-08 20:00:00 |   30 |         29 | <-- ( 28 + 29 + 30 ) / 3
  | hoge | 2024-11-08 21:00:00 | NULL |       29.5 | <-- ( 29 + 30 ) / 2       (NULL はカウントされない)
  | hoge | 2024-11-08 22:00:00 | NULL |         30 | <-- ( 30 ) / 1            (NULL はカウントされない)
  | hoge | 2024-11-08 23:00:00 |   33 |         33 | <-- ( 33 ) / 1            (NULL はカウントされない)
  +------+---------------------+------+------------+
  24 rows in set (0.001 sec)

自分自身と後ろ 2 点 (rows between current row and 2 following) を用いて平均を作ってみると 以下のような結果となる．こちらも期待通りであろう．

MariaDB [iotex]> select name, time, temp, avg(temp) over (order by time rows between current row and 2 following) as moving_avg from j4oss3 where name like 'hoge';

  +------+---------------------+------+------------+
  | name | time                | temp | moving_avg |
  +------+---------------------+------+------------+
  | hoge | 2024-11-08 00:00:00 |   10 |         11 | <-- ( 10 + 11 + 12 ) / 3
  | hoge | 2024-11-08 01:00:00 |   11 |         12 | <-- ( 11 + 12 + 13 ) / 3
  | hoge | 2024-11-08 02:00:00 |   12 |         13 |
  | hoge | 2024-11-08 03:00:00 |   13 |         14 |
  | hoge | 2024-11-08 04:00:00 |   14 |         15 |
  | hoge | 2024-11-08 05:00:00 |   15 |         16 |
  | hoge | 2024-11-08 06:00:00 |   16 |         17 |
  | hoge | 2024-11-08 07:00:00 |   17 |         18 |
  | hoge | 2024-11-08 08:00:00 |   18 |         19 |
  | hoge | 2024-11-08 09:00:00 |   19 |         20 |
  | hoge | 2024-11-08 10:00:00 |   20 |         21 |
  | hoge | 2024-11-08 11:00:00 |   21 |         22 |
  | hoge | 2024-11-08 12:00:00 |   22 |         23 |
  | hoge | 2024-11-08 13:00:00 |   23 |         24 |
  | hoge | 2024-11-08 14:00:00 |   24 |         25 |
  | hoge | 2024-11-08 15:00:00 |   25 |         26 |
  | hoge | 2024-11-08 16:00:00 |   26 |         27 |
  | hoge | 2024-11-08 17:00:00 |   27 |         28 |
  | hoge | 2024-11-08 18:00:00 |   28 |         29 | <-- ( 28 + 29 + 30 ) / 3
  | hoge | 2024-11-08 19:00:00 |   29 |       29.5 | <-- ( 29 + 30 ) / 2       (NULL はカウントされない)
  | hoge | 2024-11-08 20:00:00 |   30 |         30 | <-- ( 30 ) / 1            (NULL はカウントされない)
  | hoge | 2024-11-08 21:00:00 | NULL |         33 | <-- ( 33 ) / 1            (NULL はカウントされない)
  | hoge | 2024-11-08 22:00:00 | NULL |         33 | <-- ( 33 ) / 1            (NULL はカウントされない)
  | hoge | 2024-11-08 23:00:00 |   33 |         33 | <-- ( 33 ) / 1            (NULL はカウントされない)
  +------+---------------------+------+------------+
  24 rows in set (0.003 sec)

課題：代表値・分散を示すダッシュボードの作成

テーブル sample2024 に含まれるデータから以下と全く同じ grafana のダッシュボードを新たに作成せよ． 以下に示すように，2024-11-12 19:00:00 から 2024-11-12 21:00:00 の範囲について 表示し，そのスナップショットを提出すること．スナップショットには必ず URL 欄を含めること．

ある程度ブラウザの幅を広げないと，以下の例のように，データを示すマル印が表示されないことに注意されたい．

• 実際のダッシュボードの例 <-- これと同じものを作る！
  • SQL が間違っていないことを確認するために，複数のグラフを重ねている

ダッシュボードに表示するもの

1枚目

• 1 分毎のデータ (欠損値処理済)
• 前 10 分平均値
  • 09:10 時点での 10 分平均値は，09:01～09:10 の範囲の平均値
  • 1 分毎に表示をすること．
• 前 10 分平均値
  • 09:10 時点での 10 分平均値は，09:01～09:10 の範囲の平均値
  • 10 分毎に表示をすること．

2枚目

• 1 分毎のデータ (欠損値処理済)
• 前 10 分の最大値
  • 09:10 時点での最大値は，09:01～09:10 の範囲の最大値とする
  • 10 分毎に表示をすること．
• 前 10 分の最小値
  • 09:10 時点での最小値は，09:01～09:10 の範囲の最小値とする
  • 10 分毎に表示をすること．

3枚目

• 前 10 分の標準偏差
  • 09:10 時点での標準偏差は，09:01～09:10 の範囲より計算すること．
  • 10 分毎に表示をすること．
• 前 1 時間の標準偏差
  • 09:00 時点での標準偏差は，08:01～09:00 の範囲より計算すること．
  • 1 時間毎に表示をすること．

編集について

1 つのグラフに複数の折れ線グラフが書かれているが，以下の編集画面で示すように， 「+ Query」アイコンをクリックすると，複数の SQL を書くことができる．

SQL の例

1 分毎の測定結果を表示する SQL の例．

grafana 回の資料 で示した SQL を若干改造し，特定のホスト (以下の例は sugiyama) のみ表示するようにしている．

SELECT
   UNIX_TIMESTAMP(time) as time_sec,
   temp as value,
   "1分データ" as metric
FROM sample2024
WHERE $__timeFilter(time) AND name like 'sugiyama'
ORDER BY time ASC

前 10 分平均値を 1 分毎にプロットする SQL の例．

先に述べた Window 関数を用いて 9 つ前の行から現在の行まで (合計 10 行分 = 10 分) のデータを平均している．

SELECT
   UNIX_TIMESTAMP(time) AS time_sec,
   avg(temp) over (order by time rows between 9 preceding and current row) as avg_temp,
   '前10分平均値_1分毎' AS metric
FROM sample2024
WHERE $__timeFilter(time) AND name LIKE 'sugiyama'
ORDER BY time ASC

前 10 分平均値を 10 分毎にプロットする SQL の例．

サブクエリを使って，前 10 分平均値を作った後に時刻の末尾が 0:00 (すなわち 10 分間隔) なデータを抽出している．

SELECT
   UNIX_TIMESTAMP(avg_time) AS time_sec,
   avg_temp,
   '前10分平均値_10分毎' AS metric
   FROM (
       SELECT
          time as avg_time,
          avg(temp) over (order by time rows between 9 preceding and current row) as avg_temp
          FROM sample2024
       WHERE $__timeFilter(time) AND name LIKE 'sugiyama'
   ) AS subquery
   where avg_time like '%0:00'
   ORDER BY avg_time ASC