在同一日期或最接近的日期（之前或之后）联接两个表

呼延承平

2023-03-14

问题内容：

我刚刚从Gord
Thompson那里得到了一个类似问题的大力帮助（通过在同一日期或最接近的先前日期（不仅是完全匹配）进行联接来合并两个表），但现在意识到我的数据不是我所期望的。事实证明，我的Lead_Dates可以晚于Product_Interest_Dates，这导致先前的SQL代码删除了这些情况。进一步来说：

我有两个表：

顾客ID
Lead_Date
Lead_Source

和

顾客ID
Product_Interest_Date
产品_兴趣

我想两个创建一个表，在该表中，对于每个CustomerID，每个Product_Interest都连接到Lead_Source，该Lead_Source是最接近的日期（
之前或之后 ）。决赛桌将是：

CustomerID
Product_Interest_Date
Product_Interest
Lead_Date (the closest entry in time to Product_Interest_Date)
Lead_Source (the Lead_Source of the closest Lead_Date)

我研究了Gord的代码，但无法理解。按照他的示例，以图形方式我要这样做： http: //i.stack.imgur.com/4ZVDV.jpg

序列 Stack Overflow NEW 1* 的SQL *

SELECT
pi.CustomerID, 
pi.Product_Interest_Date,
l.Lead_Date, 
Abs(pi.Product_Interest_Date-l.Lead_Date) AS Date_Gap

FROM 
Test_PI pi
INNER JOIN 
Test_Leads l

堆栈溢出新2

SELECT 
[Stack Overflow NEW 1].CustomerID,
[Stack Overflow NEW 1].Product_Interest_Date, 
Min([Stack Overflow NEW 1].Date_Gap) AS MinOfDate_Gap
FROM [Stack Overflow NEW 1]
GROUP BY [Stack Overflow NEW 1].CustomerID, 
[Stack Overflow NEW 1].Product_Interest_Date;

最终的

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 ([Stack Overflow NEW 2] 
 INNER JOIN [Stack Overflow NEW 1] 
   ON ([Stack Overflow NEW 2].CustomerID = [Stack Overflow NEW 1].CustomerID) 
    AND ([Stack Overflow NEW 2].Product_Interest_Date = [Stack Overflow NEW 1].Product_Interest_Date) 
    AND ([Stack Overflow NEW 2].MinOfDate_Gap = [Stack Overflow NEW 1].Date_Gap)) 
  ON (Test_PI.CustomerID = [Stack Overflow NEW 2].CustomerID) 
   AND (Test_PI.Product_Interest_Date = [Stack Overflow NEW 2].Product_Interest_Date))
 INNER JOIN Test_Leads 
 ON ([Stack Overflow NEW 1].CustomerID = Test_Leads.CustomerID) 
  AND ([Stack Overflow NEW 1].Lead_Date = Test_Leads.Lead_Date)
GROUP BY Test_PI.CustomerID, Test_PI.Product_Interest_Date, Test_PI.Product_Interest, Test_Leads.Lead_Date, Test_Leads.Lead_Source;

我试图将所有这些组合到一个代码中，并且无法通过SQL FROM错误！这是我的特定问题，如何用单个SQL代码编写此代码？

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 
             latest.CustomerID, 
             latest.Product_Interest_Date, 
             Min(latest.Date_Gap) AS Min_Date_Gap
      FROM 
           latest 
     ) latest1
INNER JOIN
  (SELECT 
             pi.CustomerID, 
             pi.Product_Interest_Date, 
             l.Lead_Date,
             Abs(pi.Product_Interest_Date - l.Lead_Date) AS Date_Gap
      FROM 
            Test_PI pi 
       INNER JOIN 
            Test_Leads l
       ON pi.CustomerID = l.CustomerID 
    ) latest
   ) 
 ON Test_PI.CustomerID = latest1.CustomerID AND Test_PI.Product_Interest_Date = latest1.Product_Interest_Date

INNER JOIN 
  Test_Leads
    ON Test_Leads.CustomerID = latest1.CustomerID
        AND Test_Leads.Lead_Date = latest1.Lead_Date

问题答案：

现在我们正在考虑过去和将来的[Lead_Date]值，我已经对测试数据进行了调整，以解决特殊情况

表：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

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

我们将从创建一个名为[Date_Gaps]的已保存Access查询开始。

SELECT
    pi.CustomerID, 
    pi.Product_Interest_Date,
    l.Lead_Date,
    Abs(DateDiff("d", pi.Product_Interest_Date, l.Lead_Date)) AS Date_Gap
FROM 
    Test_PI pi
    INNER JOIN 
    Test_Leads l
        ON pi.CustomerID = l.CustomerID

