代码之家 › 专栏 › 技术社区 › Greg Reynolds

在Oracle SQL中选择周围的行

oracle sql

Greg Reynolds · 技术社区 · 15 年前

我们有一个将数据写入日志表的项目。现在,在调查一个问题时,查询问题行并获取周围的行是很有用的,这样我们就可以很容易地看到导致问题的原因。日志表有一个timestamp字段,因此我们可以根据它进行排序。

本质上,我希望SQL使用类似“grep-C”的东西。

例如,假设我们有一个reference\u id列和一个activity\u code列。我发现引用ID=1234有问题,所以我想查找前面的N个活动。

Code        Reference   Time
Allocate    ABC1        9:00
Allocate    ABC2        9:01
Problem     MYREF1      9:02
Allocate    ABC3        9:03
Allocate    ABC4        9:03
Problem2    MYREF1      9:04
Allocate    ABC5        9:09

我被赋予了“MYREF1”作为一种观察对象,但我想看看在同一时间发生了什么。我需要一个查询,该查询将拾取“MYREF1”引用行,以及一些其他行(可能是周围或前面行中的1或2行)。在我的示例中,如果我想要前面的行(类似于grep-B1),那将是ABC2和ABC4

5 回复 | 直到 15 年前

JB. 15 年前

下面是一个方法的概念分解。

用行号注释已订购的日志:

WITH ordered_logs AS (
    SELECT ROWNUM r, log_table.*
    FROM log_table 
    ORDER BY timestamp
)
SELECT * FROM ordered_logs;

在行号中查找我们要查找的中心信息:

SELECT r r0 FROM ordered_logs
WHERE reference_id = 1234; -- or whatever uniquely identifies your problem

浏览它周围的几行:

SELECT * FROM ordered_logs, sought WHERE r BETWEEN r0 - 5 AND r0 + 5;

把它们重新组合起来:

WITH
  ordered_logs AS (
    SELECT ROWNUM r, log_table.*
    FROM log_table 
    ORDER BY timestamp
  ),
  sought AS (
    SELECT r r0
    FROM ordered_logs
    WHERE reference_id = 1234
  )
SELECT *
FROM ordered_logs, sought
WHERE r BETWEEN r0 - 5 AND r0 + 5;

Nick Pierpoint 15 年前

有趣的问题。

您可以使用分析函数为您提供可能感兴趣的时间范围,然后使用此范围从日志记录表中进行选择。

(没有机会运行此SQL,但它应该会让您产生想法)。

这将为以下两行提供时间:前两行和后两行:

select
    l.code,
    l.reference,
    l.time,
    min(l.time) over (
        order by l.time 
        rows between 2 preceding and current row) 
            preceding_time,
    max(l.time) over (
        order by l.time 
        rows beween current row and 2 following) 
            following_time
from
    log_table l;

然后,您可以使用这些“时间框”在驾驶表中选择一个范围。

with timebox as
    (
    select
        l.code,
        l.reference,
        l.time,
        min(l.time) over (
            order by l.time 
            rows between 2 preceding and current row) 
                preceding_time,
        max(l.time) over (
            order by l.time 
            rows beween current row and 2 following) 
                following_time
    from
        log_table l
    )
select
    *
from
    log_table a
where
    exists
        (
        select 1 from
            timebox t
        where
            t.reference = 'MYREF1'
        and a.time between t.preceding_time and t.following_time
        );

这和你想要的接近吗?

Tony Andrews 15 年前

如果只需要1234的最新:n行:

select timestamp, activity_code
from
( select timestamp, activity_code
  from   log
  where  reference_id=1234
  order by timestamp desc
)
where rownum <= :n;

René Nyffenegger 15 年前

create table grep_like (
  id  number,
  dt  date,
  txt varchar2(10)
);


insert into grep_like values(10, sysdate -  9/24/60/60, 'foo');
insert into grep_like values(30, sysdate -  8/24/60/60, 'bar');
insert into grep_like values(39, sysdate -  2/24/60/60, 'baz');
insert into grep_like values(22, sysdate -  5/24/60/60, '***');
insert into grep_like values(87, sysdate -  7/24/60/60, '###');
insert into grep_like values(57, sysdate -  4/24/60/60, '!!!');
insert into grep_like values(32, sysdate +  1/24/60/60, '---');
insert into grep_like values(99, sysdate - 12/24/60/60, '...');
insert into grep_like values(18, sysdate -  1/24/60/60, 'noo');
insert into grep_like values(20, sysdate - 10/24/60/60, 'moo');
insert into grep_like values(81, sysdate -  0/24/60/60, 'huh');


select p.dt, p.txt
from (
  select r.dt, r.txt, r.r,
         max(case when r.id = 57 then r.r else 0 end) over () p
  from (
    select dt, txt, id,
           row_number() over (order by dt)  r
      from grep_like
  ) r
) p
where 
  p.r - p.p between -1 and 1
;

Thorsten 15 年前

另一个想法是,无论采用哪种方式,都可以抓取几分钟的“条目”:

WITH ts as (
    SELECT timestamp
    FROM log_table
    WHERE reference_id = 1234)
SELECT *
FROM log_table join ts
WHERE timestamp > ts.timestamp - 5 minutes
AND timestamp < ts.timestamp + 5 minutes

当然,必须根据您的数据库系统来实现“+/-5分钟”。

这可能比获得一个行号并用它来定义“窗口”要容易一些,但它可能不能满足您的要求。