oracle 基于数量的金额总和

ttisahbt 于 2023-03-17 发布在 Oracle

关注(0)|答案(2)|浏览(230)

我想有两个表，并根据总计数需要收费的金额。

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);

现在，我想计算总行数，并对前25000行收取0.15美分，然后对剩余行收取0.10美分。以后，我将在AMOUNT表中添加更多的数量和金额。
那么总的线路是25,900条，我想对25,000条线路收取0. 15美分，900条线路收取0. 10美分，我该怎么做呢？

oracle

来源：https://stackoverflow.com/questions/75680126/sum-of-amount-based-on-quantity

2条答案

按热度按时间

ogsagwnx1#

您可以使用聚合、case表达式和LAG（）分析函数的组合，如下所示：

SELECT  ID, CNT, Sum(CNT_AMOUNT * AMOUNT) "TOTAL"
FROM(   Select  p.ID "ID", p.CNT "CNT",
                CASE  WHEN p.CNT > a.NUM_END 
                      THEN a.NUM_END 
                ELSE p.CNT 
                END - Nvl( LAG(CASE   WHEN p.CNT > a.NUM_END 
                                      THEN a.NUM_END 
                                ELSE p.CNT 
                                END) OVER(Partition By p.ID Order By p.id, a.NUM_START), 0 ) "CNT_AMOUNT",
                a.AMOUNT "AMOUNT"
        FROM    ( Select p.ID "ID", Sum(p.CNT) "CNT"
                  From po p
                  Group By p.ID
                ) p
        Left Join amounts a ON(1 = 1)
    )
Group By ID, CNT
Order By ID, CNT

用你的样本数据（加ID 30，用于测试多组价格）

WITH 
    amounts (NUM_START, NUM_END, AMOUNT) AS
        (
            Select 1,     25000,    0.15 From Dual Union All
            Select 25001, 50000,    0.10 From Dual Union All
            Select 50001, 100000,   0.05 From Dual
        ), 

    po (ID, PO_NUM, CNT) AS
        (
            Select 10,  111,    100 From Dual Union All
            Select 10,  222,    500 From Dual Union All
            Select 10,  333,  25000 From Dual Union All
            Select 20,  111,    100 From Dual Union All
            Select 20,  222,    200 From Dual Union All
            Select 30,  111,  53000 From Dual
        )

结果是
| 识别号|碳纳米管|共计|
| - ------|- ------|- ------|
| 十个|小行星25600|小行星381|
| 二十个|三百|四十五|
| 三十|小行星53|小行星6400|
如果您想要所有的总计（不按ID分组），则：

SELECT  CNT, Sum(CNT_AMOUNT * AMOUNT) "TOTAL"
FROM    
    (   Select  p.CNT "CNT",
                CASE  WHEN p.CNT > a.NUM_END 
                      THEN a.NUM_END 
                ELSE p.CNT 
                END - Nvl( LAG(CASE   WHEN p.CNT > a.NUM_END 
                                      THEN a.NUM_END 
                                ELSE p.CNT 
                                END) OVER(Order By  a.NUM_START), 0 ) "CNT_AMOUNT", 
                a.AMOUNT "AMOUNT"
        FROM    ( Select Sum(p.CNT) "CNT"
                  From po p
                ) p
        Left Join amounts a ON(1 = 1)
    )
Group By CNT

结果
| 碳纳米管|共计|
| - ------|- ------|
| 小行星78900|小行星7695|

