SQL查询：基于客户最后交易日期获取近24个月交易数据

思路说明 #

把Table2的交易记录和Table1中对应客户的最后交易日期关联，筛选出交易日期在「最后交易日期往前推24个月」到「最后交易日期」之间的所有记录即可。

通用SQL代码 #

SELECT t2.Customer, t2.Transactionid, t2.TransactionDate
FROM Table2 t2
JOIN Table1 t1 ON t2.Customer = t1.Customer
WHERE t2.TransactionDate >= DATEADD(MONTH, -24, t1.Date_last_transacted)
  AND t2.TransactionDate <= t1.Date_last_transacted;

不同数据库适配代码 #

• MySQL/MariaDB：用DATE_SUB替代DATEADD

SELECT t2.Customer, t2.Transactionid, t2.TransactionDate
FROM Table2 t2
JOIN Table1 t1 ON t2.Customer = t1.Customer
WHERE t2.TransactionDate >= DATE_SUB(t1.Date_last_transacted, INTERVAL 24 MONTH)
  AND t2.TransactionDate <= t1.Date_last_transacted;

• PostgreSQL：直接用INTERVAL做日期运算

SELECT t2.Customer, t2.Transactionid, t2.TransactionDate
FROM Table2 t2
JOIN Table1 t1 ON t2.Customer = t1.Customer
WHERE t2.TransactionDate >= t1.Date_last_transacted - INTERVAL '24 months'
  AND t2.TransactionDate <= t1.Date_last_transacted;

额外优化：无需依赖Table1的版本 #

如果Table1不是物理表，只是从Table2聚合出的临时结果，直接内嵌子查询即可，不用单独创建Table1：

SELECT t2.Customer, t2.Transactionid, t2.TransactionDate
FROM Table2 t2
JOIN (
    SELECT Customer, MAX(TransactionDate) AS Date_last_transacted
    FROM Table2
    GROUP BY Customer
) t1 ON t2.Customer = t1.Customer
WHERE t2.TransactionDate >= DATEADD(MONTH, -24, t1.Date_last_transacted)
  AND t2.TransactionDate <= t1.Date_last_transacted;

内容的提问来源于stack exchange，提问作者Tinkerbelle_codes