在同一日期或最接近的日期(之前或之后)联接两个表 [英] Join two tables on the same date or closest date (before or after)
问题描述
我刚刚从Gord Thompson那里得到了一个类似问题的大力帮助(通过在同一日期或最接近的先前日期进行联接来组合两个表(而不仅仅是完全匹配))，但现在意识到我的数据不是我期望的.事实证明，我的Lead_Dates可以晚于Product_Interest_Dates，这导致先前的SQL代码删除了这些情况.更具体地说:
I just got great help from Gord Thompson on a similar question (Combine two tables by joining on the same date or closest prior date (not just exact matches)) but now realize my data is not what I expected. It turns out I can have Lead_Dates later than Product_Interest_Dates and this is causing the previous SQL code to drop those cases. More specifically:
我有两个表:
- CustomerID
- 有效期
- Lead_Source
和
- CustomerID
- Product_Interest_Date
- Product_Interest
我想两个创建一个表，其中对于每个CustomerID，每个Product_Interest都连接到最接近日期(之前或之后)的Lead_Source.决赛桌将是:
I want two create a single table where, for each CustomerID, each Product_Interest is connected to a the Lead_Source that is the closest date (either before or after). The final table would be:
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
I studied Gord's code but cannot bring this home. Following his example, graphically I want this: http://i.stack.imgur.com/4ZVDV.jpg
序列的SQL 堆栈溢出新1
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代码编写此代码?
I tried to combine all these into a single code, and cannot get past the SQL FROM error! This is my specific question, how do I write this in a single SQL code?
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]值，我已经将测试数据调整为特殊情况
Now that we're considering both past and future [Lead_Date] values I've tweaked the test data cover a special case
表: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
Table: 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查询
We'll start by creating a saved Access query named [Date_Gaps]
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]
so if we simply join back into the [Date_Gaps] query to get the [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
请注意，我们在9月15日获得了两次匹配，因为它们之间的间隔均为1天(前后).因此，我们需要通过使用Min(Lead_Date)(或您选择的Max(Lead_Date))将上述查询包装在聚合查询中来打破这种束缚.
Notice that we get two hits for 09-15 because they both have a gap of 1 day (before and after). So, we need to break that tie by wrapping the above query in an aggregation query using Min(Lead_Date) (or Max(Lead_Date), your choice)
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
现在我们准备加入原始表
So now we're ready to JOIN up the original tables
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
这篇关于在同一日期或最接近的日期(之前或之后)联接两个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
