使用SQL DATEADD与DATEDIFF函数实现拆分发票组筛选

需求规则 #

需要从包含vendor_id、invoice_id、created_dt、total_amount字段的发票表中，筛选符合以下所有条件的拆分发票组：

• 组内所有发票归属同一vendor_id
• 组内所有发票的创建时间最大间隔不超过90天
• 组内所有发票的总金额≥6000美元

实现思路 #

我们可以通过自关联+分组聚合的方式实现，核心逻辑如下：

1. 按供应商分组，对每个供应商的所有发票，以每张发票的创建时间作为90天窗口的起始点
2. 关联同一供应商下、创建时间在该窗口起始点到起始点+90天范围内的所有发票
3. 统计每个窗口的总金额，筛选符合金额要求的窗口，再对重复的发票组去重即可

完整SQL代码（基于SQL Server语法，兼容支持DATEADD/DATEDIFF的主流数据库） #

WITH invoice_date_convert AS (
    -- 先统一处理日期格式，避免字符串日期计算错误，可根据自身数据库类型调整转换函数
    SELECT 
        invoice_id,
        vendor_id,
        CONVERT(DATE, created_dt, 103) AS created_dt, -- 103对应dd/mm/yyyy格式，和示例日期格式匹配
        total_amount
    FROM invoice_table
),
vendor_invoice_windows AS (
    SELECT 
        t1.vendor_id,
        t1.created_dt AS window_start_dt,
        DATEADD(DAY, 90, t1.created_dt) AS window_end_dt,
        t2.invoice_id,
        t2.total_amount
    FROM invoice_date_convert t1
    -- 自关联拉取同一供应商、90天窗口内的所有发票
    INNER JOIN invoice_date_convert t2
        ON t1.vendor_id = t2.vendor_id
        AND t2.created_dt BETWEEN t1.created_dt AND DATEADD(DAY, 90, t1.created_dt)
),
window_amount_calc AS (
    SELECT 
        vendor_id,
        window_start_dt,
        window_end_dt,
        STRING_AGG(invoice_id, ',') AS group_invoice_ids, -- 拼接组内所有发票ID
        SUM(total_amount) AS group_total_amount
    FROM vendor_invoice_windows
    GROUP BY vendor_id, window_start_dt, window_end_dt
    -- 筛选总金额符合要求的组
    HAVING SUM(total_amount) >= 6000
)
-- 最终去重，避免同一发票组合被不同窗口统计多次
SELECT DISTINCT
    vendor_id,
    group_invoice_ids,
    group_total_amount,
    window_start_dt,
    window_end_dt,
    DATEDIFF(DAY, window_start_dt, window_end_dt) AS max_day_interval
FROM window_amount_calc

代码说明 #

• DATEADD(DAY, 90, t1.created_dt) 用来计算90天窗口的结束时间
• DATEDIFF(DAY, window_start_dt, window_end_dt) 用来校验组内最大日期间隔不超过90天
• 若使用MySQL，可将日期转换函数替换为STR_TO_DATE(created_dt, '%d/%m/%Y')，字符串拼接函数替换为GROUP_CONCAT(invoice_id)即可
• 针对示例数据，执行后会返回包含所有5张发票的分组，总金额为7311.85美元，符合筛选要求

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