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 Go to next message
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 #684809 is a reply to message #684808] Thu, 26 August 2021 05:09 Go to previous messageGo to next message
Michel Cadot
Messages: 68043
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Why have we no feedback in your previous topics?

Re: Generate Unique Grouping ID [message #684810 is a reply to message #684809] Thu, 26 August 2021 05:15 Go to previous messageGo to next message
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 Go to previous message
_jum
Messages: 576
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

Previous Topic: About REF datatype
Next Topic: Oracle Invalid hint
Goto Forum:
  


Current Time: Thu Dec 02 13:51:09 CST 2021