希望帮忙精简下程序

[benpao0816]

drop table if exists rf1;
create table rf1
(
id varchar(20),
customer int(10),
date datetime(0),
#money NUMERIC(8),
money decimal(20,2),
type varchar(20)
);
#MYSQL_QUERY('SET NAMES GBK');
load data infile 'G:\\mysql\\data\\rfm.txt' into table rf1;
drop table if exists rf2;
CREATE TABLE rf2
(
customer VARCHAR(200),
R int(10),
F int(10),
M DECIMAL(20,2)
);

#插入
INSERT INTO rf2(customer,R,F,M)
select d.customer,R,(sum1-sum2+sum3)F,M
from
(select customer,datediff('2010-9-27 19:30:36',max(date)) as R from rf1 where date<="2010-09-30 19:30:36" and money>0 group by customer)a,
(select customer,count(1) as sum1 from rf1 c where money>0 group by customer)b,
(select customer,count(1) as sum2 from rf1 c where money<=0 group by customer)c,
(select customer,count(1) as sum3 from rf1 c where money=0 group by customer)e,
(select customer,sum(money) as M from rf1 group by customer)d
where a.customer=b.customer and a.customer=c.customer and a.customer=e.customer and a.customer=d.customer;

drop table if exists rf3;
CREATE TABLE rf3
(
customer VARCHAR(200),
R int(10),
F int(10),
M DECIMAL(20,2),
r1 DECIMAL(20,2),
f1 DECIMAL(20,2),
m1 DECIMAL(20,2)

);

#插入
INSERT INTO rf3(customer,R,F,M,r1,f1,m1)
select a.customer,r,f,m,a.r1,b.f1,c.m1
from
(select * from rf2)d,
(select customer,
(case
when r<=63.5 then 5
when r>63.5 and r<=129 then 4
when r>129 and r<=192.5 then 3
when r>192.5 and r<=256 then 2
when r>256 then 1
end)r1 from rf2)a,
(select customer,
(case
when f<=5.25 then 1
when f>5.25 and f<=13.5 then 2
when f>13.5 and f<=18.75 then 3
when f>18.75 and f<=24 then 4
when f>24 then 5
end)f1 from rf2)b,
(select customer,
(
case
when m<=1302 then 1
when m>1302 and m<=3071 then 2
when m>3071 and m<=4373 then 3
when m>4373 and m<=5675 then 4
when m>5675 then 5
end)m1 from rf2)c
where a.customer=b.customer and a.customer=c.customer and a.customer=d.customer;

drop table if exists rf4;
CREATE TABLE rf4
(
customer VARCHAR(200),
R int(10),
F int(10),
M DECIMAL(20,2),
r1 DECIMAL(20,2),
f1 DECIMAL(20,2),
m1 DECIMAL(20,2),
r1_ave DECIMAL(20,2),
f1_ave dECIMAL(20,2),
m1_ave dECIMAL(20,2)
);

#插入
INSERT INTO rf4(customer,R,F,M,r1,f1,m1,r1_ave,f1_ave,m1_ave)
select f.customer,r,f,m,r1,f1,m1,(a.a1+b.b1+c.c1+d.d1+e.e1)/1200 r1_ave,(f2.f2+g.g1+h.h1+i.i1+j.j1)/1200 f1_ave,(k.k1+l.l1+m2.m2+n.n1+o.o1)/1200 m1_ave
from
(select * from rf3)f,
(select customer,sum(r1) as a1 from rfm5 where r1=5)a,
(select customer,sum(r1) as b1 from rfm5 where r1=4)b,
(select customer,sum(r1) as c1 from rfm5 where r1=3)c,
(select customer,sum(r1) as d1 from rfm5 where r1=2)d,
(select customer,sum(r1) as e1 from rfm5 where r1=1)e,
(select customer,sum(f1) as f2 from rfm5 where f1=5)f2,
(select customer,sum(f1) as g1 from rfm5 where f1=4)g,
(select customer,sum(f1) as h1 from rfm5 where f1=3)h,
(select customer,sum(f1) as i1 from rfm5 where f1=2)i,
(select customer,sum(f1) as j1 from rfm5 where f1=1)j,
(select customer,sum(m1) as k1 from rfm5 where m1=5)k,
(select customer,sum(m1) as l1 from rfm5 where m1=4)l,
(select customer,sum(m1) as m2 from rfm5 where m1=3)m2,
(select customer,sum(m1) as n1 from rfm5 where m1=2)n,
(select customer,sum(m1) as o1 from rfm5 where m1=1)o;

drop table if exists rf5;
CREATE TABLE rf5
(
customer VARCHAR(200),
R int(10),
F int(10),
M DECIMAL(20,2),
r1 DECIMAL(20,2),
f1 DECIMAL(20,2),
m1 DECIMAL(20,2),
r1_ave DECIMAL(20,2),
f1_ave dECIMAL(20,2),
m1_ave dECIMAL(20,2),
type varchar(100)
);
INSERT INTO rf5(customer,R,F,M,r1,f1,m1,r1_ave,f1_ave,m1_ave,type)
select customer,r,f,m,r1,f1,m1,r1_ave,f1_ave,m1_ave,
(case
when r1>=r1_ave and f1>=f1_ave and m1>=m1_ave then '重要价值客户'
when r1<r1_ave and f1>=f1_ave and m1>=m1_ave then '重要唤回客户'
when r1>=r1_ave and f1<f1_ave and m1>=m1_ave then '重要深耕客户'
when r1<r1_ave and f1<f1_ave and m1>=m1_ave then '重要挽留客户'
when r1>=r1_ave and f1>=f1_ave and m1<m1_ave then '潜力客户'
when r1>=r1_ave and f1<f1_ave and m1<m1_ave then '新客户'
when r1<=r1_ave and f1>=f1_ave and m1<=m1_ave then '一般维持客户'
when r1<=r1_ave and f1<=f1_ave and m1<=m1_ave then '流失客户'
end)ty from rf4

