添加临时表改良程序

[moshangdanqing]

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 rf2a)d,
(select customer,
(case
when r<=min_r+(max_r-min_r)/5 then 5
when r>min_r+(max_r-min_r)/5 and r<=min_r+(max_r-min_r)/5*2 then 4
when r>=min_r+(max_r-min_r)/5*2 and r<=min_r+(max_r-min_r)/5*3 then 3
when r>=min_r+(max_r-min_r)/5*3 and r<=min_r+(max_r-min_r)/5*4 then 2
when r>min_r+(max_r-min_r)/5*4 then 1
end)r1 from rf2a)a,
(select customer,
(case
when f<=min_f+(max_f-min_f)/5 then 1
when f>min_f+(max_f-min_f)/5 and f<=min_f+(max_f-min_f)/5*2 then 2
when f>=min_f+(max_f-min_f)/5*2 and f<=min_f+(max_f-min_f)/5*3 then 3
when f>=min_f+(max_f-min_f)/5*3 and f<=min_f+(max_f-min_f)/5*4 then 4
when f>min_f+(max_f-min_f)/5*4 then 5
end)f1 from rf2a)b,
(select customer,
(
case
when m<=min_m+(max_m-min_m)/5 then 1
when m>min_m+(max_m-min_m)/5 and m<=min_m+(max_m-min_m)/5*2 then 2
when m>=min_m+(max_m-min_m)/5*2 and m<=min_m+(max_m-min_m)/5*3 then 3
when m>=min_m+(max_m-min_m)/5*3 and m<=min_m+(max_m-min_m)/5*4 then 4
when m>min_m+(max_m-min_m)/5*4 then 5
end)m1 from rf2a)c
where a.customer=b.customer and a.customer=c.customer and a.customer=d.customer;

这个是做的一个模型，这个程序在量小的情况下是可以运行的。但数据量较大情况下，跑不动，看网上有人说通过建立临时表来改良程序，之前没接触过临时表，求各位大神帮忙改良下，应该是把a,b,c,d,e这些表改为临时表，谢谢!

[shoppo0505]

都是数据运算，会跑不动？ 估计是你a，b，c 3个表full join的关系。 根据你的需求，你可以使用inner join，那处理数据量就会少很多。

[吉普赛的歌]

CREATE TEMPORARY TABLE a
select customer,
(case   
  when r<=min_r+(max_r-min_r)/5  then 5
  when r>min_r+(max_r-min_r)/5 and r<=min_r+(max_r-min_r)/5*2 then 4  
  when r>=min_r+(max_r-min_r)/5*2 and r<=min_r+(max_r-min_r)/5*3 then 3  
  when r>=min_r+(max_r-min_r)/5*3 and r<=min_r+(max_r-min_r)/5*4 then 2  
  when r>min_r+(max_r-min_r)/5*4 then 1   
end)r1 from  rf2a

举个例，其它的类似。 弄完连接临时表。

[moshangdanqing]

引用 2 楼 yenange 的回复:

CREATE TEMPORARY TABLE a
select customer,
(case   
  when r<=min_r+(max_r-min_r)/5  then 5
  when r>min_r+(max_r-min_r)/5 and r<=min_r+(max_r-min_r)/5*2 then 4  
  when r>=min_r+(max_r-min_r)/5*2 and r<=min_r+(max_r-min_r)/5*3 then 3  
  when r>=min_r+(max_r-min_r)/5*3 and r<=min_r+(max_r-min_r)/5*4 then 2  
  when r>min_r+(max_r-min_r)/5*4 then 1   
end)r1 from  rf2a

举个例，其它的类似。 弄完连接临时表。

可不可以帮忙全写下，新手不会弄。连接也是这样连吗where a.customer=b.customer and a.customer=c.customer and a.customer=d.customer，另外有可能是表被锁死了，这种问题怎么解决呢

[benpao002]

应该是索引问题，也许不是这条语句造成慢的原因