oracle 根据开始/结束标记为行分配GROUP_ID

6tdlim6h 于 2023-04-20 发布在 Oracle

关注(0)|答案(3)|浏览(117)

我有一个名为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
1.日志组始终以<Event ...行开始，以</Event>行结束。
1.每组最少3行，最多5行。
1.这些行通过ID列按顺序排列。
对于每个组，我想分配一个GROUP_ID号：

问题：
在SQL查询中，如何根据开始/结束标记将GROUP_ID分配给行？
相关：
Fill in rows per group for pivoting

oracle

来源：https://stackoverflow.com/questions/76021166/assign-group-id-to-rows-based-on-start-end-tags

3条答案

按热度按时间

qybjjes11#

你有一个 gaps and islands 的问题，一个选择是使用窗口函数sum()结合case when子句来定义所需的组：

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

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

gc0ot86w2#

几个解析函数：row_number（）创建组id，导致获取下一个“〈Event”的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 
;

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

5tmbdcev3#

原始查询：

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

结果：

| 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> |

编辑：

SelVazi的解决方案非常简单。受此启发，我试图使其更简单：

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

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

我来回答

oracle 根据开始/结束标记为行分配GROUP_ID

3条答案

相关问题

热门标签

最新问答