赞(0）回复(0）举报 2023-03-17

q5iwbnjs2#

您可以使用解析SUM函数来查找每个范围的开始和结束，然后将JOINMap到amount表，以获取范围中每个部分的成本（在超出金额边界的位置拆分范围）：

SELECT p.id,
       p.po_num,
       p.cnt,
       GREATEST(p.cnt_start, a.num_start) AS cnt_start,
       LEAST(p.cnt_end, a.num_end) AS cnt_start,
       a.amount,
       a.amount * (LEAST(p.cnt_end, a.num_end) - GREATEST(p.cnt_start, a.num_start) + 1) AS total_cost
FROM   (
         SELECT id,
                po_num,
                cnt,
                SUM(cnt) OVER (PARTITION BY id ORDER BY po_num) - cnt + 1 AS cnt_start,
                SUM(cnt) OVER (PARTITION BY id ORDER BY po_num) AS cnt_end
         FROM   po
       ) p
       LEFT OUTER JOIN amount a
       ON a.num_start <= p.cnt_end AND a.num_end >= p.cnt_start

对于样本数据，它输出：
| 识别号|采购订单编号|碳纳米管|计数器_开始|计数器_开始|金额|总计_成本|
| - ------|- ------|- ------|- ------|- ------|- ------|- ------|
| 十个|三百三十三|二万五千|小行星25001|小行星25600|第一款|六十|
| 十个|三百三十三|二万五千|六零一|二万五千|点十五|小行星3660|
| 十个|二百二十二|五百|一百零一|六百|点十五|七十五|
| 二十个|二百二十二|二百|一百零一|三百|点十五|三十|
| 二十个|一百一十一|一百|1个|一百|点十五|十五|
| 十个|一百一十一|一百|1个|一百|点十五|十五|
如果要汇总以获得总成本：

SELECT p.id,
       p.po_num,
       p.cnt,
       SUM(a.amount * (LEAST(p.cnt_end, a.num_end) - GREATEST(p.cnt_start, a.num_start) + 1)) AS total_cost
FROM   (
         SELECT id,
                po_num,
                cnt,
                SUM(cnt) OVER (PARTITION BY id ORDER BY po_num) - cnt + 1 AS cnt_start,
                SUM(cnt) OVER (PARTITION BY id ORDER BY po_num) AS cnt_end
         FROM   po
       ) p
       LEFT OUTER JOIN amount a
       ON a.num_start <= p.cnt_end AND a.num_end >= p.cnt_start
GROUP BY
       p.id,
       p.po_num,
       p.cnt;

其输出：
| 识别号|采购订单编号|碳纳米管|总计_成本|
| - ------|- ------|- ------|- ------|
| 二十个|一百一十一|一百|十五|
| 十个|一百一十一|一百|十五|
| 十个|三百三十三|二万五千|小行星3720|
| 十个|二百二十二|五百|七十五|
| 二十个|二百二十二|二百|三十|
如果不想使用PARTITION BY id，则可以将id移动到聚合函数的ORDER BY子句中：

SELECT p.id,
       p.po_num,
       p.cnt,
       GREATEST(p.cnt_start, a.num_start) AS cnt_start,
       LEAST(p.cnt_end, a.num_end) AS cnt_start,
       a.amount,
       a.amount * (LEAST(p.cnt_end, a.num_end) - GREATEST(p.cnt_start, a.num_start) + 1) AS total_cost
FROM   (
         SELECT id,
                po_num,
                cnt,
                SUM(cnt) OVER (ORDER BY po_num, id) - cnt + 1 AS cnt_start,
                SUM(cnt) OVER (ORDER BY po_num, id) AS cnt_end
         FROM   po
       ) p
       LEFT OUTER JOIN amount a
       ON a.num_start <= p.cnt_end AND a.num_end >= p.cnt_start

其输出：
| 识别号|采购订单编号|碳纳米管|计数器_开始|计数器_开始|金额|总计_成本|
| - ------|- ------|- ------|- ------|- ------|- ------|- ------|
| 十个|三百三十三|二万五千|小行星25001|小行星25900|第一款|九十|
| 十个|三百三十三|二万五千|九零一|二万五千|点十五|小行星3615|
| 二十个|二百二十二|二百|七零一|九百|点十五|三十|
| 十个|二百二十二|五百|二百零一|七百|点十五|七十五|
| 二十个|一百一十一|一百|一百零一|二百|点十五|十五|
| 十个|一百一十一|一百|1个|一百|点十五|十五|
或者仅使用总数：

SELECT p.id,
       p.po_num,
       p.cnt,
       SUM(a.amount * (LEAST(p.cnt_end, a.num_end) - GREATEST(p.cnt_start, a.num_start) + 1)) AS total_cost
FROM   (
         SELECT id,
                po_num,
                cnt,
                SUM(cnt) OVER (ORDER BY po_num, id) - cnt + 1 AS cnt_start,
                SUM(cnt) OVER (ORDER BY po_num, id) AS cnt_end
         FROM   po
       ) p
       LEFT OUTER JOIN amount a
       ON a.num_start <= p.cnt_end AND a.num_end >= p.cnt_start
GROUP BY
       p.id,
       p.po_num,
       p.cnt;

其输出：
| 识别号|采购订单编号|碳纳米管|总计_成本|
| - ------|- ------|- ------|- ------|
| 二十个|一百一十一|一百|十五|
| 十个|一百一十一|一百|十五|
| 十个|三百三十三|二万五千|小行星3705|
| 二十个|二百二十二|二百|三十|
| 十个|二百二十二|五百|七十五|
fiddle

赞(0）回复(0）举报 2023-03-17

我来回答

oracle 基于数量的金额总和

2条答案

相关问题

热门标签

最新问答