postgresql left join и MIN()

211

15 марта 2018, 07:54

Есть две таблицы звонков calls1 и calls2. Связь между таблицами следующая:

calls1.outer_phone = calls2.caller_phone

Одной строке в calls1 может соответствовать несколько строк из calls2. Задача следующая: вытащить все звонки с calls1 в связке с calls2, но из calls2 только один звонок(если он есть) у которого abs(calls2.call_time-calls1.call_time) <= 10 минут и нужно взять только самый ближний к calls1.call_time звонок.

Вот то, как все должно получиться:

Вот мой запрос, но это не то, что нужно:

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)

Answer 1

Код для Oracle.

Если звонки в call2 идут всегда строго после (например, это ответный звонок), то получается, надо выбрать первый звонок, если он был в течение 10 минут, или ничего, если после. Тут проще:

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 минут в оракле можно обозначить так, 
                                                         -- и в постргресе вроде тоже

Если звонки в call2 могут быть и до, и после, то тут сложнее немного. Надо сначала выяснить, который из них ближаший, а потом сджойнить с calls1 еще раз, чтобы показать тех, у кого в 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