Wählen Sie datasätze für den Bereich Vergleich

Ich bin in diesem einen saugen. Ich wünschte, ich könnte es in pure sql tun, aber an dieser Stelle wird jede Lösung tun.

Ich habe ta und tb Tabellen, die listn von Ereignissen enthalten, die ungefähr zur gleichen time aufgetreten sind. Das Ziel ist, "verwaiste" Aufzeichnungen von ta on tb . Z.B:

 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' ); 

Aber sagen wir, ich muss einen Schwellenwert von + -5 Sekunden verwenden. Die zu suchende Abfrage würde also etwa so aussehen:

  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 

(Geige: http://sqlfiddle.com/#!4/b58f7c/5 )

Die Regeln sind:

  • Ereignisse werden 1 zu 1 zugeordnet
  • Das nächstgelegene Ereignis in tb für ein bestimmtes Ereignis in ta wird als korrektes Mapping betrachtet.

Das heißt, die resultierende Abfrage sollte etwas zurückgeben

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

Obwohl die Beispielabfrage, die ich hier gesetzt habe, genau das Problem zeigt, das ich habe. Wenn sich die time überschneidet, ist es schwierig, systematisch die richtige Zeile auszuwählen.

dense_rank() scheint die Antwort zu sein, um die richtigen Zeilen auszuwählen, aber welche Partitionierung / sorting wird sie richtig platzieren?

Erwähnenswert ist, dass ich dies auf einem Oracle 11gR2 mache.

Es scheint, als ob dies mit einer einzigen SQL-statement möglich wäre, die die analytischen functionen von Oracle verwendet, vielleicht mit einer Kombination aus row_number (), lag () und max () over. Aber ich konnte einfach nicht meinen Kopf darum wickeln. Ich wollte weiterhin eine analytische function in eine andere einbetten, und ich glaube nicht, dass Sie das tun können. Sie können mit den allgemeinen Tabellenausdrücken schrittweise vorgehen, aber ich konnte nicht herausfinden, wie es funktioniert.

Aber eine prozedurale Lösung ist ziemlich einfach mit PL * SQL zusammen mit einer zusätzlichen Tabelle, um Ihr Ergebnis zu speichern. Ich verwende row_number (), um jeder Zeile in jeder Quellentabelle einen chronologischen Rang zuzuweisen. Sie möchten ein bestimmtes Ergebnis, daher ist es wichtig, einen Tiebreaker zu haben, falls Sie doppelte datesangaben haben, daher meine Bestellung von dt, id. Hier ist eine SQL-Fiddle-Demo .

Oder sehen Sie sich den folgenden Code an:

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