CustomerID  Product_Interest_Date  Lead_Date   Date_Gap
----------  ---------------------  ----------  --------
         1  2014-09-07             2014-09-07         0
         1  2014-09-08             2014-09-07         1
         1  2014-09-15             2014-09-07         8
         1  2014-09-28             2014-09-07        21
         1  2014-09-07             2014-09-14         7
         1  2014-09-08             2014-09-14         6
         1  2014-09-15             2014-09-14         1
         1  2014-09-28             2014-09-14        14
         1  2014-09-07             2014-09-16         9
         1  2014-09-08             2014-09-16         8
         1  2014-09-15             2014-09-16         1
         1  2014-09-28             2014-09-16        12

现在查询

SELECT 
    CustomerID,
    Product_Interest_Date,
    Min(Date_Gap) AS MinOfDate_Gap
FROM Date_Gaps
GROUP BY
    CustomerID, 
    Product_Interest_Date

退货

CustomerID  Product_Interest_Date  MinOfDate_Gap
----------  ---------------------  -------------
         1  2014-09-07                         0
         1  2014-09-08                         1
         1  2014-09-15                         1
         1  2014-09-28                        12

因此，如果我们只是简单地重新加入[Date_Gaps]查询以获取[Lead_Date]

SELECT
    mingap.CustomerID,
    mingap.Product_Interest_Date,
    Date_Gaps.Lead_Date
FROM
    Date_Gaps
    INNER JOIN
    (
        SELECT 
            CustomerID,
            Product_Interest_Date,
            Min(Date_Gap) AS MinOfDate_Gap
        FROM Date_Gaps
        GROUP BY
            CustomerID, 
            Product_Interest_Date
    ) mingap
        ON Date_Gaps.CustomerID = mingap.CustomerID
            AND Date_Gaps.Product_Interest_Date = mingap.Product_Interest_Date
            AND Date_Gaps.Date_Gap = mingap.MinOfDate_Gap

我们得到

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-14
         1  2014-09-15             2014-09-16
         1  2014-09-28             2014-09-16

请注意，我们在09-15期间获得了两次匹配，因为它们之间的间隔均为1天（之前和之后）。因此，我们需要通过使用Min(Lead_Date)（或Max(Lead_Date)选择）将上述查询包装在聚合查询中来打破这种束缚

SELECT
    CustomerID,
    Product_Interest_Date,
    Min(Lead_Date) AS MinOfLead_Date
FROM
    (
        SELECT
            mingap.CustomerID,
            mingap.Product_Interest_Date,
            Date_Gaps.Lead_Date
        FROM
            Date_Gaps
            INNER JOIN
            (
                SELECT 
                    CustomerID,
                    Product_Interest_Date,
                    Min(Date_Gap) AS MinOfDate_Gap
                FROM Date_Gaps
                GROUP BY
                    CustomerID, 
                    Product_Interest_Date
            ) mingap
                ON Date_Gaps.CustomerID = mingap.CustomerID
                    AND Date_Gaps.Product_Interest_Date = mingap.Product_Interest_Date
                    AND Date_Gaps.Date_Gap = mingap.MinOfDate_Gap
    )
GROUP BY
    CustomerID,
    Product_Interest_Date

给我们

CustomerID  Product_Interest_Date  MinOfLead_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-16

现在我们准备加入原始表

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
                CustomerID,
                Product_Interest_Date,
                Min(Lead_Date) AS MinOfLead_Date
            FROM
                (
                    SELECT
                        mingap.CustomerID,
                        mingap.Product_Interest_Date,
                        Date_Gaps.Lead_Date
                    FROM
                        Date_Gaps
                        INNER JOIN
                        (
                            SELECT 
                                CustomerID,
                                Product_Interest_Date,
                                Min(Date_Gap) AS MinOfDate_Gap
                            FROM Date_Gaps
                            GROUP BY
                                CustomerID, 
                                Product_Interest_Date
                        ) mingap
                            ON Date_Gaps.CustomerID = mingap.CustomerID
                                AND Date_Gaps.Product_Interest_Date = mingap.Product_Interest_Date
                                AND Date_Gaps.Date_Gap = mingap.MinOfDate_Gap
                )
            GROUP BY
                CustomerID,
                Product_Interest_Date
        ) closest
            ON Test_PI.CustomerID = closest.CustomerID
                AND Test_PI.Product_Interest_Date = closest.Product_Interest_Date
    )
    INNER JOIN
    Test_Leads
        ON Test_Leads.CustomerID = closest.CustomerID
            AND Test_Leads.Lead_Date = closest.MinOfLead_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-16  Source4

在同一日期或最接近的日期（之前或之后）联接两个表

相关阅读

相关文章

相关问答

相关工具

相关文档