分析函数运用样例--计费规则计算

最新推荐文章于 2025-04-14 16:51:17 发布

原创最新推荐文章于 2025-04-14 16:51:17 发布 · 550 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#分析函数

oracle 专栏收录该内容

18 篇文章

订阅专栏

本文介绍了一种基于阶梯和非阶梯模式的结算规则配置方案，通过IF_jieti和price_type字段定义不同类型的结算算法，包括百分比和固定值，并提供测试脚本示例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

本文在梁敬彬讲解的基础上，考虑新写了一个方法，见方法二

结算规则配置方案
1、用IF_jieti字段来区分是否是阶梯，0为非阶梯，1为半阶梯，2为全阶梯
2、用price_type字段来区分算法是百分比还是固定值（无论全阶梯、半阶梯还是非阶梯算法，最终都
是离不开百分比和固定值选择）
3、 IF_jieti字段和price_type字段的组合可以定义出全部的配置类型
名词解释
全阶梯定义：就是比如0-50不分成，给0元。50-100，4：6开，101-200，5：5开，201以上6：4开。
这个时候有300元，是0.4100+1000.5+1000.6=150 这个就是全阶梯（并非0.450+1000.5+1000.6）
如果这个时候是20元呢，那SP的钱就是0，没得分！
半阶梯是300元总金额不要再去截断了，直接乘落在哪段的比例：300*0.6=180
以下脚本建立配置表中暂且配置了4个SP，分别为a,b,c,d，分别对应一下四个类型
测试脚本一股脑儿扔到COMMAND下执行，然后看结果就好了！

    drop table test_rule purge;
    create table TEST_RULE
    ( 
      IF_jieti  number,
      SP_NAME    VARCHAR2(10),
      PRICE_TYPE NUMBER,
      SP_PARAM   NUMBER,
      TEL_PARAM  NUMBER,
      TOTAL1     NUMBER,
      TOTAL2     NUMBER
    );
    -- Add comments to the columns 
    comment on column TEST_RULE.IF_JIETI
      is '用来判断是否阶梯，0为非阶梯，1为半阶梯（不需分段算），2为全阶梯（需分段算）';
    comment on column TEST_RULE.SP_NAME
      is 'sp名';
    comment on column TEST_RULE.PRICE_TYPE
      is '分成类型（1表示比率，2为固定金额）';
    comment on column TEST_RULE.SP_PARAM
      is 'sp比率或金额';
    comment on column TEST_RULE.TEL_PARAM
      is '电信比率或金额';
    comment on column TEST_RULE.TOTAL1
      is '金额范围开始';
    comment on column TEST_RULE.TOTAL2
      is '金额范围开始';

  

    insert into TEST_RULE (if_jieti,sP_NAME, PRICE_TYPE, SP_PARAM, TEL_PARAM, TOTAL1, TOTAL2) values (0,'a', 1, 40, 60, null, null);
    insert into TEST_RULE (if_jieti,sP_NAME, PRICE_TYPE, SP_PARAM, TEL_PARAM, TOTAL1, TOTAL2) values (0,'b', 2, 800, null, null, null);
    insert into TEST_RULE (if_jieti,sP_NAME, PRICE_TYPE, SP_PARAM, TEL_PARAM, TOTAL1, TOTAL2) values (1,'c', 1, 30, 70, 0, 500);
    insert into TEST_RULE (if_jieti,sP_NAME, PRICE_TYPE, SP_PARAM, TEL_PARAM, TOTAL1, TOTAL2) values (1,'c', 1, 40, 60, 501, 1000);
    insert into TEST_RULE (if_jieti,sP_NAME, PRICE_TYPE, SP_PARAM, TEL_PARAM, TOTAL1, TOTAL2) values (1,'c', 1, 50, 50, 1001, 2000);
    insert into TEST_RULE (if_jieti,sP_NAME, PRICE_TYPE, SP_PARAM, TEL_PARAM, TOTAL1, TOTAL2) values (1,'c', 1, 60, 40, 2001, 9999999);
    insert into TEST_RULE (if_jieti,sP_NAME, PRICE_TYPE, SP_PARAM, TEL_PARAM, TOTAL1, TOTAL2) values (2,'d', 2, 0, null, 0, 500);      
    insert into TEST_RULE (if_jieti,sP_NAME, PRICE_TYPE, SP_PARAM, TEL_PARAM, TOTAL1, TOTAL2) values (2,'d', 1, 40, 60, 501, 1000);    
    insert into TEST_RULE (if_jieti,sP_NAME, PRICE_TYPE, SP_PARAM, TEL_PARAM, TOTAL1, TOTAL2) values (2,'d', 1, 50, 50, 1001, 2000);   
    insert into TEST_RULE (if_jieti,sP_NAME, PRICE_TYPE, SP_PARAM, TEL_PARAM, TOTAL1, TOTAL2) values (2,'d', 1, 60, 40, 2001, 9999999);    
    commit;

  

    --略去一堆字段
    drop table ticket purge;
    create table TICKET
    (
      ID_NAME     number,
      SP_NAME     VARCHAR2(10),
      TOTAL_PRICE NUMBER
    );
    
    insert into TICKET (ID_NAME,SP_NAME, TOTAL_PRICE) values (121,'a', 100);
    insert into TICKET (ID_NAME,SP_NAME, TOTAL_PRICE) values (222,'b', 1000);
    insert into TICKET (ID_NAME,SP_NAME, TOTAL_PRICE) values (387,'c', 100 );
    insert into TICKET (ID_NAME,SP_NAME, TOTAL_PRICE) values (645,'c', 600 );
    insert into TICKET (ID_NAME,SP_NAME, TOTAL_PRICE) values (555,'c', 1200);
    insert into TICKET (ID_NAME,SP_NAME, TOTAL_PRICE) values (987,'d', 100 );
    insert into TICKET (ID_NAME,SP_NAME, TOTAL_PRICE) values (333,'d', 600 );
    insert into TICKET (ID_NAME,SP_NAME, TOTAL_PRICE) values (221,'d', 1200);
    insert into TICKET (ID_NAME,SP_NAME, TOTAL_PRICE) values (528,'d', 5000);
    commit;

