About creating SQL for Iot data

There is a system for recording equipment parked at the facility.(There is no record of the aircraft moving because it is a monitoring record at each facility)
In Table A, the name of the aircraft, the name of the aircraft, and the recording time are described.
Table B is a list of From and To (facility name, time) when the aircraft is moved from facility A to facility B.

Please tell me which SQL (PostgreSQL 10.4) you want to create Table B.

We have prepared examples of Table A and Table B.The table B below has been manually created.The actual table A has more than one million lines.
Table A

No Airframe Name Facility Name Time    
  --  ------  ------  --------
   1 Airframe A City Hall 13:18:56
   2 Airframe B Court 13:19:19
   3 Airframe C Police Station 13:19:25
   4 Airframe A Health Center 14:08:18
   5 Airframe B Court 14:18:40
   6 Airframe A Meteorological Observatory 14:20:10
   7 Airframe C Police Station 14:28:45
   8 Airframe A Meteorological Observatory 14:30:11
   9 Airframe C Government Office 14:36:48
  10 Airframe C Government Office 14:38:47
  11 Airframe C Government Office 14:40:47
  12 Airframe A High School 14:46:19
  13 Airframe C Museum 14:50:42
  14 Airframe C Museum 14:52:42
  15 Airframe B Shrine 14:54:41
  16 Airframe C Museum 14:54:43
  17 Airframe C Museum 14:56:40
  18 Airframe C Museum 14:58:40
  19 Airframe C Shrine 15:06:40
  20 Airframe C Shrine 15:08:38
  21 Airframe B Factory 15:10:43
  22 Airframe C Government Office 15:14:48
  23 Airframe B Fire Station 15:18:41
  24 Airframe B Fire Station 15:20:40
  25 Airframe B Fire Station 15:22:40
  26 Airframe B Shrine 15:28:38
  27 Airframe A High School 15:36:18
  28 Airframe A High School 15:38:17
  29 Airframe B Shrine 15:48:39
  30 Airframe C Government Office 15:48:48
  31 Airplane A police box 15:50:16
  32 Airframe C Shrine 15:56:38
  33 Airframe C Post Office 16:02:42
  34 Airframe B Elementary and Junior High School 16:16:42
  35 Airframe C Post Office 16:16:42
  36 Airplane A police box 16:18:16
  37 Airframe B Elementary and Junior High School 16:18:42
  38 Airframe C Post Office 16:18:42

Table B

No Airframe Name Facility Name From Time From Facility Name To Time To   
  --  ------  -------     -----------  --------   --------
   1 Airframe A City Hall 13:18:56 Health Center 14:08:18
   2 Airframe A Health Center 14:08:18 Meteorological Observatory 14:20:10
   3 Airframe C Police Station 14:28:45 Government Offices 14:36:48
   4 Airframe A Meteorological Observatory 14:30:11 High School 14:46:19
   5 Airframe C Government Office 14:40:47 Museum 14:50:42
   6 Airframe B Court 14:18:40 Shrine 14:54:41
   7 Airframe C Museum 14:58:40 Shrine 15:06:40
   8 Airframe B Shrine 14:54:41 Factory 15:10:43
   9 Airframe C Shrine 15:08:38 Government Offices 15:14:48
  10 Airframe B Factory 15:10:43 Fire Station 15:18:41
  11 Airframe B Fire Station 15:22:40 Shrine 15:28:38
  12 Airframe A High School 15:38:17 Police Station 15:50:16
  13 Airframe C Government Office 15:48:48 Shrine 15:56:38
  14 Airframe C Shrine 15:56:38 Post Office 16:02:42
  15 Airframe B Shrine 15:48:39 Elementary and junior high school 16:16:42

sql postgresql

2022-09-30 21:37

2 Answers

This is possible by using the lead window function.

Table

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)
);

Data

