Sql 为每组填写行以进行旋转

发布于04月16日

我有一个名为LOGS的Oracle 18c表:

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个

我的最终目标是对数据进行轴心分析.每组log_tags人将被枢转成5列.

就像这样:

作为一个新手，我认为有几个步骤是必要的:

已解决:Assign GROUP_IDs to rows based on start/end tags
解决方案:使用类型列按组对每个标签进行分类.
未解决:填补空白.每组应有5行，每种类型一行.
未解决:将每组标签旋转到5个类型的列中.
未解决:将透视类型的列解析为其他列.(不再需要Edit:.)

第1步和第2步(已解决):

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>

问题:

我如何解Step #3 - Fill in the gaps，使每组有5行，每种类型一行？

如下所示(*):

  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>

Sql 为每组填写行以进行旋转

推荐答案

Sql相关问答推荐

我可以将INSERT语句与SELECT一起使用来创建条件吗？

SQL—如何根据2列填写缺失的值

我希望以正确的升序获取SQL结果.怎样才能得到它们？

Django将字符串筛选为整数？

收到%1、%2或%2邮箱的唯一客户

将所有XML文件导入到SQL Server中

数据库SQL-CTE命名空间(错误？)使用临时视图

如何找到一个组合的两个列，这是不是在其他表在ORACLE SQL？

每小时 Select 1行

将一个数组反嵌套到另外两个数组SQL中(Athena/presto)

如何简化此PostgreSQL查询以计算平均值？

如何使用聚合连接两个表

按行值出现的顺序对行值进行分组

从每月生成的系列中生成每日汇率

计算组内多个日期间隔go 年的累计天数

使用长 IN 子句的 SQL 优化

如何在 JSONB 数组的每个对象中添加新的键值对- PostgreSQL

Oracle PL/SQL长期运行问题

Teradata 多个进程的最大进程结束时间捕获

查找距上一条记录大于或等于 30 天的记录