oracle 为旋转填充每组的行

kqlmhetl 于 2023-04-20 发布在 Oracle

关注(0)|答案(4)|浏览(136)

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

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列。
就像这样：

作为一个新手，我认为需要几个步骤：
1.已解决：Assign GROUP_IDs to rows based on start/end tags
1.已解决：使用TYPE列按组对每个标记进行分类。
1.未解决的问题：填写空白。每组应该有5行，每种类型一行。
1.未解决：将每组标记透视为5个类型列。
1.未解决：将透视类型列解析为其他列。（**编辑：**不再需要。）
步骤#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>

问题：
如何解决步骤#3 -填充空白，使每组有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>

oracle

来源：https://stackoverflow.com/questions/76024662/fill-in-rows-per-group-for-pivoting

4条答案

按热度按时间

q5iwbnjs1#

看起来完成第三和第四步所需要的只是一个支点：

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

小提琴
关于步骤5：我们并不完全清楚你的目标是什么（很可能这是一个很好的适合单独的问题），但是你所需要的只是将标签Event的属性提取到单独的列中，然后查看XMLTABLE。这里有很多关于这个问题的问题。举个例子：one昨天问

赞(0）回复(0）举报 2023-04-20

vuktfyat2#

这是一个简单的pivpt，只要你知道有多少类型。

WITH CTE AS (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 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)
SELECT
group_id
, MAX(CASE WHEN  type  = 1 then log_tags END ) type1
, MAX(CASE WHEN  type  = 2 then log_tags END ) type2
, MAX(CASE WHEN  type  = 3 then log_tags END ) type3
, MAX(CASE WHEN  type  = 4 then log_tags END ) type4
, MAX(CASE WHEN  type  = 5 then log_tags END ) type5
FROM CTE
GROUP BY group_id
ORDER BY group_id

组ID	类型1	类型2	类型3	类型4
1	〈Event time=“Sat Apr 15 1：13：17.750”type=“Debug”thread=“2fec：主CIM工作线程”elapsed=“0”	数据库：C：\2023 Files\GDBs_4\New移动的Geodatabase.geodatabase	SQL：SELECT main.ACTIVE_TRANSPORTATION.OBJECTID，main.active_transportation_nt_flag_vw.flag FRO	联系我们
二	〈Event time=“Sat Apr 15 1：13：17.749”type=“Debug”thread=“2fec：主CIM工作线程”elapsed=“1”	联系我们	联系我们	联系我们
三	〈Event time=“Sat Apr 15 1：13：17.749”type=“Debug”thread=“2fec：主CIM工作线程”elapsed=“2”	数据库：C：\2023 Files\GDBs_4\New移动的Geodatabase.geodatabase	SQL：SELECT main.ACTIVE_TRANSPORTATION.OBJECTID，main.active_transportation_nt_flag_vw.flag FRO	返回的要素数：100
四	〈Event time=“Sat Apr 15 1：13：17.749”type=“Debug”thread=“2fec：主CIM工作线程”elapsed=“0”	联系我们	联系我们	联系我们
五	〈Event time=“Sat Apr 15 1：13：17.747”type=“Debug”thread=“2fec：主CIM工作线程”elapsed=“0”	数据库：C：\2023 Files\GDBs_4\New移动的Geodatabase.geodatabase	SQL：SELECT main.ACTIVE_TRANSPORTATION.OBJECTID，main.active_transportation_nt_flag_vw.flag FRO	联系我们
六	〈Event time=“Sat Apr 15 1：13：17.747”type=“Debug”thread=“2fec：主CIM工作线程”elapsed=“2”	数据库：C：\2023 Files\GDBs_4\New移动的Geodatabase.geodatabase	SQL：SELECT main.ACTIVE_TRANSPORTATION.OBJECTID，main.active_transportation_nt_flag_vw.flag FRO	返回的要素数：100
七	〈Event time=“Sat Apr 15 1：13：17.746”type=“Debug”thread=“2fec：主CIM工作线程”elapsed=“1”	联系我们	联系我们	联系我们
八	〈Event time=“Sat Apr 15 1：13：17.746”type=“Debug”thread=“2fec：主CIM工作线程”elapsed=“0”	联系我们	联系我们	联系我们
九	〈Event time=“Sat Apr 15 1：13：17.744”type=“Debug”thread=“2fec：主CIM工作线程”elapsed=“0”	数据库：C：\2023 Files\GDBs_4\New移动的Geodatabase.geodatabase	SQL：SELECT main.ACTIVE_TRANSPORTATION.OBJECTID，main.active_transportation_nt_flag_vw.flag FRO	联系我们
10个	〈Event time=“Sat Apr 15 1：13：17.743”type=“Debug”thread=“2fec：主CIM工作线程”elapsed=“1”	联系我们	联系我们	联系我们

fiddle

赞(0）回复(0）举报 2023-04-20

bybem2ql3#

感谢您提出如此详细和足智多谋的问题。您可以使用oracle 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 'Type_1'
           when substr(log_tags,1, 9) = 'Database:'                          then 'Type_2'
           when substr(log_tags,1,10) = '      SQL:'                         then 'Type_3'
           when substr(log_tags,1,34) = '      Number of features returned:' then 'Type_4'
           when substr(log_tags,1, 8) = '</Event>'                           then 'Type_5'
       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 ('Type_1','Type_2','Type_3','Type_4','Type_5')
 ) order by group_id

输出：
| 组ID|'类型_1'|'类型_2'|'类型_3'|'类型_4'|'类型_5'|
| --------------|--------------|--------------|--------------|--------------|--------------|
| 1|〈Event time=“Sat Apr 15 1：13：17.750”type=“Debug”thread=“2fec：主CIM工作线程”elapsed=“0”|数据库：C：\2023 Files\GDBs_4\New移动的Geodatabase.geodatabase| SQL：SELECT main.ACTIVE_TRANSPORTATION.OBJECTID，main.active_transportation_nt_flag_vw.flag FRO|联系我们||
| 二|〈Event time=“Sat Apr 15 1：13：17.749”type=“Debug”thread=“2fec：主CIM工作线程”elapsed=“1”|联系我们|联系我们|联系我们||
| 三|〈Event time=“Sat Apr 15 1：13：17.749”type=“Debug”thread=“2fec：主CIM工作线程”elapsed=“2”|数据库：C：\2023 Files\GDBs_4\New移动的Geodatabase.geodatabase| SQL：SELECT main.ACTIVE_TRANSPORTATION.OBJECTID，main.active_transportation_nt_flag_vw.flag FRO| 返回的要素数：100||
| 四|〈Event time=“Sat Apr 15 1：13：17.749”type=“Debug”thread=“2fec：主CIM工作线程”elapsed=“0”|联系我们|联系我们|联系我们||
| 五|〈Event time=“Sat Apr 15 1：13：17.747”type=“Debug”thread=“2fec：主CIM工作线程”elapsed=“0”|数据库：C：\2023 Files\GDBs_4\New移动的Geodatabase.geodatabase| SQL：SELECT main.ACTIVE_TRANSPORTATION.OBJECTID，main.active_transportation_nt_flag_vw.flag FRO|联系我们||
| 六|〈Event time=“Sat Apr 15 1：13：17.747”type=“Debug”thread=“2fec：主CIM工作线程”elapsed=“2”|数据库：C：\2023 Files\GDBs_4\New移动的Geodatabase.geodatabase| SQL：SELECT main.ACTIVE_TRANSPORTATION.OBJECTID，main.active_transportation_nt_flag_vw.flag FRO| 返回的要素数：100||
| 七|〈Event time=“Sat Apr 15 1：13：17.746”type=“Debug”thread=“2fec：主CIM工作线程”elapsed=“1”|联系我们|联系我们|联系我们||
| 八|〈Event time=“Sat Apr 15 1：13：17.746”type=“Debug”thread=“2fec：主CIM工作线程”elapsed=“0”|联系我们|联系我们|联系我们||
| 九|〈Event time=“Sat Apr 15 1：13：17.744”type=“Debug”thread=“2fec：主CIM工作线程”elapsed=“0”|数据库：C：\2023 Files\GDBs_4\New移动的Geodatabase.geodatabase| SQL：SELECT main.ACTIVE_TRANSPORTATION.OBJECTID，main.active_transportation_nt_flag_vw.flag FRO|联系我们||
| 10个|〈Event time=“Sat Apr 15 1：13：17.743”type=“Debug”thread=“2fec：主CIM工作线程”elapsed=“1”|联系我们|联系我们|联系我们||
fiddle

赞(0）回复(0）举报 2023-04-20

qrjkbowd4#

这个查询使用了来自@markalex和@Kazi的答案的pivot技术。有一些小的区别：

修剪某些已旋转的列以删除前导空格。
删除透视列名中的单引号。
添加了第6个类型/列来处理类型2-4未捕获的值。有关更多信息，请参阅SQL中的注解。

select group_id, col_1, col_2, trim(col_3) as col_3, trim(col_4) as col_4, col_5, trim(col_6) as col_6 from 
   (select sum(case when log_tags like '<Event%' then 1 else 0 end) over (order by id) group_id,  --https://stackoverflow.com/a/76021834/5576771
           case 
             when substr(log_tags,1,13) = '<Event time="'                      then 'col_1'
             when substr(log_tags,1, 9) = 'Database:'                          then 'col_2'
             when substr(log_tags,1,10) = '      SQL:'                         then 'col_3'
             when substr(log_tags,1,34) = '      Number of features returned:' then 'col_4'
             when substr(log_tags,1, 8) = '</Event>'                           then 'col_5'
             else 'col_6' --Sometimes the values in cols 2-4 are different from what I'm querying for above. Col_6 is a catch-all for any unexpected values. 
                          --However, col_6 will only grab the first unexpected value it comes across; all others will be ignored. I haven't found a better way to do it.
           end as type,    
           log_tags  --substr(log_tags,1,100)
      from logs
     where log_tags is not null)
pivot (max(log_tags) for type in ('col_1' as col_1,'col_2' as col_2,'col_3' as col_3,'col_4' as col_4,'col_5' as col_5, 'col_6' as col_6)) 
order by group_id

相关：将诊断监视器日志复制为Excel表，而不是垂直标记

赞(0）回复(0）举报 2023-04-20

我来回答

oracle 为旋转填充每组的行

4条答案

相关问题

热门标签

最新问答