如何用SQL实现基于slab阶梯规则的用户收益分段计算

核心逻辑为对每个阶梯计算有效覆盖用户数，自动过滤无用户覆盖的空阶梯，支持无上限的最后一档阶梯计算。

前置假设 #

你的阶梯费率表名为slab_rates，结构和你给出的一致：包含slabs、min_bucket、max_bucket、rate_per_month字段。

通用实现逻辑 #

每个阶梯的有效用户数计算规则：

1. 取「总用户数」和「当前阶梯上限」的较小值，减去「当前阶梯下限」
2. 若计算结果小于0，说明总用户数未进入当前阶梯，有效用户数记为0
3. 最后一级无上限的阶梯，将空的max_bucket替换为远大于业务可能最大值的数值处理
4. 阶梯收益 = 有效用户数 * 对应阶梯费率
5. 过滤有效用户数为0的阶梯，按阶梯下限升序排列

MySQL版本实现 #

-- 定义总用户数变量，替换为实际值即可
SET @total_users = 1000001;

SELECT
  min_bucket,
  max_bucket,
  rate_per_month,
  GREATEST(
    0,
    LEAST(
      @total_users,
      COALESCE(max_bucket, 9999999999)
    ) - min_bucket
  ) AS `Count`,
  rate_per_month,
  GREATEST(
    0,
    LEAST(
      @total_users,
      COALESCE(max_bucket, 9999999999)
    ) - min_bucket
  ) * rate_per_month AS revenue
FROM slab_rates
HAVING `Count` > 0
ORDER BY min_bucket ASC;

PostgreSQL版本实现 #

WITH params AS (
  -- 定义总用户数，替换为实际值即可
  SELECT 1000001 AS total_users
)
SELECT
  s.min_bucket,
  s.max_bucket,
  s.rate_per_month,
  GREATEST(
    0,
    LEAST(
      p.total_users,
      COALESCE(s.max_bucket, 9999999999)
    ) - s.min_bucket
  ) AS "Count",
  s.rate_per_month,
  GREATEST(
    0,
    LEAST(
      p.total_users,
      COALESCE(s.max_bucket, 9999999999)
    ) - s.min_bucket
  ) * s.rate_per_month AS revenue
FROM slab_rates s, params p
WHERE GREATEST(
  0,
  LEAST(
    p.total_users,
    COALESCE(s.max_bucket, 9999999999)
  ) - s.min_bucket
) > 0
ORDER BY s.min_bucket ASC;

覆盖场景说明 #

• 支持总用户数落在任意阶梯的计算，比如45万用户会返回2条阶梯数据，计算结果符合300000*20 + 150000*17的规则
• 自动处理最后一级无上限阶梯的计算
• 所有返回行的Count总和和输入的总用户数完全一致
• 如果需要直接获取总收益，在外层嵌套求和即可：

SELECT SUM(revenue) AS total_revenue FROM (/* 上面的完整查询语句 */) t;

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