只是大概思路,没有环境执行,不知道有没有语法错误:merge into cust_info using
(select t1.cust_id,
case when t1.cust_tot/t2.tot_money<=0.2 then '核心客户'
when t1.cust_tot/t2.tot_money>0.2 and t1.cust_tot/t2.tot_money<=0.8 then '战略客户'
when t1.cust_tot/t2.tot_money>0.8 then '普通客户'
end cals
from
(select cust_id,sum(money) cust_tot where mounth=pi_dt group by cust_id) t1,
(select sum(money) tot_money from cust_dep_info where mounth=pi_dt) t2) t3
on (cust_info.cust_id=t3.id)
when matched then
update set cust_info.cust_type=t3.cals
(select t1.cust_id,
case when t1.cust_tot/t2.tot_money<=0.2 then '核心客户'
when t1.cust_tot/t2.tot_money>0.2 and t1.cust_tot/t2.tot_money<=0.8 then '战略客户'
when t1.cust_tot/t2.tot_money>0.8 then '普通客户'
end cals
from
(select cust_id,sum(money) cust_tot where mounth=pi_dt group by cust_id) t1,
(select sum(money) tot_money from cust_dep_info where mounth=pi_dt) t2) t3
on (cust_info.cust_id=t3.id)
when matched then
update set cust_info.cust_type=t3.cals
--建表和插入数据
create table cust_dep_info
(account_id number,
cust_id varchar2(20),
money number,
month date);
create table cust_info (
cust_id varchar2(20) primary key,
cust_name varchar2(50),
cust_type varchar2(20)
);
insert into cust_info select level, 'Name' || to_char(level), null
from dual connect by level <= 100;
commit;insert into cust_dep_info
select level, level, round(DBMS_RANDOM.VALUE(1,1000)), trunc(sysdate) - mod(level, 7)
from dual
connect by level <= 100;
commit;--Merge,假设给定日期就是今天。
merge into cust_info
using (
with all_cust as (
select count(*) cust_count from cust_dep_info
where month=trunc(sysdate)
),
ranking (core, ordinary) as (
select trunc(cust_count*0.2), trunc(cust_count * 0.8)
from all_cust
)
select t2.*,
case
when t2.rn <= ranking.core then '核心客户'
when (t2.rn >ranking.core and t2.rn <= ranking.ordinary) then '战略客户'
when t2.rn > ranking.ordinary then '普通客户'
end lev
from
(
select t1.*, rownum rn from (
select cust_id, money,round(ratio_to_report(sum(money)) over(),3) ratio
from cust_dep_info
where month=trunc(sysdate)
group by cust_id, money
order by ratio desc
) t1
) t2, ranking
) src
on (cust_info.cust_id = src.cust_id)
when matched then
update set cust_type=src.lev;
commit;
select * from cust_info;
我的理解是:
先按比例从大到小排序,排名在前20%是核心客户,20%(不包含)-80%(包含)是战略客户,依此类推。
请楼主给分。