20220420

with tt as
(select count(case when u.behavior_type=1 then u.user_id end) dj_user,
        count(case when u.behavior_type=2 then u.user_id end) sc_user,
        count(case when u.behavior_type=3 then u.user_id end) jg_user,
        count(case when u.behavior_type=4 then u.user_id end) gm_user
from userds1 u)
select (sc_user * 100)/dj_user 收藏转化率, 
       (jg_user * 100)/dj_user 加购转化率,
       (gm_user * 100)/dj_user 购买转化率
from tt;

````sql
select u.time, 
 sum(case when u.behavior_type=1 then 1 else 0 end) dj,
 sum(case when u.behavior_type=2 then 1 else 0 end) sc,
 sum(case when u.behavior_type=3 then 1 else 0 end) jg,
 sum(case when u.behavior_type=4 then 1 else 0 end) gm
from userds1 u
group by time
order by 1; 

````sql

create table userds2 tablespace ts1 as select u.time, 
 sum(case when u.behavior_type=1 then 1 else 0 end) dj,
 sum(case when u.behavior_type=2 then 1 else 0 end) sc,
 sum(case when u.behavior_type=3 then 1 else 0 end) jg,
 sum(case when u.behavior_type=4 then 1 else 0 end) gm
from userds1 u
group by time
order by 1 ;
select 
(lead(dj) over(order by time) -dj)*100/dj 点击增长率,
(lead(sc) over(order by time) -sc)*100/sc 收藏增长率,
(lead(jg) over(order by time) -jg)*100/jg 加购增长率,
(lead(gm) over(order by time) -gm)*100/gm 购买增长率
from userds2 u

````sql
with tt as
(select u.*,
lead(time) over(partition by u.user_id order by time) day1
from userds3 u)
select tt.time, count(case when time+1=day1 then 1 end)/count(*) day1 
from tt
group by tt.time order by 1;