【无标题】

一、求取用户最后一次购物的远近程度
1.首先从表userds1中选取出来用户的ID和用户最近一次进行购物距离统计最后一天的间隔天数t：
select u.user_id, (date'2014-12-18'-max(u.time)) t from userds1 u where u.behavior_type=4
group by u.user_id order by 2,1;
2.对于用户最后一次消费的间隔天数进行打分，小于2天的打5分；小于5天的4分；小于10天的3分；小于20天的2分；其他的打1分：
with tt as
(select u.user_id, (date'2014-12-18'-max(u.time)) t from userds1 u where u.behavior_type=4
group by u.user_id order by 2,1)
select tt.*,case when t<2 then 5 when t<5 then 4 when t<10 then  3 when t<20 then 2 else 1 end r_score
from tt;
3.求出用户的平均分 3.59
with rr as(
select tt.*, case when t<2 then 5 when t<5 then 4 when t<10 then  3 when t<20 then 2 else 1 end r_score
from (select u.user_id, (date'2014-12-18'-max(u.time)) t from userds1 u
where u.behavior_type=4 group by u.user_id order by 2,1) tt)
select avg(r_score) from rr;
4.求出用户最近一次消费的远近;
with rr as(
select tt.*, case when t<2 then 5 when t<5 then 4 when t<10 then  3
when t<20 then 2 else 1 end r_score
from (select u.user_id, (date'2014-12-18'-max(u.time)) t from userds1 u
where u.behavior_type=4 group by u.user_id order by 2,1) tt)
select rr.*, case when r_score<3.59 then '较远' else '较近' end r from rr;
5.创建一个新表us1，中记录用户ID、评分、r、t：
create table us1 tablespace ts1 as
select rr.*, case when r_score<3.59 then '较远' else '较近' end r 
from (select tt.*, case when t<2 then 5 when t<5 then 4 when t<10 then  3
when t<20 then 2 else 1 end r_score
from (select u.user_id, (date'2014-12-18'-max(u.time)) t from userds1 u
where u.behavior_type=4
group by u.user_id order by 2,1) tt) rr;





二、求用户购物的频率
1.首先从表userds1中选取出来用户的ID和用户购物的次数：
select u.user_id, count(1) c from userds1 u
where u.behavior_type=4
group by u.user_id order by 2 desc;
2.根据用户消费频次给用户打分：
with tt as
(select u.user_id, count(1) c from userds1 u
where u.behavior_type=4
group by u.user_id order by 2 desc)
select tt.*, case when c>300 then 5 when c>100 then 4 when c>50 then 3 when c>10 then 2 else 1 end f_score  
from tt;
3.求取平均值 1.45分
with ff as
(select tt.*, case when c>300 then 5 when c>100 then 4 when c>50 then 3 when c>10 then 2 else 1 end f_score  
from (select u.user_id, count(1) c from userds1 u
where u.behavior_type=4
group by u.user_id order by 2 desc) tt)
select avg(f_score) from ff;



4.用户消费的频率
with ff as
(select tt.*, case when c>300 then 5 when c>100 then 4 when c>50 then 3 when c>10 then 2 else 1 end f_score  
from (select u.user_id, count(1) c from userds1 u
where u.behavior_type=4
group by u.user_id order by 2 desc) tt)
select ff.*, case when f_score<1.45 then '较低' else '较高' end f from ff;
5.创建一个新表us2
create table us2 tablespace ts1 as
select ff.*, case when f_score<1.45 then '较低' else '较高' end f 
from (select tt.*, case when c>300 then 5 when c>100 then 4 
when c>50 then 3 when c>10 then 2 else 1 end f_score  
from (select u.user_id, count(1) c from userds1 u
where u.behavior_type=4
group by u.user_id order by 2 desc) tt) ff;
6.两个表进行连接，完成需求
select u1.user_id, u1.r, u2.f,
case when r='较近' and f='较高' then '重要高价值用户' 
     when r='较远' and f='较高' then '重要唤回用户' 
     when r='较近' and f='较低' then '重要深耕用户' 
     when r='较远' and f='较低' then '重要挽留用户' 
end 用户类型
from us1 u1, us2 u2 where u1.user_id=u2.user_id;