分析函数之计费规则计算

/*
结算规则配置方案
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.4*100+100*0.5+100*0.6=150 这个就是全阶梯 （并非0.4*50+100*0.5+100*0.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
                         

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28194062/viewspace-1677160/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28194062/viewspace-1677160/