Iotのデータに関するSQLの作成について
仮定
施設に停留している機器を記録するシステムがある。(各施設での監視記録であるため機体移動中の記録はない)
表Aにはどの施設にどの機体が停留しているか機体名、施設名、記録時刻が記載さている。
機体が施設Aから施設Bに移動した際のFromとToの(施設名, 時刻)の一覧を表Bとする。
質問
このとき表Bを作成するSQL(PostgreSQL 10.4)をご教示ください。
表Aと表Bの例を用意いたしました。下表Bは手動で作成いたしました。実際の表Aは100万行以上の行を持っています。
表A
No 機体名 施設名 時刻
-- ------ ------ --------
1 機体A 市役所 13:18:56
2 機体B 裁判所 13:19:19
3 機体C 警察署 13:19:25
4 機体A 保健所 14:08:18
5 機体B 裁判所 14:18:40
6 機体A 気象台 14:20:10
7 機体C 警察署 14:28:45
8 機体A 気象台 14:30:11
9 機体C 官公署 14:36:48
10 機体C 官公署 14:38:47
11 機体C 官公署 14:40:47
12 機体A 高校 14:46:19
13 機体C 博物館 14:50:42
14 機体C 博物館 14:52:42
15 機体B 神社 14:54:41
16 機体C 博物館 14:54:43
17 機体C 博物館 14:56:40
18 機体C 博物館 14:58:40
19 機体C 神社 15:06:40
20 機体C 神社 15:08:38
21 機体B 工場 15:10:43
22 機体C 官公署 15:14:48
23 機体B 消防署 15:18:41
24 機体B 消防署 15:20:40
25 機体B 消防署 15:22:40
26 機体B 神社 15:28:38
27 機体A 高校 15:36:18
28 機体A 高校 15:38:17
29 機体B 神社 15:48:39
30 機体C 官公署 15:48:48
31 機体A 交番 15:50:16
32 機体C 神社 15:56:38
33 機体C 郵便局 16:02:42
34 機体B 小中校 16:16:42
35 機体C 郵便局 16:16:42
36 機体A 交番 16:18:16
37 機体B 小中校 16:18:42
38 機体C 郵便局 16:18:42
表B
No 機体名 施設名From 時刻From 施設名To 時刻To
-- ------ ------- ----------- -------- --------
1 機体A 市役所 13:18:56 保健所 14:08:18
2 機体A 保健所 14:08:18 気象台 14:20:10
3 機体C 警察署 14:28:45 官公署 14:36:48
4 機体A 気象台 14:30:11 高校 14:46:19
5 機体C 官公署 14:40:47 博物館 14:50:42
6 機体B 裁判所 14:18:40 神社 14:54:41
7 機体C 博物館 14:58:40 神社 15:06:40
8 機体B 神社 14:54:41 工場 15:10:43
9 機体C 神社 15:08:38 官公署 15:14:48
10 機体B 工場 15:10:43 消防署 15:18:41
11 機体B 消防署 15:22:40 神社 15:28:38
12 機体A 高校 15:38:17 交番 15:50:16
13 機体C 官公署 15:48:48 神社 15:56:38
14 機体C 神社 15:56:38 郵便局 16:02:42
15 機体B 神社 15:48:39 小中校 16:16:42