INSERT INTO "events" ("id", "machine", "place", "time") VALUES (1, 'Airframe A', 'City Hall', '13:18:56');
INSERT INTO "events" ("id", "machine", "place", "time") VALUES (2, 'Airframe B', 'Court', '13:19');
INSERT INTO "events" ("id", "machine", "place", "time") VALUES (3, 'Airframe C', 'Police Station', '13:19:25');
INSERT INTO "events" ("id", "machine", "place", "time") VALUES (4, 'Airframe A', 'Health Center', '14:08:18');
INSERT INTO "events" ("id", "machine", "place", "time") VALUES (5, 'Airframe B', 'Court', '14:18:40');
INSERT INTO "events" ("id", "machine", "place", "time") VALUES (6, 'airframe A', 'weather station', '14:20:10');
INSERT INTO "events" ("id", "machine", "place", "time") VALUES (7, 'Airframe C', 'Police Station', '14:28:45');
INSERT INTO "events" ("id", "machine", "place", "time") VALUES (8, 'Airframe A', 'weather station', '14:30:11');
INSERT INTO "events" ("id", "machine", "place", "time") VALUES (9, 'Airframe C', 'Government Office', '14:36:48');
INSERT INTO "events" ("id", "machine", "place", "time") VALUES (10, 'Airframe C', 'Government Office', '14:38:47');
INSERT INTO "events" ("id", "machine", "place", "time") VALUES (11, 'Airframe C', 'Government Office', '14:40:47');
INSERT INTO "events" ("id", "machine", "place", "time") VALUES (12, 'Airframe A', 'High School', '14:46:19');
INSERT INTO "events" ("id", "machine", "place", "time") VALUES (13, 'Airframe C', 'Museum', '14:50:42');
INSERT INTO "events" ("id", "machine", "place", "time") VALUES (14, 'Airframe C', 'Museum', '14:52:42');
INSERT INTO "events" ("id", "machine", "place", "time") VALUES (15, 'Airframe B', ' Shrine', '14:54:41');
INSERT INTO "events" ("id", "machine", "place", "time") VALUES (16, 'Airframe C', 'Museum', '14:54:43');
INSERT INTO "events" ("id", "machine", "place", "time") VALUES (17, 'Airframe C', 'Museum', '14:56:40');
INSERT INTO "events" ("id", "machine", "place", "time") VALUES (18, 'Airframe C', 'Museum', '14:58:40');
INSERT INTO "events" ("id", "machine", "place", "time") VALUES (19, 'Airframe C', ' Shrine', '15:06:40');
INSERT INTO "events" ("id", "machine", "place", "time") VALUES (20, 'Airframe C', 'Jingu', '15:08:38');
INSERT INTO "events" ("id", "machine", "place", "time") VALUES (21, 'Airframe B', 'Factory', '15:10:43');
INSERT INTO "events" ("id", "machine", "place", "time") VALUES (22, 'Airframe C', 'Government Office', '15:14:48');
INSERT INTO "events" ("id", "machine", "place", "time") VALUES (23, 'Airframe B', 'Fire Station', '15:18:41');
INSERT INTO "events" ("id", "machine", "place", "time") VALUES (24, 'Airframe B', 'Fire Station', '15:20:40');
INSERT INTO "events" ("id", "machine", "place", "time") VALUES (25, 'Airframe B', 'Fire Station', '15:22:40');
INSERT INTO "events" ("id", "machine", "place", "time") VALUES (26, 'Airframe B', 'Jingu', '15:28:38');
INSERT INTO "events" ("id", "machine", "place", "time") VALUES (27, 'Airframe A', 'High School', '15:36:18');
INSERT INTO "events" ("id", "machine", "place", "time") VALUES (28, 'Airframe A', 'High School', '15:38:17');
INSERT INTO "events" ("id", "machine", "place", "time") VALUES (29, 'Airframe B', 'Jingu', '15:48:39');
INSERT INTO "events" ("id", "machine", "place", "time") VALUES (30, 'Airframe C', 'Government Office', '15:48');
INSERT INTO "events" ("id", "machine", "place", "time") VALUES (31, 'Airframe A', 'Box', '15:50:16');
INSERT INTO "events" ("id", "machine", "place", "time") VALUES (32, 'airframe C', 'shrine', '15:56:38');
INSERT INTO "events" ("id", "machine", "place", "time") VALUES (33, 'Airframe C', 'Post Office', '16:02:42');
INSERT INTO "events" ("id", "machine", "place", "time") VALUES (34, 'Airframe B', 'Elementary and Middle School', '16:16:42');
INSERT INTO "events" ("id", "machine", "place", "time") VALUES (35, 'Airframe C', 'Post Office', '16:16:42');
INSERT INTO "events" ("id", "machine", "place", "time") VALUES (36, 'Airframe A', 'Box', '16:18:16');
INSERT INTO "events" ("id", "machine", "place", "time") VALUES (37, 'Airframe B', 'Elementary and Middle School', '16:18:42');
INSERT INTO "events" ("id", "machine", "place", "time") VALUES (38, 'Airframe C', 'Post Office', '16:18:42');

Query

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

Results

 machine | from_place | from_time | to_place | to_time
---------+------------+-----------+----------+----------
 Airframe A | City Hall | 13:18:56 | Health Center | 14:08:18
 Airframe A | Health Center | 14:08:18 | Meteorological Observatory | 14:20:10
 Airframe C | Police Station | 14:28:45 | Government Office | 14:36:48
 Airframe A | Meteorological Observatory | 14:30:11 | High School | 14:46:19
 Airframe C | Government | 14:40:47 | Museum | 14:50:42
 Airframe B | Court | 14:18:40 | Shrine | 14:54:41
 Airframe C | Museum | 14:58:40 | Shrine | 15:06:40
 Airframe B | Shrine | 14:54:41 | Factory | 15:10:43
 Airframe C | Shrine | 15:08:38 | Government | 15:14:48
 Airframe B | Factory | 15:10:43 | Fire Station | 15:18:41
 Airframe B | Fire Station | 15:22:40 | Shrine | 15:28:38
 Airframe A | High School | 15:38:17 | Police Station | 15:50:16
 Airframe C | Government | 15:48:48 | Shrine | 15:56:38
 Airframe C | Shrine | 15:56:38 | Post Office | 16:02:42
 Airframe B | Shrine | 15:48:39 | Elementary and junior high school | 16:16:42
(15 rows)

By the way

The above query may also be valid for MySQL (>= 8.0).(Similar table definitions)

2022-09-30 21:37

(Same as the answer) I was able to solve myself with the following SQL.
If you don't mind, I would appreciate it if you could let me know the difference between @YukiInoue♦ and SQL in terms of efficiency.　　

select row_number() over(order by e1.time) asid,
       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 3
                   where e1.machine=e3.machine
                     and e1.time>e3.time);

2022-09-30 21:37

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656