/*
结算规则配置方案
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
结算规则配置方案
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/