Assign GROUP_ID to rows based on start/end tags

Question

I have an Oracle 18c table called LOGS:

create table logs (id number, log_tags varchar2(4000));

insert into logs (id, log_tags) values ( 1,'<Event time="Sat Apr 15 1:13:17.750" type="Debug" thread="2fec: Main CIM worker thread" elapsed="0" ');
insert into logs (id, log_tags) values ( 2,'Database: C:\2023 Files\GDBs_4\New Mobile Geodatabase.geodatabase');
insert into logs (id, log_tags) values ( 3,'      SQL: SELECT main.ACTIVE_TRANSPORTATION.OBJECTID,main.active_transportation_nt_flag_vw.flag FRO');
insert into logs (id, log_tags) values ( 4,'</Event>');

insert into logs (id, log_tags) values ( 5,'<Event time="Sat Apr 15 1:13:17.749" type="Debug" thread="2fec: Main CIM worker thread" elapsed="1" ');
insert into logs (id, log_tags) values ( 6,null);
insert into logs (id, log_tags) values ( 7,'</Event>');

insert into logs (id, log_tags) values ( 8,'<Event time="Sat Apr 15 1:13:17.749" type="Debug" thread="2fec: Main CIM worker thread" elapsed="2" ');
insert into logs (id, log_tags) values ( 9,'Database: C:\2023 Files\GDBs_4\New Mobile Geodatabase.geodatabase');
insert into logs (id, log_tags) values (10,'      SQL: SELECT main.ACTIVE_TRANSPORTATION.OBJECTID,main.active_transportation_nt_flag_vw.flag FRO');
insert into logs (id, log_tags) values (11,'      Number of features returned: 100');
insert into logs (id, log_tags) values (12,'</Event>');

insert into logs (id, log_tags) values (13,'<Event time="Sat Apr 15 1:13:17.749" type="Debug" thread="2fec: Main CIM worker thread" elapsed="0" ');
insert into logs (id, log_tags) values (14,null);
insert into logs (id, log_tags) values (15,'</Event>');

insert into logs (id, log_tags) values (16,'<Event time="Sat Apr 15 1:13:17.747" type="Debug" thread="2fec: Main CIM worker thread" elapsed="0" ');
insert into logs (id, log_tags) values (17,'Database: C:\2023 Files\GDBs_4\New Mobile Geodatabase.geodatabase');
insert into logs (id, log_tags) values (18,'      SQL: SELECT main.ACTIVE_TRANSPORTATION.OBJECTID,main.active_transportation_nt_flag_vw.flag FRO');
insert into logs (id, log_tags) values (19,'</Event>');

insert into logs (id, log_tags) values (20,'<Event time="Sat Apr 15 1:13:17.747" type="Debug" thread="2fec: Main CIM worker thread" elapsed="2" ');
insert into logs (id, log_tags) values (21,'Database: C:\2023 Files\GDBs_4\New Mobile Geodatabase.geodatabase');
insert into logs (id, log_tags) values (22,'      SQL: SELECT main.ACTIVE_TRANSPORTATION.OBJECTID,main.active_transportation_nt_flag_vw.flag FRO');
insert into logs (id, log_tags) values (23,'      Number of features returned: 100');
insert into logs (id, log_tags) values (24,'</Event>');

insert into logs (id, log_tags) values (25,'<Event time="Sat Apr 15 1:13:17.746" type="Debug" thread="2fec: Main CIM worker thread" elapsed="1" ');
insert into logs (id, log_tags) values (26,null);
insert into logs (id, log_tags) values (27,'</Event>');

insert into logs (id, log_tags) values (28,'<Event time="Sat Apr 15 1:13:17.746" type="Debug" thread="2fec: Main CIM worker thread" elapsed="0" ');
insert into logs (id, log_tags) values (29,null);
insert into logs (id, log_tags) values (30,'</Event>');

insert into logs (id, log_tags) values (31,'<Event time="Sat Apr 15 1:13:17.744" type="Debug" thread="2fec: Main CIM worker thread" elapsed="0" ');
insert into logs (id, log_tags) values (32,'Database: C:\2023 Files\GDBs_4\New Mobile Geodatabase.geodatabase');
insert into logs (id, log_tags) values (33,'      SQL: SELECT main.ACTIVE_TRANSPORTATION.OBJECTID,main.active_transportation_nt_flag_vw.flag FRO');
insert into logs (id, log_tags) values (34,'</Event>');

insert into logs (id, log_tags) values (35,'<Event time="Sat Apr 15 1:13:17.743" type="Debug" thread="2fec: Main CIM worker thread" elapsed="1" ');
insert into logs (id, log_tags) values (36,null);
insert into logs (id, log_tags) values (37,'</Event>');

db<>fiddle

The log groups always start with a <Event ... row and end with a </Event> row.
There are a minimum of 3 rows per group and a maximum of 5 rows per group.
The rows are in sequential order via the ID column.

For each group, I want to assign a GROUP_ID number:

Question:

In a SQL query, how can I assign GROUP_IDs to the rows based on the start/end tags?

score 3 · Accepted Answer · answered Apr 15 '23 at 10:52

3

You have a gaps and islands problem, one option is to use the window function sum() combined with case when clause to define the required groups :

select 1 + sum(case when log_tags = '</Event>' then 1 else 0 end) over (order by id) 
  - case when log_tags = '</Event>' then 1 else 0 end as GROUP_ID,
  log_tags
from logs

Demo here

answered Apr 15 '23 at 10:52

SelVazi

10,028
2
13
29

1

