Присоединяйтесь по идентификатору и ближайшей дате

У меня две таблицы:

Таблица 1, содержащая телефонные звонки (на каждый CustomerID приходится не более одного телефонного звонка в день):

ActicityID  CustomerID   PhoneDate
1           A            2019-11-01
2           A            2019-12-01
3           A            2019-12-20
4           B            2019-11-01
5           B            2019-11-20
6           C            2019-11-03
7           D            2019-11-03
8           D            2019-12-01
9           E            2019-11-05
10          F            2019-11-01

Таблица 2, которая содержит заказы (OrdDate - это дата размещения заказа, а BillingDate - дата, когда заказ был списан)

CustomerID  OrdDate      BillingDate
A           2019-12-03   2019-12-04
A           2019-12-21   2019-12-21
B           2019-11-03   2019-11-10
D           2019-12-02   2019-12-02
F           2019-11-02   2019-11-02

Я хочу присоединиться к столам. В объединенной таблице должно быть такое же количество строк, что и в таблице 1. Итак, в основном я хочу знать, был ли заказ после телефонного звонка. Проблема в том, что если просто присоединиться к CustomerID, я получу OrdDat и BillingDate за каждого клиента, который когда-либо делал заказ. Например, клиент A сделал заказ после звонка 01.12.2019 и после звонка 20.12.2019, но не после первого звонка.

Итак, мой желаемый результат был бы

ActicityID  CustomerID   PhoneDate    OrdDate     BillingDate 
1           A            2019-11-01   NULL        NULL
2           A            2019-12-01   2019-12-03  2019-12-04
3           A            2019-12-20   2019-12-21  2019-12-21
4           B            2019-11-01   2019-11-03  2019-11-10
5           B            2019-11-20   NULL        NULL
6           C            2019-11-03   NULL        NULL
7           D            2019-11-03   NULL        NULL
8           D            2019-12-01   2019-12-02  2019-12-02
9           E            2019-11-05   NULL        NULL
10          F            2019-11-01   2019-11-02  2019-11-02

Я думаю, мне нужно присоединиться к CustomerID и ближайшей дате между PhoneDate и OrdDate, но мои знания SQL довольно ограничены, и я не мог понять, как это сделать.


sql sql-server
person dyel    schedule 03.02.2020    source источник
comment
что ты пробовал? Вы изучали различные типы соединений и как их выполнять?   -  person Iria    schedule 03.02.2020
comment
Вы должны использовать левое соединение   -  person Prajakta Kale    schedule 03.02.2020

Ответы (2)


arrow_upward
2
arrow_downward

Я думаю, вы можете делать все, что хотите, используя lead(), чтобы назначить дату следующего звонка, а затем просто присоединяясь:

select a.*, b.orddate, b.billdate
from (select a.*,
             lead(phonedate) over (partition by customerid order by phonedate) as next_pd
      from a
     ) a left join
     b
     on b.customerid = a.customerid and
        b.orddate >= a.phonedate and
        (b.orddate < a.next_pd or a.next_pd is null);
person Gordon Linoff    schedule 03.02.2020
comment
Спасибо за ваш ответ, но я получаю много дубликатов, когда пытаюсь это сделать (в таблице A ~ 52000 строк, я остановил запрос после 1000000 строк) - person dyel; 03.02.2020
comment
@dyel. . . Я пропустил, что вы также хотите присоединиться по идентификатору клиента. Это решит проблему дублирования. - person Gordon Linoff; 03.02.2020
comment
Спасибо за вашу помощь, я все еще получаю несколько дубликатов (~ 100 из 52000 наблюдений), но это, вероятно, из-за других столбцов, которые я не упомянул в своем вопросе. - person dyel; 04.02.2020
comment
Ради интереса, @GordonLinoff, есть ли преимущества у вашего ответа по сравнению с моей версией, приведенной ниже? Я стараюсь избегать такой функциональности по сравнению с более простым SQL, если нет явного преимущества в его использовании. - person Paul; 04.02.2020
comment
@Павел . . . lead() более прямолинейный, чем коррелированные подзапросы (будучи стандартными и более краткими). Это также дает больше возможностей для оптимизации. - person Gordon Linoff; 04.02.2020

arrow_upward
1
arrow_downward

Вам нужно использовать подзапрос, чтобы ограничить другую таблицу, ссылаясь на TOP 1 связанную запись даты ...

SELECT
    ActivityID, 
    CustomerID, 
    PhoneDate,
   (SELECT TOP (1) 
        OrderDate
    FROM 
        dbo.CustomerBilling AS b
    WHERE 
        a.PhoneDate < OrderDate AND 
        a.CustomerID = CustomerID
    ORDER BY OrderDate) AS BillingDate
FROM 
    dbo.Activity AS a
person Paul    schedule 03.02.2020