选择记录进行范围比较

Question

我在这个问题上很糟糕。希望我能在纯sql中做到这一点，但在这一点上，任何解决方案都可以。

我有ta和tb表，其中包含大约在同时发生的事件列表。目标是在tb上找到ta的"孤儿"记录。例如:

create table ta ( dt date, id varchar(1));
insert into ta values( to_date('20130101 13:01:01', 'yyyymmdd hh24:mi:ss') , '1' );
insert into ta values( to_date('20130101 13:01:02', 'yyyymmdd hh24:mi:ss') , '2' );
insert into ta values( to_date('20130101 13:01:03', 'yyyymmdd hh24:mi:ss') , '3' );

create table tb ( dt date, id varchar(1));
insert into tb values( to_date('20130101 13:01:5', 'yyyymmdd hh24:mi:ss') , 'a' );
insert into tb values( to_date('20130101 13:01:6', 'yyyymmdd hh24:mi:ss') , 'b' );

但是假设我必须使用+-5秒的阈值。因此，要查找的查询看起来像:

  select
    ta.id ida,
    tb.id idb
  from
    ta, tb
  where 
    tb.dt between (ta.dt - 5/86400) and (ta.dt + 5/86400)
  order by 1,2 

(小提琴:http://sqlfiddle.com/! 4/b58f7c/5)

规则如下:

• 事件映射为1到1
• tb上与ta中给定事件最接近的事件将被认为是正确的映射。

也就是说，结果查询应该返回类似于 的内容

IDA | IDB
1   | a
2   | b
3   | null  <-- orphan event

虽然示例查询我已经把这里显示了我所遇到的问题。当时间重叠时，很难系统地选择正确的行。

dense_rank()似乎是选择正确行的答案，但是什么分区/排序将把它们放在正确的位置?

值得一提的是，我是在Oracle 11gR2上做的。

Answer 1

使用Oracle的分析函数，也许使用row_number()， lag()和max() over的某种组合，应该可以使用单个SQL语句。但我就是无法理解它。我一直想把一个分析函数嵌入到另一个分析函数中，我认为你做不到。您可以使用公共表表达式逐步进行，但我不知道如何使其工作。

但是过程解决方案是相当直接的，使用PL*SQL和一个额外的表来存储结果。我使用row_number()为每个源表中的每一行分配一个时间顺序。您想要一个确定的结果，所以在您有重复的日期时间的情况下，有一个平局打破是很重要的，因此我的order by (dt, id)。下面是一个SQL-Fiddle演示。

或者看看下面的代码:

create table result ( 
  dif number, 
  ida varchar(1),
  idb varchar(1),
  dta date,
  dtb date
);
declare
  prevA integer := 0;
  prevB integer := 0;
begin
  for rec in (
    with 
    ordered_ta as (
      select dt dta,
             id ida,
             row_number() over (order by dt, id) rowNumA
        from ta
    ),
    ordered_tb as (
      select dt dtb,
             id idb, 
             row_number() over (order by dt, id) rowNumB 
        from tb
    )
    select ta.*,
           tb.*,
           abs(dta - dtb) * 86400 dif
      from ordered_ta ta
      join ordered_tb tb
        on dtb between (dta - 5/86400) and (dta + 5/86400)
     order by rowNumA, rowNumB
  )
  loop
    if rec.rowNumA > prevA and rec.rowNumB > prevB then
      prevA := rec.rowNumA;
      prevB := rec.rowNumB;
      insert into result values (
        rec.dif,
        rec.ida,
        rec.idb,
        rec.dta,
        rec.dtb
      );
    end if;
  end loop;
end;
/
select * from result
union all
select null dif, id ida, null idb, dt dta, null dtb
  from ta
 where id not in (select ida from result)
union all
select null dif, null ida, id idb, null dta, dt dtb
  from tb
 where id not in (select idb from result)
;