時系列データからデータの変化点を得る

仮想の課題と解決策を考えてみます。

課題

PLCあるいはセンサーから取得されるデータの変化点（変化が発生した時刻）と、変化した際のデータの差（前のデータとの差）を取得する

取得するデータ

• データに変化が生じた時刻
• 前のデータとの差（対象のデータは数値で数は1つ）

条件

• データは1秒間隔でソースから取得され、テーブルAに記録される
• 対象のデータは数値、1回に取得される数は1つ
• 1回に取得されるデータは、テーブルAにレコードとして追加される

解説

さて、データ・ソースから取得されたデータが無秩序にデータベース中のテーブルに書き込まれます。これはありがちなケースです。PLCやセンサーからデータを取得するゲートウェイの設計担当者が、出来る努力をしなかったケースですね。（もちろん、全てのデータがほしい、というオーダーがあった可能性は否定できませんが）
このようなお粗末を許すプロジェクトですから、他の失敗も考えられます。
簡単に想像できる失敗は、目的とするデータを計算するバッチ・プログラムを作り出すことです。このプログラムはスケジューラで定期的に実行されます。良さそうな設計ですが、はたしてこのプログラムの処理はデータの発生に追いつくのでしょうか。もし追いつかない場合には、対症療法的な対策が取られるでしょう。それは、チューニングと称して行われる、データベースに対する様々な工夫です。それでも解決できない場合には、ハードウェアの設計担当者のミスが指摘されるかもしれません。こうしてプロジェクトは「計画になかった役に立たない不要な作業」のために大きく複雑になっていきます。
想像したくもない状況ですが、情報システム開発のプロジェクトでは、今でもこれに類する失敗が繰り返されています。

経験からではなく歴史（学問）から学ぶ賢者はこう考えます「データへの処理は、そのデータが発生したときにしろ」。
データ・ドリブン型システムとは、本来はこのようなことを指し、大昔からソフトウェアの工学分野で有効性が唱えられていました。ただし、今回のケースでは、データが発生した時には何も手出しができません。条件から読み取ると、発生したデータはもれなくテーブルAに書き込まれ、テーブルAに書き込まれる前になにかの工夫はできなさそうです。
となると、可能で有効な細工は、データがテーブルAに追加される部分にすべきのようです。そして、もう一歩考えを進めると、データベースの基礎的な知識さえあれば、使うべきはトリガーだということに簡単に気づくことができます。
後述の解決策の一例でもトリガーを活用しています。

解決策の一例

テーブルA

解決策を具体的に説明するために、テーブルAを以下のように仮定します。これは、かつての僕の記事で何度か登場した"from_plc"ですね。

mysql> desc from_plc; 
+-------------+-----------+------+-----+-------------------+-------------------+ 
| Field       | Type      | Null | Key | Default           | Extra             |
+-------------+-----------+------+-----+-------------------+-------------------+
| body        | json      | YES  |     | NULL              |                   | 
| time_insert | timestamp | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+-------------+-----------+------+-----+-------------------+-------------------+
2 rows in set (0.02 sec)

mysql>  

見てのとおり、モデルはMySQLで実装されています。

トリガー

計算と目的のデータをピックアップするために、トリガーを使います。トリガーは以下のとおりです。

DROP TRIGGER IF EXISTS from_plc_proc;

DELIMITER //
CREATE TRIGGER from_plc_proc AFTER INSERT ON from_plc FOR EACH ROW
BEGIN
    DECLARE count_new INTEGER;
    DECLARE count_old INTEGER;
    DECLARE count_rec INTEGER;

    --  from_plc_last の参照
    SELECT COUNT(*) INTO count_rec FROM from_plc_last;
    IF count_rec > 0 THEN
        SELECT JSON_EXTRACT(body, '$.value') INTO count_old FROM from_plc_last;
    ELSE
        INSERT INTO from_plc_last (time_insert) VALUES (NOW());
        SET count_old = 0;
    END IF;

    --  from_plc_last の更新
    UPDATE from_plc_last SET body = NEW.body, time_insert = NEW.time_insert;

    SET count_new = JSON_EXTRACT(NEW.body, '$.value');

    --  count_status の更新
    IF count_new <> count_old THEN
        INSERT INTO count_status (count_old, count_new, time_change) VALUES (count_old, count_new, NEW.time_insert);
    END IF;