希望


    SP_NAME    TOTAL_PRICE   SP_PRICE TELE_PRICE       PAID
    ---------- ----------- ---------- ---------- ----------
    a                  100                               40
    b                 1000                              800
    c                  100                               30
    c                  600                              240
    c                 1200                              600
    d                  100                                0
    d                  600                              240
    d                 1200                              500
    d                 5000                             2700

分解思考思路：

    SET linesize 2000
    SET pagesize 2000
    
    
    select t.* ,LAG(total1) OVER (PARTITION BY SP_NAME ORDER BY TOTAL1) start_val from test_rule t;

      IF_JIETI SP_NAME    PRICE_TYPE   SP_PARAM  TEL_PARAM     TOTAL1     TOTAL2  START_VAL
    ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
             0 a                   1         40         60
             0 b                   2        800
             1 c                   1         30         70          0        500
             1 c                   1         40         60        501       1000          0
             1 c                   1         50         50       1001       2000        501
             1 c                   1         60         40       2001    9999999       1001
             2 d                   2          0                     0        500
             2 d                   1         40         60        501       1000          0
             2 d                   1         50         50       1001       2000        501
             2 d                   1         60         40       2001    9999999       1001
    
    已选择10行。

    SELECT r.*, DECODE(LAG(total1) OVER (PARTITION BY SP_NAME ORDER BY TOTAL1),0,1,total1) AS start_val
              FROM TEST_RULE r;
              
              
    
      IF_JIETI SP_NAME    PRICE_TYPE   SP_PARAM  TEL_PARAM     TOTAL1     TOTAL2  START_VAL
    ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
             0 a                   1         40         60
             0 b                   2        800
             1 c                   1         30         70          0        500          0
             1 c                   1         40         60        501       1000          1
             1 c                   1         50         50       1001       2000       1001
             1 c                   1         60         40       2001    9999999       2001
             2 d                   2          0                     0        500          0
             2 d                   1         40         60        501       1000          1
             2 d                   1         50         50       1001       2000       1001
             2 d                   1         60         40       2001    9999999       2001
    
    已选择10行。

    SELECT r.* 
          ,SUM((TOTAL2-start_val+1)*sp_param/100) OVER (PARTITION BY SP_NAME ORDER BY TOTAL1) - (TOTAL2-start_val+1)*sp_param/100
           AS ACCUM
      FROM (SELECT r.*, DECODE(LAG(total1) OVER (PARTITION BY SP_NAME ORDER BY TOTAL1),0,1,total1) AS start_val
              FROM TEST_RULE r
           ) r;
           
      IF_JIETI SP_NAME    PRICE_TYPE   SP_PARAM  TEL_PARAM     TOTAL1     TOTAL2  START_VAL      ACCUM
    ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
             0 a                   1         40         60
             0 b                   2        800
             1 c                   1         30         70          0        500          0          0
             1 c                   1         40         60        501       1000          1      150.3
             1 c                   1         50         50       1001       2000       1001      550.3
             1 c                   1         60         40       2001    9999999       2001     1050.3
             2 d                   2          0                     0        500          0          0
             2 d                   1         40         60        501       1000          1          0
             2 d                   1         50         50       1001       2000       1001        400
             2 d                   1         60         40       2001    9999999       2001        900
    
    已选择10行。