自个写的一个SQL分类语句，能够实现结果，但感觉太罗嗦，写的内容有点多，求大神帮忙优化下，如直接更新于原表而不是重新建标等。下面是测试数据
id customer date money type
00023351 010006 2009-09-27 20:10:22 58.00 特价
00023372 030031 2009-09-27 21:33:35 69.00 特价
00023447 040102 2009-09-28 21:12:34 69.00 特价
00023448 020173 2009-09-28 21:12:34 69.00 特价
00023449 040017 2009-09-28 21:13:48 69.00 特价
00023464 030132 2009-09-28 21:37:42 81.00 正常
00023465 020145 2009-09-28 21:40:53 60.00 正常
00023466 030087 2009-09-28 21:45:17 72.00 正常
00023467 020102 2009-09-28 21:46:54 51.00 正常
00023469 040051 2009-09-28 21:51:02 60.00 正常
00023472 010299 2009-09-28 22:00:09 60.00 正常
00023496 010128 2009-09-29 19:11:19 72.00 特价
00023499 040239 2009-09-29 19:16:34 51.00 特价
00023500 030108 2009-09-29 19:16:34 60.00 特价
00023501 030089 2009-09-29 19:16:34 72.00 特价
00023502 030068 2009-09-29 19:16:34 81.00 特价
00023503 020013 2009-09-29 19:16:34 60.00 特价
00023504 010247 2009-09-29 19:16:34 162.00 特价
00023505 020255 2009-09-29 19:16:34 51.00 特价
00023506 020132 2009-09-29 19:16:34 60.00 特价
00023507 010229 2009-09-29 19:16:34 81.00 特价
00023518 020179 2009-09-29 20:03:48 81.00 特价
00023521 020183 2009-09-29 20:42:04 81.00 特价
00023526 010149 2009-09-29 20:58:05 77.00 特价
00023607 010078 2009-09-30 19:46:42 81.00 特价
00023608 020051 2009-09-30 19:46:42 77.00 特价
00023609 010158 2009-09-30 19:46:42 60.00 特价
00023610 040107 2009-09-30 19:46:42 81.00 特价
00023611 010044 2009-09-30 19:46:42 180.00 特价
00023612 030182 2009-09-30 19:46:42 120.00 特价
00023613 030068 2009-09-30 19:46:42 51.00 特价
00023614 040020 2009-09-30 19:46:42 72.00 特价
00023615 030073 2009-09-30 19:46:42 81.00 特价
00023616 020029 2009-09-30 19:46:42 60.00 特价
00023617 030052 2009-09-30 19:46:42 72.00 特价
00023618 020240 2009-09-30 19:46:42 60.00 特价
00023619 010170 2009-09-30 19:46:42 81.00 特价
00023620 020076 2009-09-30 19:46:42 81.00 特价
00023621 010233 2009-09-30 19:46:42 72.00 特价
00023622 040194 2009-09-30 19:47:04 60.00 特价
00023627 020089 2009-09-30 19:55:43 81.00 特价
00023630 030062 2009-09-30 20:09:00 77.00 特价
00023631 020098 2009-09-30 20:15:02 81.00 特价
00023632 020092 2009-09-30 20:20:11 81.00 特价
00023637 010099 2009-09-30 21:02:59 81.00 特价

想实现的过程如下:这是一个电商的订单数据，（R)查询每个id的最近一次订单money>0的日期距离2010-9-27对应的最小天数
(F)每个id money>0的下单次数-每个id money<0的下单次数 （M)每个订单对应的成交金额 然后根据R、F、M分别取最大值减最小值中间分为4等份，R越小取5、4、3、2、1，F和M越大取5、4、3、2、1。算出取5、4、3、2、1对应的个数然后进行加权平均，得出r1_ave,f1_ave,m1_ave。然后根据r1，f1，m1跟加权数进行比较分类，得出8个分类。自个写的sql能够用，希望能够帮忙精简下

[中国风]

用CASE WHEN 或IF

[benpao0816]

引用 1 楼 roy_88 的回复:

用CASE WHEN 或IF

是用的case when呀

[中国风]

比如:以你的第一段insert为例 e.g.

SELECT a.customer
     , DATEDIFF('2010-9-27 19:30:36'
              , MAX(CASE WHEN a.date<="2010-09-30 19:30:36"
                              AND a.money>0 THEN date
                    END)) AS R
     , COUNT(*) AS F
     , SUM(a.money) AS M
FROM rf1 AS a
WHERE EXISTS(SELECT 1 FROM rf1 WHERE customer=a.customer AND date<="2010-09-30 19:30:36" and money>0)
GROUP BY a.customer