sqloracle-databasegaps-and-islandsoracle18cgaps-in-data
Fill in rows per group for pivoting
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>');
My end goal is to pivot the data. Each group of log_tags would be pivoted into 5 columns.
Like this:
As a novice, I think a few steps are required:
- Solved: Assign GROUP_IDs to rows based on start/end tags
- Solved: Categorize each tag per group using a TYPE column.
- Unsolved: Fill in the gaps. Each group should have 5 rows, one for each type.
- Unsolved: Pivot each group of tags into 5 type columns.
- Unsolved: Parse the pivoted type columns into additional columns. (Edit: No longer needed.)
Steps #1 and #2 (solved):
select --Step #1:
sum(case when log_tags like '<Event%' then 1 else 0 end) over (order by id) group_id,
--Step #2:
case
when substr(log_tags,1,13) = '<Event time="' then 1
when substr(log_tags,1, 9) = 'Database:' then 2
when substr(log_tags,1,10) = ' SQL:' then 3
when substr(log_tags,1,34) = ' Number of features returned:' then 4
when substr(log_tags,1, 8) = '</Event>' then 5
end as type,
substr(log_tags,1,100) as log_tags
from logs
where log_tags is not null
GROUP_ID TYPE 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 5 </Event>
2 1 <Event time="Sat Apr 15 1:13:17.749" type="Debug" thread="2fec: Main CIM worker thread" elapsed="1"
2 5 </Event>
3 1 <Event time="Sat Apr 15 1:13:17.749" type="Debug" thread="2fec: Main CIM worker thread" elapsed="2"
3 2 Database: C:\2023 Files\GDBs_4\New Mobile Geodatabase.geodatabase
3 3 SQL: SELECT main.ACTIVE_TRANSPORTATION.OBJECTID,main.active_transportation_nt_flag_vw.flag FRO
3 4 Number of features returned: 100
3 5 </Event>
4 1 <Event time="Sat Apr 15 1:13:17.749" type="Debug" thread="2fec: Main CIM worker thread" elapsed="0"
4 5 </Event>
5 1 <Event time="Sat Apr 15 1:13:17.747" type="Debug" thread="2fec: Main CIM worker thread" elapsed="0"
5 2 Database: C:\2023 Files\GDBs_4\New Mobile Geodatabase.geodatabase
5 3 SQL: SELECT main.ACTIVE_TRANSPORTATION.OBJECTID,main.active_transportation_nt_flag_vw.flag FRO
5 5 </Event>
6 1 <Event time="Sat Apr 15 1:13:17.747" type="Debug" thread="2fec: Main CIM worker thread" elapsed="2"
6 2 Database: C:\2023 Files\GDBs_4\New Mobile Geodatabase.geodatabase
6 3 SQL: SELECT main.ACTIVE_TRANSPORTATION.OBJECTID,main.active_transportation_nt_flag_vw.flag FRO
6 4 Number of features returned: 100
6 5 </Event>
7 1 <Event time="Sat Apr 15 1:13:17.746" type="Debug" thread="2fec: Main CIM worker thread" elapsed="1"
7 5 </Event>
8 1 <Event time="Sat Apr 15 1:13:17.746" type="Debug" thread="2fec: Main CIM worker thread" elapsed="0"
8 5 </Event>
9 1 <Event time="Sat Apr 15 1:13:17.744" type="Debug" thread="2fec: Main CIM worker thread" elapsed="0"
9 2 Database: C:\2023 Files\GDBs_4\New Mobile Geodatabase.geodatabase
9 3 SQL: SELECT main.ACTIVE_TRANSPORTATION.OBJECTID,main.active_transportation_nt_flag_vw.flag FRO
9 5 </Event>
10 1 <Event time="Sat Apr 15 1:13:17.743" type="Debug" thread="2fec: Main CIM worker thread" elapsed="1"
10 5 </Event>
Question:
How can I solve Step #3 - Fill in the gaps, so that each group has 5 rows, one for each type?
Like this (*):
GROUP_ID TYPE 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
1 5 </Event>
2 1 <Event time="Sat Apr 15 1:13:17.749" type="Debug" thread="2fec: Main CIM worker thread" elapsed="1"
2 *2
2 *3
2 *4
2 5 </Event>
3 1 <Event time="Sat Apr 15 1:13:17.749" type="Debug" thread="2fec: Main CIM worker thread" elapsed="2"
3 2 Database: C:\2023 Files\GDBs_4\New Mobile Geodatabase.geodatabase
3 3 SQL: SELECT main.ACTIVE_TRANSPORTATION.OBJECTID,main.active_transportation_nt_flag_vw.flag FRO
3 4 Number of features returned: 100
3 5 </Event>
4 1 <Event time="Sat Apr 15 1:13:17.749" type="Debug" thread="2fec: Main CIM worker thread" elapsed="0"
4 *2
4 *3
4 *4
4 5 </Event>
5 1 <Event time="Sat Apr 15 1:13:17.747" type="Debug" thread="2fec: Main CIM worker thread" elapsed="0"
5 2 Database: C:\2023 Files\GDBs_4\New Mobile Geodatabase.geodatabase
5 3 SQL: SELECT main.ACTIVE_TRANSPORTATION.OBJECTID,main.active_transportation_nt_flag_vw.flag FRO
5 *4
5 5 </Event>
6 1 <Event time="Sat Apr 15 1:13:17.747" type="Debug" thread="2fec: Main CIM worker thread" elapsed="2"
6 2 Database: C:\2023 Files\GDBs_4\New Mobile Geodatabase.geodatabase
6 3 SQL: SELECT main.ACTIVE_TRANSPORTATION.OBJECTID,main.active_transportation_nt_flag_vw.flag FRO
6 4 Number of features returned: 100
6 5 </Event>
7 1 <Event time="Sat Apr 15 1:13:17.746" type="Debug" thread="2fec: Main CIM worker thread" elapsed="1"
7 *2
7 *3
7 *4
7 5 </Event>
8 1 <Event time="Sat Apr 15 1:13:17.746" type="Debug" thread="2fec: Main CIM worker thread" elapsed="0"
8 *2
8 *3
8 *4
8 5 </Event>
9 1 <Event time="Sat Apr 15 1:13:17.744" type="Debug" thread="2fec: Main CIM worker thread" elapsed="0"
9 2 Database: C:\2023 Files\GDBs_4\New Mobile Geodatabase.geodatabase
9 3 SQL: SELECT main.ACTIVE_TRANSPORTATION.OBJECTID,main.active_transportation_nt_flag_vw.flag FRO
9 *4
9 5 </Event>
10 1 <Event time="Sat Apr 15 1:13:17.743" type="Debug" thread="2fec: Main CIM worker thread" elapsed="1"
10 *2
10 *3
10 *4
10 5 </Event>
Solution
It looks like all you need to accomplish all your steps three and four is just a pivot:
select * from
(select --Step #1: (https://stackoverflow.com/a/76021834/5576771)
sum(case when log_tags like '<Event%' then 1 else 0 end) over (order by id) group_id,
--Step #2:
case
when substr(log_tags,1,13) = '<Event time="' then 'a1'
when substr(log_tags,1, 9) = 'Database:' then 'a2'
when substr(log_tags,1,10) = ' SQL:' then 'a3'
when substr(log_tags,1,34) = ' Number of features returned:' then 'a4'
when substr(log_tags,1, 8) = '</Event>' then 'a5'
end as type,
substr(log_tags,1,100) as log_tags
from logs
where log_tags is not null)
PIVOT(
max(log_tags)
FOR type
IN (
'a1','a2','a3','a4','a5'
)
)
ORDER BY group_id
Fiddle here.
Regarding step 5: it is not entirely clear what exactly is you goal (and most likely it would be a good fit for separate question), but id all you need is just extract attributes of tag Event into separate columns, then look into XMLTABLE. There are a plenty of question on this matter here. Just as an example: here is one asked yesterday.