There are two call tables: calls1 and calls2. The relationship between the tables is as follows:

calls1.outer_phone = calls2.caller_phone

Several lines from calls2 can correspond to one line in calls1. The task is as follows: pull out all calls with calls1 in conjunction with calls2, but from calls2 there is only one call (if there is one) with which abs (calls2.call_time-calls1.call_time) <= 10 minutes and you only need to take the one closest to calls1.call_time call

Here's how it should turn out: enter image description here

Here is my request, but this is not what is needed:

 SELECT "calls1".*, "calls2"."id" as "call_id", "calls2"."calltime" as "call_calltime", ABS(DATE_PART('minute', "calls1"."call_time" - "calls2"."calltime" )) FROM "calls1" left join "calls2" ON calls1.outer_phone=calls2.caller_phone WHERE ("calls1"."id" in (1,2,3)) AND (ABS(DATE_PART('minute', calls1.call_time-calls2.calltime ))<=10)
  • The condition for 10 minutes is binding. Calculate the minimum deviation. This is a subquery that gives the time of the records to be selected. And no Left Join in principle - strictly Inner. - Akina

1 answer 1

Code for Oracle.

If calls to call2 always go strictly after (for example, this is a return call), then it turns out, you must select the first call, if it was within 10 minutes, or nothing, if after. It's easier here:

 select c1.*, c2.id as "call_id", c2.calltime as "call_calltime", round((c2.call_time - c1.call_time) * 1440) delay -- число минут округленно from calls1 c1 left join ( select id, caller_phone, min(call_time) calltime from calls2 group by caller_phone, id ) c2 on c1.outer_phone = c2.caller_phone and abs(c2.call_time - c1.call_time) <= 1/144 -- 10 минут в оракле можно обозначить так, -- и в постргресе вроде тоже

If calls to call2 can be both before and after, then a little more difficult. We must first find out which of them is closest, and then join up with calls1 again to show those who calls2 empty calls2 :

 select c.*, cc.call_time, cc.delay from call1 c left join ( select c1.outer_phone, c2.call_time, min(abs(c2.call_time - c1.call_time)) delay from call1 c1 join call2 c2 on c1.outer_phone = c2.caller_phone where abs(c2.call_time - c1.call_time) <= 1/144 group by c1.outer_phone, c2.call_time ) cc on cc.outer_phone = c.outer_phone