

## 仮定

施設に停留している機器を記録するシステムがある。(各施設での監視記録であるため機体移動中の記録はない)  
表Aにはどの施設にどの機体が停留しているか機体名、施設名、記録時刻が記載さている。  
機体が施設Aから施設Bに移動した際のFromとToの(施設名, 時刻)の一覧を表Bとする。

## 質問

このとき表Bを作成するSQL(PostgreSQL 10.4)をご教示ください。

表Aと表Bの例を用意いたしました。下表Bは手動で作成いたしました。実際の表Aは100万行以上の行を持っています。  
表A

[code]

      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
    
[/code]

表B  

[code]

      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
    
[/code]


CC BY-SA 4.0
2018-12-16T10:03:54.270
51256
2018-12-17T05:00:54.107
2018-12-16T10:09:32.320
16974
16974
question
sql postgresql
Iotのデータに関するSQLの作成について


ウィンドウ関数のleadを用いることで、実現可能です。

テーブル

[code]

    CREATE TABLE events
    (
        id integer NOT NULL,
        machine text COLLATE pg_catalog."default" NOT NULL,
        place text COLLATE pg_catalog."default" NOT NULL,
        "time" text COLLATE pg_catalog."default",
        CONSTRAINT events_pkey PRIMARY KEY (id)
    );
    
[/code]

データ

[code]

    INSERT INTO "events" ("id","machine","place","time") VALUES (1,'機体A','市役所','13:18:56');
    INSERT INTO "events" ("id","machine","place","time") VALUES (2,'機体B','裁判所','13:19:19');
    INSERT INTO "events" ("id","machine","place","time") VALUES (3,'機体C','警察署','13:19:25');
    INSERT INTO "events" ("id","machine","place","time") VALUES (4,'機体A','保健所','14:08:18');
    INSERT INTO "events" ("id","machine","place","time") VALUES (5,'機体B','裁判所','14:18:40');
    INSERT INTO "events" ("id","machine","place","time") VALUES (6,'機体A','気象台','14:20:10');
    INSERT INTO "events" ("id","machine","place","time") VALUES (7,'機体C','警察署','14:28:45');
    INSERT INTO "events" ("id","machine","place","time") VALUES (8,'機体A','気象台','14:30:11');
    INSERT INTO "events" ("id","machine","place","time") VALUES (9,'機体C','官公署','14:36:48');
    INSERT INTO "events" ("id","machine","place","time") VALUES (10,'機体C','官公署','14:38:47');
    INSERT INTO "events" ("id","machine","place","time") VALUES (11,'機体C','官公署','14:40:47');
    INSERT INTO "events" ("id","machine","place","time") VALUES (12,'機体A','高校','14:46:19');
    INSERT INTO "events" ("id","machine","place","time") VALUES (13,'機体C','博物館','14:50:42');
    INSERT INTO "events" ("id","machine","place","time") VALUES (14,'機体C','博物館','14:52:42');
    INSERT INTO "events" ("id","machine","place","time") VALUES (15,'機体B','神社','14:54:41');
    INSERT INTO "events" ("id","machine","place","time") VALUES (16,'機体C','博物館','14:54:43');
    INSERT INTO "events" ("id","machine","place","time") VALUES (17,'機体C','博物館','14:56:40');
    INSERT INTO "events" ("id","machine","place","time") VALUES (18,'機体C','博物館','14:58:40');
    INSERT INTO "events" ("id","machine","place","time") VALUES (19,'機体C','神社','15:06:40');
    INSERT INTO "events" ("id","machine","place","time") VALUES (20,'機体C','神社','15:08:38');
    INSERT INTO "events" ("id","machine","place","time") VALUES (21,'機体B','工場','15:10:43');
    INSERT INTO "events" ("id","machine","place","time") VALUES (22,'機体C','官公署','15:14:48');
    INSERT INTO "events" ("id","machine","place","time") VALUES (23,'機体B','消防署','15:18:41');
    INSERT INTO "events" ("id","machine","place","time") VALUES (24,'機体B','消防署','15:20:40');
    INSERT INTO "events" ("id","machine","place","time") VALUES (25,'機体B','消防署','15:22:40');
    INSERT INTO "events" ("id","machine","place","time") VALUES (26,'機体B','神社','15:28:38');
    INSERT INTO "events" ("id","machine","place","time") VALUES (27,'機体A','高校','15:36:18');
    INSERT INTO "events" ("id","machine","place","time") VALUES (28,'機体A','高校','15:38:17');
    INSERT INTO "events" ("id","machine","place","time") VALUES (29,'機体B','神社','15:48:39');
    INSERT INTO "events" ("id","machine","place","time") VALUES (30,'機体C','官公署','15:48:48');
    INSERT INTO "events" ("id","machine","place","time") VALUES (31,'機体A','交番','15:50:16');
    INSERT INTO "events" ("id","machine","place","time") VALUES (32,'機体C','神社','15:56:38');
    INSERT INTO "events" ("id","machine","place","time") VALUES (33,'機体C','郵便局','16:02:42');
    INSERT INTO "events" ("id","machine","place","time") VALUES (34,'機体B','小中校','16:16:42');
    INSERT INTO "events" ("id","machine","place","time") VALUES (35,'機体C','郵便局','16:16:42');
    INSERT INTO "events" ("id","machine","place","time") VALUES (36,'機体A','交番','16:18:16');
    INSERT INTO "events" ("id","machine","place","time") VALUES (37,'機体B','小中校','16:18:42');
    INSERT INTO "events" ("id","machine","place","time") VALUES (38,'機体C','郵便局','16:18:42');
    
