使用SQL查询发票数据中的18个月交易间隔问题

需求说明 #

现有一张记录2020年7月1日以来所有发票的表（记为m），包含CustomerID和InvoiceDt字段。需为每个CustomerID执行以下判断：

• 若存在交易间隔达18个月的情况，获取最近一次18个月间隔后的首笔交易日期；
• 若客户首笔发票日期晚于2022年1月1日（即与数据起始日间隔超18个月），则获取其首笔发票日期。

输入输出示例 #

输入表（m） #

期望输出（startDates） #

已实现的Python循环代码 #

import pandas as pd
import numpy as np

# 假设m是已加载的DataFrame
startDates = pd.DataFrame(index=m.CustomerID.unique(), columns=["ModStartDate"])

for cid in m.CustomerID.unique():
    m1 = m[m.CustomerID == cid].sort_values("InvoiceDt")
    m1["InvShift"] = m1.InvoiceDt.shift(1)
    m1["Gap"] = ((m1.InvoiceDt - m1.InvShift)/np.timedelta64(1, 'D'))/30.42
    m1["18MonthGap"] = m1.Gap >= 18
    if m1["18MonthGap"].sum() > 0:
        # 获取最后一次出现18个月间隔后的首笔交易日期
        target_row = m1[m1["18MonthGap"]].iloc[-1]
        startDates.loc[cid, "ModStartDate"] = target_row.InvoiceDt
    elif m1.iloc[0].InvoiceDt > pd.to_datetime("2022-01-01"):
        startDates.loc[cid, "ModStartDate"] = m1.iloc[0].InvoiceDt

# 清理空值并重置索引
startDates = startDates.dropna().reset_index().rename(columns={"index": "CustomerID", "ModStartDate": "Modified Start Date"})

纯SQL解决方案（无循环） #

以下SQL利用窗口函数实现需求，无需循环逻辑：

WITH customer_invoices AS (
    -- 为每个客户的交易按日期排序，计算与上一笔交易的间隔月数
    SELECT 
        CustomerID,
        InvoiceDt,
        LAG(InvoiceDt) OVER (PARTITION BY CustomerID ORDER BY InvoiceDt) AS prev_invoice_dt,
        -- 计算间隔月数（以BigQuery语法为例，其他数据库需调整）
        DATE_DIFF(InvoiceDt, LAG(InvoiceDt) OVER (PARTITION BY CustomerID ORDER BY InvoiceDt), MONTH) AS gap_months,
        -- 获取客户的首笔交易日期
        MIN(InvoiceDt) OVER (PARTITION BY CustomerID) AS first_invoice_dt
    FROM m
    WHERE InvoiceDt >= '2020-07-01' -- 过滤数据起始日之后的记录
),
gap_records AS (
    -- 筛选出间隔≥18个月的交易记录，并标记最近的那笔
    SELECT 
        CustomerID,
        InvoiceDt,
        ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY InvoiceDt DESC) AS rn
    FROM customer_invoices
    WHERE gap_months >= 18
)
-- 最终结果：优先取间隔后的日期，否则判断首笔日期是否符合条件
SELECT 
    c.CustomerID,
    CASE
        WHEN g.InvoiceDt IS NOT NULL THEN g.InvoiceDt
        WHEN c.first_invoice_dt > '2022-01-01' THEN c.first_invoice_dt
    END AS `Modified Start Date`
FROM (
    -- 获取所有客户及其首笔交易日期
    SELECT DISTINCT CustomerID, first_invoice_dt
    FROM customer_invoices
) c
LEFT JOIN (
    -- 取每个客户最近一次间隔≥18个月的交易记录
    SELECT CustomerID, InvoiceDt
    FROM gap_records
    WHERE rn = 1
) g ON c.CustomerID = g.CustomerID
-- 只保留有符合条件日期的客户
WHERE CASE
        WHEN g.InvoiceDt IS NOT NULL THEN 1
        WHEN c.first_invoice_dt > '2022-01-01' THEN 1
        ELSE 0
      END = 1
ORDER BY c.CustomerID;

数据库语法适配 #

不同数据库的日期计算函数略有差异，需根据实际使用场景调整：

• MySQL：将DATE_DIFF替换为TIMESTAMPDIFF(MONTH, prev_invoice_dt, InvoiceDt)
• PostgreSQL：用EXTRACT(MONTH FROM AGE(InvoiceDt, prev_invoice_dt))计算间隔月数
• SQL Server：使用DATEDIFF(MONTH, prev_invoice_dt, InvoiceDt)

逻辑解释 #

1. customer_invoices CTE：为每个客户的交易按日期排序，用LAG获取上一笔交易日期，计算间隔月数，同时提取首笔交易日期。
2. gap_records CTE：筛选出间隔≥18个月的交易，并用ROW_NUMBER按日期倒序标记，确保取到最近的那笔间隔后的交易。
3. 最终查询：关联客户表和间隔记录，优先取间隔后的日期；无间隔记录时判断首笔日期是否符合要求，最后过滤掉没有符合条件日期的客户（比如示例中的CustomerID 2）。

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