END;
//
DELIMITER ;

3つのテーブルが登場します。

• from_plc : 前述のテーブルAの実態
• from_plc_last : from_plc中の最新のレコードのコピーが置かれる
• count_status : 目的のデータの導出が可能なデータが記録される

各テーブルの属性は以下のとおりです。from_plcは既出なので、あらためて示すことはしません。

mysql> desc from_plc_last; 
+-------------+-----------+------+-----+---------+-------+ 
| Field       | Type      | Null | Key | Default | Extra |
+-------------+-----------+------+-----+---------+-------+
| body        | json      | YES  |     | NULL    |       | 
| time_insert | timestamp | YES  |     | NULL    |       |
+-------------+-----------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> desc count_status; 
+-------------+-----------+------+-----+---------+-------+ 
| Field       | Type      | Null | Key | Default | Extra | 
+-------------+-----------+------+-----+---------+-------+
| count_old   | int       | YES  |     | NULL    |       | 
| count_new   | int       | YES  |     | NULL    |       | 
| time_change | timestamp | YES  |     | NULL    |       |
+-------------+-----------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql>

このシステムでは、from_plc_last はヒット率100%のキャッシュとして機能し、前後のデータの差を計算する処理の効率化に寄与します。

テスト

上記の解決策をテストしてみました。

データ

以前の僕の記事「サーバやPC上のプログラムでModbus機器からデータを取得 (2)」で登場したPerlスクリプトの劣化版とModbusシミュレーターでデータを発生させました。

サーバやPC上のプログラムでModbus機器からデータを取得 (2)

Perlスクリプトの46, 68行目をコメント・アウトしただけです。

結果

from_plc

mysql> select * from from_plc; 
+----------------------------------------------------+---------------------+ 
| body                                               | time_insert         |
+----------------------------------------------------+---------------------+
| {"ts": "2022-08-07T12:31:52.445466", "value": 0}   | 2022-08-07 12:31:52 | 
| {"ts": "2022-08-07T12:31:53.492522", "value": 0}   | 2022-08-07 12:31:53 |
| {"ts": "2022-08-07T12:31:54.547653", "value": 0}   | 2022-08-07 12:31:54 |
| {"ts": "2022-08-07T12:31:55.563391", "value": 0}   | 2022-08-07 12:31:55 | 
| {"ts": "2022-08-07T12:31:56.610040", "value": 0}   | 2022-08-07 12:31:56 |
| {"ts": "2022-08-07T12:31:57.672496", "value": 1}   | 2022-08-07 12:31:57 |
| {"ts": "2022-08-07T12:31:58.703830", "value": 1}   | 2022-08-07 12:31:58 | 
| {"ts": "2022-08-07T12:31:59.734885", "value": 1}   | 2022-08-07 12:31:59 |
| {"ts": "2022-08-07T12:32:00.766081", "value": 1}   | 2022-08-07 12:32:00 |
| {"ts": "2022-08-07T12:32:01.797569", "value": 1}   | 2022-08-07 12:32:01 | 
| {"ts": "2022-08-07T12:32:02.859724", "value": 2}   | 2022-08-07 12:32:02 |
| {"ts": "2022-08-07T12:32:03.891062", "value": 2}   | 2022-08-07 12:32:03 |
| {"ts": "2022-08-07T12:32:04.958819", "value": 2}   | 2022-08-07 12:32:04 | 
| {"ts": "2022-08-07T12:32:06.5521", "value": 2}     | 2022-08-07 12:32:06 |
+----------------------------------------------------+---------------------+
14 rows in set (0.01 sec)

mysql>

from_plc_last

mysql> select * from from_plc_last;
+------------------------------------------------+---------------------+
| body                                           | time_insert         |
+------------------------------------------------+---------------------+
| {"ts": "2022-08-07T12:32:06.5521", "value": 2} | 2022-08-07 12:32:06 |
+------------------------------------------------+---------------------+
1 row in set (0.00 sec)

mysql> 

count_status

mysql> select * from count_status;
+-----------+-----------+---------------------+
| count_old | count_new | time_change         |
+-----------+-----------+---------------------+
|         0 |         1 | 2022-08-07 12:31:57 |
|         1 |         2 | 2022-08-07 12:32:02 |
+-----------+-----------+---------------------+
2 rows in set (0.00 sec)

mysql>