[/code]

クエリ

[code]

    select * from (
      select machine, place as from_place, time as from_time, lead(place) over w as to_place, lead(time) over w as to_time
      from events
      window w as (partition by machine order by time)
    ) as with_lead
    where to_place <> from_place
    order by to_time;
    
[/code]

結果

[code]

     machine | from_place | from_time | to_place | to_time
    ---------+------------+-----------+----------+----------
     機体A   | 市役所     | 13:18:56  | 保健所   | 14:08:18
     機体A   | 保健所     | 14:08:18  | 気象台   | 14:20:10
     機体C   | 警察署     | 14:28:45  | 官公署   | 14:36:48
     機体A   | 気象台     | 14:30:11  | 高校     | 14:46:19
     機体C   | 官公署     | 14:40:47  | 博物館   | 14:50:42
     機体B   | 裁判所     | 14:18:40  | 神社     | 14:54:41
     機体C   | 博物館     | 14:58:40  | 神社     | 15:06:40
     機体B   | 神社       | 14:54:41  | 工場     | 15:10:43
     機体C   | 神社       | 15:08:38  | 官公署   | 15:14:48
     機体B   | 工場       | 15:10:43  | 消防署   | 15:18:41
     機体B   | 消防署     | 15:22:40  | 神社     | 15:28:38
     機体A   | 高校       | 15:38:17  | 交番     | 15:50:16
     機体C   | 官公署     | 15:48:48  | 神社     | 15:56:38
     機体C   | 神社       | 15:56:38  | 郵便局   | 16:02:42
     機体B   | 神社       | 15:48:39  | 小中校   | 16:16:42
    (15 rows)
    
[/code]

### ちなみに

上記クエリは、 MySQL(>=8.0) でも有効だったりします。(同じようなテーブル定義だった場合)


CC BY-SA 4.0
2018-12-16T13:12:41.277
51264
2018-12-17T05:00:54.107
2018-12-17T05:00:54.107
754
754
51256
answer


(返答と同様です)こちらでも以下のSQLで自己解決できました。  
よろしければ効率性の観点で@Yuki Inoue♦さまのSQLとの相違点をご教示いただければ幸いです。

[code]

    select row_number() over(order by e1.time) as id,
           e1.machine as machine,
           e2.place as place_from,
           e2.time as time_from,
           e1.place as place_to,
           e1.time as time_to
      from events e1, events e2
     where e1.machine = e2.machine
       and e1.place <> e2.place
       and e2.time = (select max(time)
                        from events e3
                       where e1.machine = e3.machine
                         and e1.time > e3.time);
    
[/code]


CC BY-SA 4.0
2018-12-16T13:19:48.527
51265
2018-12-16T13:19:48.527
16974
51256
answer