跨年度季度自定义周期如何关联聚合查询完成返点消费校验

两种常用方案可以实现跨年度/非标准周期的消费聚合关联，可根据你的规则复杂度选择：

方案1：内置周期映射逻辑（适合固定的少数非标准周期） #

核心思路是给每笔交易的日期匹配对应的自定义周期标识，后续聚合、关联逻辑和标准年/季度周期完全一致。
以11月至次年4月的返点周期为例，示例查询如下：

SELECT
    t.FacilityName,
    t.Department,
    t.Item,
    t.InvoiceDate,
    t.InvoiceSpend,
    CASE WHEN period_spend.TotalSpend > 1000000 THEN t.InvoiceSpend * 0.05
         ELSE 0 
    END AS RebateAmount
FROM Transactions t
INNER JOIN (
    SELECT
        -- 生成周期唯一标识：11-12月归属当年到下一年的周期，1-4月归属上一年到当年的周期
        CASE WHEN MONTH(InvoiceDate) >= 11 
             THEN CONCAT('REBATE_PERIOD_', YEAR(InvoiceDate), '_', YEAR(InvoiceDate)+1)
             ELSE CONCAT('REBATE_PERIOD_', YEAR(InvoiceDate)-1, '_', YEAR(InvoiceDate))
        END AS RebatePeriod,
        SUM(InvoiceSpend) AS TotalSpend
    FROM Transactions
    WHERE MONTH(InvoiceDate) IN (11,12,1,2,3,4) -- 提前过滤不需要参与该返点的交易
    GROUP BY CASE WHEN MONTH(InvoiceDate) >= 11 
                  THEN CONCAT('REBATE_PERIOD_', YEAR(InvoiceDate), '_', YEAR(InvoiceDate)+1)
                  ELSE CONCAT('REBATE_PERIOD_', YEAR(InvoiceDate)-1, '_', YEAR(InvoiceDate))
             END
) period_spend 
-- 明细和周期消费总额通过周期标识关联
ON period_spend.RebatePeriod = CASE WHEN MONTH(t.InvoiceDate) >= 11 
                                    THEN CONCAT('REBATE_PERIOD_', YEAR(t.InvoiceDate), '_', YEAR(t.InvoiceDate)+1)
                                    ELSE CONCAT('REBATE_PERIOD_', YEAR(t.InvoiceDate)-1, '_', YEAR(t.InvoiceDate))
                               END

方案2：独立配置周期表（适合周期多、规则经常调整的场景） #

如果返点规则经常变动、同时存在多类不同周期的返点，可以单独建一张返点周期配置表，所有周期、门槛、返点比例都存在表中，不用改SQL就能更新规则。

配置表示例（RebatePeriods） #

关联查询示例 #

SELECT
    t.FacilityName,
    t.Department,
    t.Item,
    t.InvoiceDate,
    t.InvoiceSpend,
    CASE WHEN period_spend.TotalSpend >= rp.MinSpendThreshold THEN t.InvoiceSpend * rp.RebateRate
         ELSE 0
    END AS RebateAmount
FROM Transactions t
-- 匹配交易所属的返点周期
INNER JOIN RebatePeriods rp 
    ON t.InvoiceDate >= rp.StartDate 
    AND t.InvoiceDate < DATEADD(day, 1, rp.EndDate) -- 避免时间戳精度问题导致的边界数据遗漏
INNER JOIN (
    SELECT
        rp.PeriodID,
        SUM(t.InvoiceSpend) AS TotalSpend
    FROM Transactions t
    INNER JOIN RebatePeriods rp 
        ON t.InvoiceDate >= rp.StartDate 
        AND t.InvoiceDate < DATEADD(day, 1, rp.EndDate)
    GROUP BY rp.PeriodID
) period_spend ON rp.PeriodID = period_spend.PeriodID

注意事项 #

• 周期边界判断优先用小于周期结束后第一天的写法代替小于等于周期最后一天，避免发票时间带时分秒时出现漏算、多算
• 若同一笔交易需要参与多类返点计算，给每类返点加单独的规则类型标识，避免重复统计
• 聚合总消费额前要提前过滤作废、红冲的无效发票，避免门槛校验错误

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