方法一

    WITH new_rule AS 
    (SELECT r.* 
          ,SUM((TOTAL2-start_val+1)*sp_param/100) OVER (PARTITION BY SP_NAME ORDER BY TOTAL1) - (TOTAL2-start_val+1)*sp_param/100
           AS ACCUM
      FROM (SELECT r.*, DECODE(LAG(total1) OVER (PARTITION BY SP_NAME ORDER BY TOTAL1),0,1,total1) AS start_val
              FROM TEST_RULE r
           ) r
    )
    SELECT t.*
          ,(CASE WHEN n.if_jieti =2 THEN
                (t.total_price-start_val+1)*n.sp_param/100 + n.accum
            ELSE DECODE(n.price_type,1,round(t.total_price*n.sp_param/100,2)
                      ,n.sp_param
                       )
            END) as paid
      FROM ticket t, new_rule n
    WHERE n.sp_name = t.sp_name
           AND (if_jieti = 0 OR if_jieti>0 AND t.total_price BETWEEN n.TOTAL1 AND n.TOTAL2) ;
           
           
       ID_NAME SP_NAME    TOTAL_PRICE    PAID
    ------- ---------------------------------
        121     a               100         40
        222     b              1000        800
        387     c               100         30
        645     c               600        240
        555     c              1200        600
        987     d               100          0
        333     d               600        240
        221     d              1200        500
        528     d              5000       2700

方法二：

WITH new_rule AS
 (SELECT tmp.*,
         --找出上一阶段实际SP费总数
         SUM(pre_section * pre_sp_param / 100) over(PARTITION BY sp_name ORDER BY total1) AS accum
    FROM (SELECT t.*,
                 --找出上一个阶段的区间数
                 decode(lag(total2, 1, 0)
                        over(PARTITION BY sp_name ORDER BY total2) -
                        lag(decode(total1, 0, 1, total1), 1, 0)
                        over(PARTITION BY sp_name ORDER BY total2) + 1,
                        1,
                        0,
                        lag(total2, 1, 0)
                        over(PARTITION BY sp_name ORDER BY total2) -
                        lag(decode(total1, 0, 1, total1), 1, 0)
                        over(PARTITION BY sp_name ORDER BY total2) + 1) AS pre_section,
                 --找出上一阶段的SP分成比例
                 decode(lag(sp_param, 1, 0)
                        over(PARTITION BY sp_name ORDER BY total1),
                        0,
                        sp_param,
                        lag(sp_param, 1, 999)
                        over(PARTITION BY sp_name ORDER BY total1)) AS pre_sp_param
            FROM test_rule t) tmp)

SELECT t.*,
       (CASE
         WHEN n.if_jieti = 2 THEN
          (t.total_price - decode(total1,0,1,total1) + 1) * n.sp_param / 100 + n.accum
         ELSE
          decode(n.price_type,
                 1,
                 round(t.total_price * n.sp_param / 100, 2),
                 n.sp_param)
       END) AS paid
  FROM ticket t, new_rule n
 WHERE n.sp_name = t.sp_name
   AND (if_jieti = 0 OR
       if_jieti > 0 AND t.total_price BETWEEN n.total1 AND n.total2);