Home » SQL & PL/SQL » SQL & PL/SQL » Generate Unique Grouping ID (Oracle 12c)
Generate Unique Grouping ID [message #684808] |
Thu, 26 August 2021 04:25 |
Manoj.Gupta.91
Messages: 239 Registered: March 2008 Location: Delhi
|
Senior Member |
|
|
Dear All,
I've GPS data and I want to generate unique Grouping ID for each sequence of Start and Stop of vehicle. IGNITION_STATUS = 1 indicated vehicle is started and 0 indicates it is stopped. Vehicle's GPS sends data at 1 minute interval and it will continue to send data even if ignition is off. So there may be more than one messages received where ignition remained in on state or off state. I need to identify these sequences separately.
I tried multiple approaches but could not find any way to do it. Please help.
CREATE TABLE DEVICE_EVENT_LOG
(
DEVICE_ID NUMBER(10),
IGNITION_STATUS NUMBER(1),
SPEED NUMBER(7,5),
EVENT_TS TIMESTAMP
) ;
INSERT INTO DEVICE_EVENT_LOG( DEVICE_ID, IGNITION_STATUS, SPEED, EVENT_TS ) VALUES( 1, 0, 0, TO_DATE('25-08-2021 18:06:29','DD-MM-RRRR HH24:MI:SS') ) ;
INSERT INTO DEVICE_EVENT_LOG( DEVICE_ID, IGNITION_STATUS, SPEED, EVENT_TS ) VALUES( 1, 1, 20, TO_DATE('25-08-2021 18:07:29','DD-MM-RRRR HH24:MI:SS') ) ;
INSERT INTO DEVICE_EVENT_LOG( DEVICE_ID, IGNITION_STATUS, SPEED, EVENT_TS ) VALUES( 1, 1, 40, TO_DATE('25-08-2021 18:08:29','DD-MM-RRRR HH24:MI:SS') ) ;
INSERT INTO DEVICE_EVENT_LOG( DEVICE_ID, IGNITION_STATUS, SPEED, EVENT_TS ) VALUES( 1, 0, 0, TO_DATE('25-08-2021 18:09:29','DD-MM-RRRR HH24:MI:SS') ) ;
INSERT INTO DEVICE_EVENT_LOG( DEVICE_ID, IGNITION_STATUS, SPEED, EVENT_TS ) VALUES( 1, 0, 0, TO_DATE('25-08-2021 18:10:29','DD-MM-RRRR HH24:MI:SS') ) ;
INSERT INTO DEVICE_EVENT_LOG( DEVICE_ID, IGNITION_STATUS, SPEED, EVENT_TS ) VALUES( 1, 1, 44, TO_DATE('25-08-2021 18:11:29','DD-MM-RRRR HH24:MI:SS') ) ;
INSERT INTO DEVICE_EVENT_LOG( DEVICE_ID, IGNITION_STATUS, SPEED, EVENT_TS ) VALUES( 1, 1, 13, TO_DATE('25-08-2021 18:12:29','DD-MM-RRRR HH24:MI:SS') ) ;
INSERT INTO DEVICE_EVENT_LOG( DEVICE_ID, IGNITION_STATUS, SPEED, EVENT_TS ) VALUES( 1, 0, 0, TO_DATE('25-08-2021 18:13:29','DD-MM-RRRR HH24:MI:SS') ) ;
INSERT INTO DEVICE_EVENT_LOG( DEVICE_ID, IGNITION_STATUS, SPEED, EVENT_TS ) VALUES( 1, 0, 0, TO_DATE('25-08-2021 18:14:29','DD-MM-RRRR HH24:MI:SS') ) ;
INSERT INTO DEVICE_EVENT_LOG( DEVICE_ID, IGNITION_STATUS, SPEED, EVENT_TS ) VALUES( 1, 0, 0, TO_DATE('25-08-2021 18:15:29','DD-MM-RRRR HH24:MI:SS') ) ;
INSERT INTO DEVICE_EVENT_LOG( DEVICE_ID, IGNITION_STATUS, SPEED, EVENT_TS ) VALUES( 2, 1, 5, TO_DATE('25-08-2021 18:06:10','DD-MM-RRRR HH24:MI:SS') ) ;
INSERT INTO DEVICE_EVENT_LOG( DEVICE_ID, IGNITION_STATUS, SPEED, EVENT_TS ) VALUES( 2, 0, 0, TO_DATE('25-08-2021 18:07:10','DD-MM-RRRR HH24:MI:SS') ) ;
INSERT INTO DEVICE_EVENT_LOG( DEVICE_ID, IGNITION_STATUS, SPEED, EVENT_TS ) VALUES( 2, 0, 0, TO_DATE('25-08-2021 18:08:10','DD-MM-RRRR HH24:MI:SS') ) ;
INSERT INTO DEVICE_EVENT_LOG( DEVICE_ID, IGNITION_STATUS, SPEED, EVENT_TS ) VALUES( 2, 0, 0, TO_DATE('25-08-2021 18:09:10','DD-MM-RRRR HH24:MI:SS') ) ;
INSERT INTO DEVICE_EVENT_LOG( DEVICE_ID, IGNITION_STATUS, SPEED, EVENT_TS ) VALUES( 2, 1, 66, TO_DATE('25-08-2021 18:10:10','DD-MM-RRRR HH24:MI:SS') ) ;
INSERT INTO DEVICE_EVENT_LOG( DEVICE_ID, IGNITION_STATUS, SPEED, EVENT_TS ) VALUES( 2, 0, 0, TO_DATE('25-08-2021 18:11:10','DD-MM-RRRR HH24:MI:SS') ) ;
INSERT INTO DEVICE_EVENT_LOG( DEVICE_ID, IGNITION_STATUS, SPEED, EVENT_TS ) VALUES( 2, 0, 0, TO_DATE('25-08-2021 18:12:10','DD-MM-RRRR HH24:MI:SS') ) ;
INSERT INTO DEVICE_EVENT_LOG( DEVICE_ID, IGNITION_STATUS, SPEED, EVENT_TS ) VALUES( 2, 0, 0, TO_DATE('25-08-2021 18:13:10','DD-MM-RRRR HH24:MI:SS') ) ;
INSERT INTO DEVICE_EVENT_LOG( DEVICE_ID, IGNITION_STATUS, SPEED, EVENT_TS ) VALUES( 2, 1, 80, TO_DATE('25-08-2021 18:14:10','DD-MM-RRRR HH24:MI:SS') ) ;
Expected Output:
DEVICE_ID IGNITION_STATUS SPEED EVENT_TS GROUP_ID
1 0 0 25-08-21 18:06:29 1
1 1 20 25-08-21 18:07:29 2
1 1 40 25-08-21 18:08:29 2
1 0 0 25-08-21 18:09:29 3
1 0 0 25-08-21 18:10:29 3
1 1 44 25-08-21 18:11:29 4
1 1 13 25-08-21 18:12:29 4
1 0 0 25-08-21 18:13:29 5
1 0 0 25-08-21 18:14:29 5
1 0 0 25-08-21 18:15:29 5
2 1 5 25-08-21 18:06:10 6
2 0 0 25-08-21 18:07:10 7
2 0 0 25-08-21 18:08:10 7
2 0 0 25-08-21 18:09:10 7
2 1 66 25-08-21 18:10:10 8
2 0 0 25-08-21 18:11:10 9
2 0 0 25-08-21 18:12:10 9
2 0 0 25-08-21 18:13:10 9
2 1 80 25-08-21 18:14:10 10
Thanks & Regards
Manoj
|
|
|
|
Re: Generate Unique Grouping ID [message #684810 is a reply to message #684809] |
Thu, 26 August 2021 05:15 |
Manoj.Gupta.91
Messages: 239 Registered: March 2008 Location: Delhi
|
Senior Member |
|
|
Michel Cadot wrote on Thu, 26 August 2021 15:39
Why have we no feedback in your previous topics?
Hi Michel,
Sorry for that. I actually did not realize that it is required or could not realize it's importance. All solutions provided on this forum were very-very helpful to me. I'll post my feedback to my previous topics as well as make it my habit to post feedback on forum every time when a solution is reached.
Regards
Manoj
[Updated on: Thu, 26 August 2021 05:16] Report message to a moderator
|
|
|
Re: Generate Unique Grouping ID [message #684812 is a reply to message #684810] |
Fri, 27 August 2021 01:13 |
_jum
Messages: 577 Registered: February 2008
|
Senior Member |
|
|
You could do it with lag/lead or with the smarter MATCH_RECOGNIZE clause:
--MATCH_RECOGNIZE
SELECT device_id, ignition_status, speed, event_ts, chg, mnr
FROM device_event_log
MATCH_RECOGNIZE
( ORDER BY device_id, event_ts
MEASURES
classifier() chg
, match_number() mnr
ALL ROWS PER MATCH
PATTERN (Y N*)
DEFINE
Y AS ignition_status != prev(ignition_status) OR prev(ignition_status) IS NULL
, N AS ignition_status = Y.ignition_status);
DEVICE_ID IGNITION_STATUS SPEED EVENT_TS CHG MNR
------------------------------------------------------------------------------
1 0 0 25.08.2021 18:06:29,000000 Y 1
1 1 20 25.08.2021 18:07:29,000000 Y 2
1 1 40 25.08.2021 18:08:29,000000 N 2
1 0 0 25.08.2021 18:09:29,000000 Y 3
1 0 0 25.08.2021 18:10:29,000000 N 3
1 1 44 25.08.2021 18:11:29,000000 Y 4
1 1 13 25.08.2021 18:12:29,000000 N 4
1 0 0 25.08.2021 18:13:29,000000 Y 5
1 0 0 25.08.2021 18:14:29,000000 N 5
1 0 0 25.08.2021 18:15:29,000000 N 5
2 1 5 25.08.2021 18:06:10,000000 Y 6
2 0 0 25.08.2021 18:07:10,000000 Y 7
2 0 0 25.08.2021 18:08:10,000000 N 7
2 0 0 25.08.2021 18:09:10,000000 N 7
2 1 66 25.08.2021 18:10:10,000000 Y 8
2 0 0 25.08.2021 18:11:10,000000 Y 9
2 0 0 25.08.2021 18:12:10,000000 N 9
2 0 0 25.08.2021 18:13:10,000000 N 9
2 1 80 25.08.2021 18:14:10,000000 Y 10
[Updated on: Fri, 27 August 2021 05:03] Report message to a moderator
|
|
|
Goto Forum:
Current Time: Sat Jun 29 07:46:19 CDT 2024
|