BigQuery多ID场景下两表关联阶梯计算usage_amount需求

现有两张业务表：

Table 1（用量主表） #

Table 2（阶梯费率表） #

需求：按usage_cost的阶梯规则，将Table1中的usage分段乘以对应Amount计算总费用usage_amount，且需自动适配大量不同id的情况，不能硬编码阶梯规则。

实现思路 #

• 先为Table2的每条费率数据计算阶梯上限：同一id下，当前阶梯的上限为下一个阶梯的usage_cost；最后一个阶梯的上限设为极大值，确保覆盖所有可能的usage值。
• 关联Table1与处理后的费率表，计算每个阶梯内的实际用量：取当前阶梯上限与Table1usage的较小值，减去当前阶梯的usage_cost，得到该段有效用量（避免负数）。
• 用有效用量乘以对应Amount，再按id分组求和得到总费用。

SQL代码实现（以MySQL为例） #

WITH ranked_rates AS (
    SELECT
        id,
        name,
        usage_cost,
        Amount,
        -- 获取下一个阶梯的阈值作为当前阶梯上限，最后一个阶梯用极大值兜底
        LEAD(usage_cost, 1, 999999999) OVER (PARTITION BY id ORDER BY usage_cost) AS next_cost
    FROM Table2
)
SELECT
    t1.id,
    t1.name,
    t1.month,
    t1.usage,
    SUM(
        -- 计算当前阶梯实际用量，确保数值非负
        GREATEST(0, LEAST(rr.next_cost, t1.usage) - rr.usage_cost) * rr.Amount
    ) AS usage_amount
FROM Table1 t1
JOIN ranked_rates rr ON t1.id = rr.id AND t1.name = rr.name
GROUP BY t1.id, t1.name, t1.month, t1.usage;

代码说明 #

• ranked_rates CTE：通过LEAD()窗口函数为每个id的阶梯费率生成上限值，解决了硬编码阶梯区间的问题。
• 用量计算逻辑：LEAST(rr.next_cost, t1.usage)避免计算超出实际总用量的部分，GREATEST(0, ...)防止出现负数用量（比如总用量小于当前阶梯阈值时）。
• 分组求和：按id、name等维度聚合，得到每个主体的总费用。

结果验证 #

对于示例中id=111、usage=5000的情况：

• 0-1000段：(1000-0)*0 = 0
• 1000-3000段：(3000-1000)*20 = 40000
• 3000-4000段：(4000-3000)*30 = 30000
• 4000-5000段：(5000-4000)*40 = 40000
  总和为0+40000+30000+40000=110000，与需求一致。

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