通过加入相同日期或最接近的先前日期(不仅仅是精确匹配)来合并两个表 [英] Combine two tables by joining on the same date or closest prior date (not just exact matches)

问题描述

我有两个表:

• CustomerID
• 有效期
• Lead_Source

和

• CustomerID
• Product_Interest_Date
• Product_Interest

我想两个创建一个表，其中对于每个CustomerID，每个Product_Interest都连接到Lead_Source，该Lead_Source是最接近的日期(但不晚于此).决赛桌将是:

I want two create a single table where, for each CustomerID, each Product_Interest in connected to a the Lead_Source that is the closest date (but not after). The final table would be:

• CustomerID
• Product_Interest_Date
• Product_Interest
• Lead_Date(时间上最接近Product_Interest_Date的条目)
• Lead_Source(最接近Lead_Date的Lead_Source)

到目前为止，我可以联接表格，并创建一个新字段来计算最接近的日期而不用重复，但是当我尝试使用Min进行分组时，我仍然会得到多个排列(每个Lead_Date到每个Product_Interest).这是代码:

So far, I can join the tables, and create a new field that calculates the closest date without going over, but when I try to Group using Min, I still get multiple permutations (every Lead_Date to every Product_Interest). Here is the code:

SELECT Min(Int(Abs([Test_PI]![Product_Interest_Date]-[Test_Leads]![Lead_Date])))
       AS Lead_PI_Link, 
       Test_Leads.CustomerID,
       Test_PI.Product_Interest_Date, 
       Test_PI.Product_Interest,
       Test_Leads.Lead_Date, 
       Test_Leads.Lead_Source
FROM Test_Leads INNER JOIN Test_PI ON Test_Leads.CustomerID = Test_PI.CustomerID
GROUP BY Test_Leads.CustomerID,
         Test_PI.Product_Interest_Date,
         Test_PI.Product_Interest, 
         Test_Leads.Lead_Date,
         Test_Leads.Lead_Source
HAVING (((Test_Leads.CustomerID)="C6UJ9A002Q2P"));

此客户ID在Test_Leads中有4个条目，在Product_Interest中有4个条目.该查询的结果提供了16个结果，而不是所需的4个结果.如果日期完全匹配，我可以添加一个条件，使日期差为"0"，但是，有时这些日期会偏移1天，有时很多天.

This CustomerID has 4 entries in Test_Leads, and 4 entries Product_Interest. The result of this query gives 16 results in stead of the desired 4. If the dates were an exact match, I could add a criteria that the date difference was "0", however, sometimes these dates are offset by a 1 day, sometimes many days.

我正在使用Access，并且希望使用本机"解决方案，但是现在可以解决所有问题！

I am using Access, and would prefer a "native" solution, but am up for anything at this point!

推荐答案

Test_PI

CustomerID  Product_Interest_Date  Product_Interest
----------  ---------------------  ----------------
         1  2014-09-07             Interest1       
         1  2014-09-08             Interest2       
         1  2014-09-15             Interest3       
         1  2014-09-28             Interest4       

Test_Leads

Test_Leads

CustomerID  Lead_Date   Lead_Source
----------  ----------  -----------
         1  2014-09-07  Source1    
         1  2014-09-14  Source2    
         2  2014-09-15  Source3    
         1  2014-09-21  Source4    

此处的技巧是使用不相等的联接作为子查询的一部分，以标识每个Product_Interest_Date的最新Lead_Date.查询

The trick here is to use an unequal join as part of a subquery to identify the most recent Lead_Date for each Product_Interest_Date. The query

SELECT 
    pi.CustomerID, 
    pi.Product_Interest_Date, 
    l.Lead_Date
FROM 
    Test_PI pi 
    INNER JOIN 
    Test_Leads l
        ON pi.CustomerID = l.CustomerID 
            AND pi.Product_Interest_Date >= l.Lead_Date

返回

CustomerID  Product_Interest_Date  Lead_Date 
----------  ---------------------  ----------
         1  2014-09-07             2014-09-07
         1  2014-09-08             2014-09-07
         1  2014-09-15             2014-09-07
         1  2014-09-15             2014-09-14
         1  2014-09-28             2014-09-07
         1  2014-09-28             2014-09-14
         1  2014-09-28             2014-09-21

请注意，对于09-15如何返回两场比赛，对于09-28如何返回三场比赛.我们只对最新查询感兴趣，因此我们将对该查询进行一些调整

Notice how two matches are returned for 09-15 and three matches are returned for 09-28. We're only interested in the latest ones, so we'll tweak that query slightly

SELECT 
    pi.CustomerID, 
    pi.Product_Interest_Date, 
    Max(l.Lead_Date) AS MaxOfLead_Date
FROM 
    Test_PI pi 
    INNER JOIN 
    Test_Leads l
        ON pi.CustomerID = l.CustomerID 
            AND pi.Product_Interest_Date >= l.Lead_Date
GROUP BY 
    pi.CustomerID, 
    pi.Product_Interest_Date 

返回

CustomerID  Product_Interest_Date  MaxOfLead_Date
----------  ---------------------  --------------
         1  2014-09-07             2014-09-07    
         1  2014-09-08             2014-09-07    
         1  2014-09-15             2014-09-14    
         1  2014-09-28             2014-09-21    

现在，我们可以将两个表与该查询结合在一起，将它们全部拉在一起

Now we can JOIN the two tables together with that query to pull it all together

SELECT 
    Test_PI.CustomerID,
    Test_PI.Product_Interest_Date,
    Test_PI.Product_Interest,
    Test_Leads.Lead_Date,
    Test_Leads.Lead_Source
FROM
    (
        Test_PI
        INNER JOIN
        (
            SELECT 
                pi.CustomerID, 
                pi.Product_Interest_Date, 
                Max(l.Lead_Date) AS MaxOfLead_Date
            FROM 
                Test_PI pi 
                INNER JOIN 
                Test_Leads l
                    ON pi.CustomerID = l.CustomerID 
                        AND pi.Product_Interest_Date >= l.Lead_Date
            GROUP BY 
                pi.CustomerID, 
                pi.Product_Interest_Date 
        ) latest
            ON Test_PI.CustomerID = latest.CustomerID
                AND Test_PI.Product_Interest_Date = latest.Product_Interest_Date
    )
    INNER JOIN
    Test_Leads
        ON Test_Leads.CustomerID = latest.CustomerID
            AND Test_Leads.Lead_Date = latest.MaxOfLead_Date

返回

CustomerID  Product_Interest_Date  Product_Interest  Lead_Date   Lead_Source
----------  ---------------------  ----------------  ----------  -----------
         1  2014-09-07             Interest1         2014-09-07  Source1    
         1  2014-09-08             Interest2         2014-09-07  Source1    
         1  2014-09-15             Interest3         2014-09-14  Source2    
         1  2014-09-28             Interest4         2014-09-21  Source4    

这篇关于通过加入相同日期或最接近的先前日期(不仅仅是精确匹配)来合并两个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！