基于数量计算总金额：按阶梯费率计算PO总条数费用的SQL实现

我们需要根据PO表中CNT字段的总条数，按照AMOUNT表定义的阶梯费率规则计算总收费金额，并且该实现需要支持后续在AMOUNT表中新增费率区间的场景。

建表及插入数据的SQL如下：

create table AMOUNT (NUM_START NUMBER(15), NUM_END NUMBER(15), AMOUNT NUMBER(15,2));
INSERT INTO AMOUNT VALUES (1,25000,0.15);
INSERT INTO AMOUNT VALUES (25001,50000,0.10);
INSERT INTO AMOUNT VALUES (50001,100000,0.05);

CREATE TABLE PO (ID NUMBER(10), PO_NUM NUMBER (10), CNT NUMBER(10));
INSERT INTO PO VALUES (10,111,100);
INSERT INTO PO VALUES (10,222,500);
INSERT INTO PO VALUES (10,333,25000);
INSERT INTO PO VALUES (20,111,100);
INSERT INTO PO VALUES (20,222,200);

实现思路 #

1. 先计算PO表中CNT字段的总条数；
2. 将总条数与AMOUNT表的阶梯区间关联，动态计算每个区间内实际需要计费的条数：
   • 对于每个费率区间，取总条数和区间上限NUM_END的较小值，减去区间下限的前一个值（NUM_START - 1）；
   • 如果计算结果为正，说明该区间有需要计费的条数，否则该区间不计费；
3. 将每个区间的计费条数乘以对应费率，最后求和得到总收费金额。

SQL实现 #

WITH total_cnt AS (
    SELECT SUM(CNT) AS total FROM PO
)
SELECT SUM(
    GREATEST(
        LEAST(t.total, a.NUM_END) - (a.NUM_START - 1),
        0
    ) * a.AMOUNT
) AS total_amount
FROM AMOUNT a, total_cnt t
WHERE a.NUM_START <= t.total;

逻辑说明 #

• total_cnt CTE：计算PO表所有CNT的总和，这里结果为25900；
• LEAST(t.total, a.NUM_END)：确定当前区间最多计费到哪一条（总条数或区间上限，取较小值）；
• LEAST(...) - (a.NUM_START - 1)：得到当前区间实际计费的条数；
• GREATEST(..., 0)：避免出现负数（当总条数小于区间下限的时候，结果为0，不计费）；
• 最后将每个区间的计费金额求和，得到总收费。

扩展性说明 #

当AMOUNT表新增费率区间（比如新增100001, 200000, 0.03），该SQL无需修改，会自动适配新的阶梯规则，因为逻辑是基于AMOUNT表的所有区间动态计算的。

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