SQL需求：达标后计算销售佣金支付金额（COMMISSION_PMT）

问题背景 #

我们向销售代理支付从客户处收取费用的佣金，但仅在达到阈值后支付。示例中该客户的阈值为3166.09美元，到2021年8月12日的交易时已达标且略有超额，需计算最后一列COMMISSION_PMT。

核心计算逻辑 #

佣金需按以下规则计算：

• 累计收费未达阈值时，所有交易的佣金为0
• 首次累计超过阈值的交易，仅对超额部分计算佣金
• 达标后的所有交易，按全额交易金额计算佣金

常见错误SQL示例 #

假设当前错误SQL未考虑累计阈值判断，直接对所有交易计算佣金：

SELECT 
    transaction_date,
    fee_collected,
    fee_collected * 0.1 AS commission_pmt
FROM customer_transactions
WHERE customer_id = 'XXX'
ORDER BY transaction_date;

修正后的SQL（以MySQL为例） #

使用窗口函数计算累计金额，结合分支判断实现正确佣金计算：

WITH cumulative_transactions AS (
    SELECT 
        transaction_date,
        fee_collected,
        -- 计算截至当前交易的累计收费
        SUM(fee_collected) OVER (ORDER BY transaction_date) AS total_collected,
        -- 获取上一笔交易的累计收费，用于判断是否为首次达标交易
        LAG(SUM(fee_collected) OVER (ORDER BY transaction_date)) OVER (ORDER BY transaction_date) AS prev_total_collected
    FROM customer_transactions
    WHERE customer_id = 'XXX'
)
SELECT 
    transaction_date,
    fee_collected,
    CASE
        -- 累计未达阈值，佣金为0
        WHEN total_collected <= 3166.09 THEN 0
        -- 首次达标，仅计算超额部分的佣金
        WHEN COALESCE(prev_total_collected, 0) <= 3166.09 THEN (total_collected - 3166.09) * 0.1
        -- 达标后的交易，按全额计算佣金
        ELSE fee_collected * 0.1
    END AS commission_pmt
FROM cumulative_transactions
ORDER BY transaction_date;

逻辑说明 #

• SUM() OVER (ORDER BY transaction_date)：按交易时间排序，逐笔计算累计收费金额
• LAG()函数：获取上一笔交易的累计金额，配合COALESCE处理首笔交易的空值场景
• CASE分支：精准区分未达标、首次达标、达标后三种情况，确保佣金计算完全符合规则

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