Oracle sql에서 가장 가까운 날짜를 가져오는 방법

Oracle sql에서 가장 가까운 날짜를 가져오는 방법

예를 들어, 나는 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

그리고 T2

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

저는 T2에서 가장 가까운 T1에서 시간을 받아야 합니다.이 테이블 사이에는 아무런 관계가 없습니다.다음과 같은 것이어야 합니다.

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)

하지만 이해가 안 돼요.좋은 의견이라도 있나?

여러 개를 사용하는 다른 솔루션과 달리 데카르트 조인 하나만 사용하면 문제를 해결할 수 있습니다.시간은 VARCHAR2로 저장된다고 가정합니다.날짜로 저장되어 있는 경우 TO_DATE 기능을 제거할 수 있습니다.날짜로 저장되어 있는 경우(이를 적극 권장합니다) 날짜 부분을 제거해야 합니다.

좀 장황하게 만들어 놓았으니 무슨 일인지 뻔합니다.

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

기본적으로, 이것은 T1과 T2의 모든 시간 사이의 절대적인 차이를 구하고 T2만큼 가장 작은 차이를 고릅니다.ID; T1에서 데이터를 반환합니다.

여기는 SQL Fiddle 형식입니다.

덜 예쁜(짧지만 짧은) 형식은 다음과 같습니다.

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

이것이 당신이 찾고 있는 쿼리라고 생각합니다.

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;

t2.time - t1.time 부분은 그렇지 않으면 작동하지 않으므로 시간 열의 날짜 부분이 동일한지 확인합니다.

편집: 수락해 주셔서 감사하지만, 아래 벤의 답변이 더 좋습니다.Oracle 분석 기능을 사용하므로 성능이 훨씬 뛰어납니다.

여기서 이 행은 T1에서 해당 행을 선택합니다. 이 행은 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);

(mysql로 테스트해보니 ORA를 받지 않길 바랍니다)

편집: 마지막 코멘트에 의하면, 다음과 같이 되어야 합니다.

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)

제작물:

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

분석 함수를 사용하는 또 다른 방법.이상할 수도 있음 :)

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로...그리고 그 너머!

이 쿼리를 좀 길게 사용해 보십시오. 최적화해 보겠습니다.

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

언급URL : https://stackoverflow.com/questions/13929053/how-to-get-the-closest-dates-in-oracle-sql