Oracle-记录对之间的分组 [英] Oracle - grouping between pairs of records
问题描述
我有一个日志表，其中包含如下所示的数据:
I have a logging table that contains data that looks like this:
ID MSG DATE
---------------------------------------------
1 TEst 2010-01-01 09:00:00
2 Job Start 2010-01-01 09:03:00
3 Do something 2010-01-01 09:03:10
4 Do something else 2010-01-01 09:03:12
5 Do something 2010-01-01 09:04:19
6 Job End 2010-01-01 09:06:30
7 Job Start 2010-01-01 09:18:03
8 Do something 2010-01-01 09:18:17
9 Do other thing 2010-01-01 09:19:48
10 Job End 2010-01-01 09:20:27
它包含(除其他事项外)由应用程序编写的消息.我感兴趣的是针对所有这样的作业开始"和作业结束"对生成一份在作业开始"记录和作业结束"记录之间写入的所有内容的报告.理想情况下，报告应如下所示:
It contains (among other things) messags written by the application. I am interested in producing a report of everything that gets written between a "Job Start" record and a "Job End" record, for all such pairs of "Job Start" and "Job End". Ideally the report would look like this:
BATCH_NUM ID MSG DATE
---------------------------------------------------------
1 3 Do something 2010-01-01 09:03:10
1 4 Do something else 2010-01-01 09:03:12
1 5 Do something 2010-01-01 09:04:19
2 8 Do something 2010-01-01 09:18:17
2 9 Do other thing 2010-01-01 09:19:48
(添加批次之间的换行符以使其更易于阅读)
(line break between batches added to make it easier to read)
输出报告省略了"Job STart"和"Job End"消息，以及"TEst"消息(存在于"Job Start"和"Job End"对的外部.)
The output report omits the "Job STart and "Job End" messages, as well as the "TEst" message (which exists outside a pair of "Job Start" and "Job End".
我什至不知道从哪里开始编写这种查询，或者最好还是使用PL/SQL.哎呀，我什至不确定我要尝试的工作是否有适当的技术术语. ;)
I'm not even sure where to begin writing this kind of query, or if it's better to go with PL/SQL for this. Heck, I'm not even sure if there's a proper technical term for what I'm trying to do. ;)
(oracle版本为10g)
(oracle version is 10g)
推荐答案
我敢肯定，还有一种分析方式可以做到这一点，但是我在标量子查询中欺骗了构建窗口.
I'm sure there's a more analytic-ish way to do this, but I'm cheating with scalar subqueries to build the windows.
SQL> select * from logging_table;
ID MSG LOG_DT
---------- ------------------------------ -------------------
1 TEst 2010-01-01 09:00:00
2 Job Start 2010-01-01 09:03:00
3 Do something 2010-01-01 09:03:10
4 Do something else 2010-01-01 09:03:12
5 Do something 2010-01-01 09:04:19
6 Job End 2010-01-01 09:06:30
7 Job Start 2010-01-01 09:18:03
8 Do something 2010-01-01 09:18:17
9 Do other thing 2010-01-01 09:19:48
10 Job End 2010-01-01 09:20:27
SQL> l
1 select dense_rank() over (order by job_start_id) as batch,
-- ^-- this part gets the batch
2 job_step_id, msg, log_dt
3 -- nested select to filter out rows outside of the boundaries
4 from (select *
5 from (select id as job_step_id, msg, log_dt,
6 -- scalar subquery to get start of "window"
7 (select max(id)
8 from logging_table
9 where msg = 'Job Start'
10 and id < log.id) as job_start_id,
11 -- scalar subquery to get end of "window"
12 (select min(id)
13 from logging_table
14 where msg = 'Job End'
15 and id > log.id) as job_end_id
16 from logging_table log
17 -- filter out the "window" rows themselves
18 where msg not in ('Job Start', 'Job End')
19 )
20 -- the filtering out of "unbounded" records
21 where job_start_id is not null
22 and job_end_id is not null
23 )
24* order by job_step_id
SQL> /
BATCH JOB_STEP_ID MSG LOG_DT
1 3 Do something 2010-01-01 09:03:10
1 4 Do something else 2010-01-01 09:03:12
1 5 Do something 2010-01-01 09:04:19
2 8 Do something 2010-01-01 09:18:17
2 9 Do other thing 2010-01-01 09:19:48
这篇关于Oracle-记录对之间的分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
