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

仮定の課題と解決策を考えてみます。
## 課題
PLCあるいはセンサーから取得されるデータの変化点（変化が発生した時刻）と、変化した際のデータの差（前のデータとの差）を取得する
### 取得するデータ
 - データに変化が生じた時刻
 - 前のデータとの差（対象のデータは数値で数は1つ）
### 条件
 - データは1秒間隔でソースから取得され、テーブルAに記録される
 - 対象のデータは数値、1回に取得される数は1つ
 - 1回に取得されるデータは、テーブルAにレコードとして追加される
## 解説
![Screenshot from 2022-08-07 12-14-16.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2737842/6886bb97-9611-e3f4-667d-9f2fe84bd8ef.png)

> さて、データ・ソースから取得されたデータが無秩序にデータベース中のテーブルに書き込まれます。これはありがちなケースです。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)](https://qiita.com/COOLMAGICPRODU1/items/71fc52cc4fed8302b24d)

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> 
```
## まとめ
一旦まとめを記述せずに投稿しました。意識的にです。
この記事の記載、多くの読者が「こんなの記事にするまでもなく、あたりまえのこと」ととらえるでしょう。さて本当にそうでしょうか。これが「まとめ」です。