That's a brilliantly simple way to solve the problem. – Kazi Mohammad Ali Nur Romel Apr 15 '23 at 22:18

score 1 · Answer 2 · edited Apr 15 '23 at 11:01

A few analytic functions: row_number() to create the group id, lead to get the next "<Event"'s id:

select start_evt.grp, other_evt.id, other_evt.log_tags
from (
  select 
  row_number() over(order by l1.id) as grp, l1.id, l1.log_tags,
  nvl(lead(l1.id) over(order by l1.id),999999999) as next_id
  from logs l1
  where l1.log_tags like '<Event time="%'
) start_evt
  join logs other_evt on other_evt.id between start_evt.id and start_evt.next_id-1 
;

score 1 · Answer 3 · edited Apr 16 '23 at 22:44

Original Query:

with cte as(
select
    id,    
    (case when substr(log_tags,1,6) = '<Event' then (row_number()over(partition by substr(log_tags,1,6)  order by id)) end) rn,
    log_tags
from
    logs
    )
select last_value(rn ignore nulls) 
         over(order by id
              rows between unbounded preceding and 0 preceding) group_id,id,log_tags
    from cte

Results:

| GROUP_ID | ID |                                                                                             LOG_TAGS |
|----------|----|------------------------------------------------------------------------------------------------------|
|        1 |  1 | <Event time="Sat Apr 15 1:13:17.750" type="Debug" thread="2fec: Main CIM worker thread" elapsed="0"  |
|        1 |  2 |                                    Database: C:\2023 Files\GDBs_4\New Mobile Geodatabase.geodatabase |
|        1 |  3 |       SQL: SELECT main.ACTIVE_TRANSPORTATION.OBJECTID,main.active_transportation_nt_flag_vw.flag FRO |
|        1 |  4 |                                                                                             </Event> |
|        2 |  5 | <Event time="Sat Apr 15 1:13:17.749" type="Debug" thread="2fec: Main CIM worker thread" elapsed="1"  |
|        2 |  6 |                                                                                               (null) |
|        2 |  7 |                                                                                             </Event> |
|        3 |  8 | <Event time="Sat Apr 15 1:13:17.749" type="Debug" thread="2fec: Main CIM worker thread" elapsed="2"  |
|        3 |  9 |                                    Database: C:\2023 Files\GDBs_4\New Mobile Geodatabase.geodatabase |
|        3 | 10 |       SQL: SELECT main.ACTIVE_TRANSPORTATION.OBJECTID,main.active_transportation_nt_flag_vw.flag FRO |
|        3 | 11 |                                                                     Number of features returned: 100 |
|        3 | 12 |                                                                                             </Event> |
|        4 | 13 | <Event time="Sat Apr 15 1:13:17.749" type="Debug" thread="2fec: Main CIM worker thread" elapsed="0"  |
|        4 | 14 |                                                                                               (null) |
|        4 | 15 |                                                                                             </Event> |
|        5 | 16 | <Event time="Sat Apr 15 1:13:17.747" type="Debug" thread="2fec: Main CIM worker thread" elapsed="0"  |
|        5 | 17 |                                    Database: C:\2023 Files\GDBs_4\New Mobile Geodatabase.geodatabase |
|        5 | 18 |       SQL: SELECT main.ACTIVE_TRANSPORTATION.OBJECTID,main.active_transportation_nt_flag_vw.flag FRO |
|        5 | 19 |                                                                                             </Event> |
|        6 | 20 | <Event time="Sat Apr 15 1:13:17.747" type="Debug" thread="2fec: Main CIM worker thread" elapsed="2"  |
|        6 | 21 |                                    Database: C:\2023 Files\GDBs_4\New Mobile Geodatabase.geodatabase |
|        6 | 22 |       SQL: SELECT main.ACTIVE_TRANSPORTATION.OBJECTID,main.active_transportation_nt_flag_vw.flag FRO |
|        6 | 23 |                                                                     Number of features returned: 100 |
|        6 | 24 |                                                                                             </Event> |
|        7 | 25 | <Event time="Sat Apr 15 1:13:17.746" type="Debug" thread="2fec: Main CIM worker thread" elapsed="1"  |
|        7 | 26 |                                                                                               (null) |
|        7 | 27 |                                                                                             </Event> |
|        8 | 28 | <Event time="Sat Apr 15 1:13:17.746" type="Debug" thread="2fec: Main CIM worker thread" elapsed="0"  |
|        8 | 29 |                                                                                               (null) |
|        8 | 30 |                                                                                             </Event> |
|        9 | 31 | <Event time="Sat Apr 15 1:13:17.744" type="Debug" thread="2fec: Main CIM worker thread" elapsed="0"  |
|        9 | 32 |                                    Database: C:\2023 Files\GDBs_4\New Mobile Geodatabase.geodatabase |
|        9 | 33 |       SQL: SELECT main.ACTIVE_TRANSPORTATION.OBJECTID,main.active_transportation_nt_flag_vw.flag FRO |
|        9 | 34 |                                                                                             </Event> |
|       10 | 35 | <Event time="Sat Apr 15 1:13:17.743" type="Debug" thread="2fec: Main CIM worker thread" elapsed="1"  |
|       10 | 36 |                                                                                               (null) |
|       10 | 37 |                                                                                             </Event> |

Edit:

The simplicity of SelVazi's solution is awesome. Inspired by that I tried to make it simpler:

select 
    sum(case when log_tags like '<Event%' then 1 else 0 end) over (order by id) GROUP_ID,
    log_tags
from 
    logs

Assign GROUP_ID to rows based on start/end tags

3 Answers3

Linked