在BigQuery中计算带年度上限的销售佣金

以下是实现需求的SQL方案，核心是通过窗口函数计算累计佣金，再根据年度上限动态调整每月实际支付金额：

假设你的数据表结构包含employee_id（员工ID）、month（月份，需按时间排序）、mtd_commission（当月应得佣金），可以用以下查询：

SELECT
  employee_id,
  month,
  mtd_commission,
  -- 计算当月实际可支付的佣金：取当月应得与剩余额度的较小值
  LEAST(
    mtd_commission,
    GREATEST(
      0,
      25000 - COALESCE(
        SUM(mtd_commission) OVER (
          PARTITION BY employee_id 
          ORDER BY month 
          ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
        ),
        0
      )
    )
  ) AS actual_commission,
  -- 截止当月的累计实际支付佣金（不超过25000）
  LEAST(
    SUM(mtd_commission) OVER (PARTITION BY employee_id ORDER BY month),
    25000
  ) AS total_paid_to_date
FROM your_table_name
ORDER BY employee_id, month;

逻辑说明 #

• SUM(mtd_commission) OVER (...)：按员工分组、月份排序，计算截止到上月的累计应得佣金，用COALESCE处理第一个月（上月累计为0的情况）。
• GREATEST(0, 25000 - 上月累计)：得到当月还能支付的最大额度（如果上月累计已达25000，额度为0）。
• LEAST(当月应得, 最大额度)：最终当月实际支付的佣金，既不会超过当月应得，也不会突破年度上限。

以你提到的每月3000美元的员工为例：

• 第1-8个月：累计佣金未达25000，每月实际支付3000美元，累计到第8个月为24000美元。
• 第9个月：上月累计24000美元，剩余额度1000美元，当月实际支付1000美元，累计达到25000美元。
• 第10-12个月：剩余额度为0，每月实际支付0美元，累计保持25000美元。

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