Comment obtenir la plus proche des dates en sql Oracle

Par exemple, j'ai 2 tables:
T1

id time
1 18:12:02
2 18:46:57
3 17:49:44
4 12:19:24
5 11:00:01
6 17:12:45

et T2

id time
1 18:13:02
2 17:46:57

J'ai besoin d'obtenir le temps de T1 qui sont le plus proche de l'heure de T2. Il n'y a pas de relation entre ces tables.
Il devrait être quelque chose comme ceci:

select T1.calldatetime
from T1, T2 
where T1.calldatetime between 
T2.calldatetime-(
    select MIN(ABS(T2.calldatetime-T1.calldatetime))
    from T2, T1)
and
T2.calldatetime+(
    select MIN(ABS(T2.calldatetime-T1.calldatetime))
    from T2, T1)

Mais je ne peux pas l'obtenir. Des suggestions?

OriginalL'auteur fen1ksss | 2012-12-18

Je crois que c'est la requête que vous êtes à la recherche pour:

CREATE TABLE t1(id INTEGER, time DATE);
CREATE TABLE t2(id INTEGER, time DATE);

INSERT INTO t1 VALUES (1, TO_DATE ('18:12:02', 'HH24:MI:SS'));
INSERT INTO t1 VALUES (2, TO_DATE ('18:46:57', 'HH24:MI:SS'));
INSERT INTO t1 VALUES (3, TO_DATE ('17:49:44', 'HH24:MI:SS'));
INSERT INTO t1 VALUES (4, TO_DATE ('12:19:24', 'HH24:MI:SS'));
INSERT INTO t1 VALUES (5, TO_DATE ('11:00:01', 'HH24:MI:SS'));
INSERT INTO t1 VALUES (6, TO_DATE ('17:12:45', 'HH24:MI:SS'));

INSERT INTO t2 VALUES (1, TO_DATE ('18:13:02', 'HH24:MI:SS'));
INSERT INTO t2 VALUES (2, TO_DATE ('17:46:57', 'HH24:MI:SS'));

SELECT t1.*, t2.*
  FROM t1, t2,
       (  SELECT t2.id, MIN (ABS (t2.time - t1.time)) diff
            FROM t1, t2
        GROUP BY t2.id) b
 WHERE ABS (t2.time - t1.time) = b.diff;

Assurez-vous que les colonnes ont la même date de la partie, parce que le t2.le temps t1.la partie du temps ne fonctionne pas autrement.

MODIFIER: Merci pour l'accepter, mais Ben la réponse ci-dessous, c'est mieux. Il utilise Oracle des fonctions analytiques et effectue beaucoup mieux.

OriginalL'auteur mavroprovato

Vous n'avez qu'à utiliser un seul Cartésien se joindre à résoudre votre problème contrairement aux autres solutions, qui utilisent plusieurs. Je suppose que le temps est stocké comme un VARCHAR2. Si elle est stockée comme une date, alors vous pouvez supprimer le TO_DATE fonctions. Si elle est stockée comme une date (je vous recommande fortement), vous devrez supprimer la date portions

J'ai fait un peu verbeux donc il est évident que ce qui se passe.

select *
  from ( select id, tm
              , rank() over ( partition by t2id order by difference asc ) as rnk
           from ( select t1.*, t2.id as t2id
                       , abs( to_date(t1.tm, 'hh24:mi:ss') 
                              - to_date(t2.tm, 'hh24:mi:ss')) as difference
                    from t1
                   cross join t2
                         ) a
                 )
 where rnk = 1

Fondamentalement, il s'agit de la valeur absolue de la différence entre les temps T1 et T2, puis choisit la plus petite différence par T2 ID; de renvoyer les données à partir de T1.

Ici, il est en SQL Violon format.

La moins jolie (mais courte) format:

select *
  from ( select t1.*
              , rank() over ( partition by t2.id 
                                  order by abs(to_date(t1.tm, 'hh24:mi:ss') 
                                            - to_date(t2.tm, 'hh24:mi:ss'))
                                  ) as rnk
           from t1
          cross join t2
                ) a
 where rnk = 1

merci? il fonctionne!

OriginalL'auteur Ben

Celui-ci sélectionne cette ligne(s) à partir de T1, qui a/ont la plus petite distance de tout dans T2:

select T1.id, T1.calldatetime from T1, T2 
 where ABS(T2.calldatetime-T1.calldatetime)
         =( select MIN(ABS(T2.calldatetime-T1.calldatetime))from T1, T2);

(testé avec mysql, j'espère que vous n'obtenez pas une ORA)

Modifier: selon le dernier commentaire, il devrait être comme ça:

drop table t1;
drop table t2;
create table t1(id int, t time);
create table t2(id int, t time);

insert into t1 values (1, '18:12:02');
insert into t1 values (2, '18:46:57');
insert into t1 values (3, '17:49:44');
insert into t1 values (4, '12:19:24');
insert into t1 values (5, '11:00:01');
insert into t1 values (6, '17:12:45');

insert into t2 values (1, '18:13:02');
insert into t2 values (2, '17:46:57');

select ot2.id, ot2.t, ot1.id, ot1.t from t2 ot2, t1 ot1  
 where ABS(ot2.t-ot1.t)=
       (select min(abs(t2.t-t1.t)) from t1, t2 where t2.id=ot2.id)

Produit:

id      t       id      t
1       18:13:02        1       18:12:02
2       17:46:57        3       17:49:44

ce que l'on trouve la plus petite différence entre T2.calldatetime-T1.calldatetime et que devient juste une ligne de T1.calldatetime, ce n'est pas le même que j'ai besoin. J'ai besoin de la plus petite de toutes les lignes.
Hmm. Donc, vous voulez pour chaque ligne T1 la ligne avec la plus petite diff de en T2? ou pour chaque ligne T2 la plus petite diff en T1?
pour chaque ligne T2 la plus petite diff en T1

OriginalL'auteur pbhd

Une autre façon d'utiliser les fonctions analytiques.
Peut être étrange 🙂

select id, time,
case 
  when to_date(time, 'hh24:mi:ss') - to_date(lag_time, 'hh24:mi:ss') < to_date(lead_time, 'hh24:mi:ss') - to_date(time, 'hh24:mi:ss')
    then lag_time 
    else lead_time
  end closest_time
from (
select id, tbl,
  LAG(time, 1, null) OVER (ORDER BY time) lag_time, 
  time,
  LEAD(time, 1, null) OVER (ORDER BY time) lead_time 
from
  (
  select id, time, 1 tbl from t1
  union all
  select id, time, 2 tbl from t2
  )
)
where tbl = 2

À SQLFiddle... et au-delà!

+1 pour le retard et de plomb, de fonctions très utiles

OriginalL'auteur knagaev

Essayer cette requête de son peu long, je vais essayer de l'optimiser

select * from t1
where id in (
select id1 from 
(select id1,id2,
rank()  over (partition by id2 order by diff) rnk
from
(select distinct t1.id id1,t2.id id2,
round(min(abs(to_date(t1.time,'HH24:MI:SS') - to_date(t2.time,'HH24:MI:SS'))),2) diff 
from 
t1,t2
group by t1.id,t2.id) )
where rnk = 1);

OriginalL'auteur Ajith Sasidharan

Vous devez vous connecter pour publier un commentaire.