一路向西电影免费观看完整高清国语 ,久青草视频精频在线观看

缺失值處理
select
sum(user_id is null),
sum(item_id is null),
sum(item_category is null),
sum(behavior_type is null),
sum(user_geohash is null),
sum(times is null),
sum(amount is null)
from userbehavior;

-- 異常值檢查
select min(times),max(times),min(amount),max(amount) from userbehavior;

-- 2.行為轉(zhuǎn)化分析（轉(zhuǎn)化率=當(dāng)前行為人數(shù)/上一行為人數(shù))
select
behavior_type,
count(distinct user_id) as 用戶人數(shù),
lag(count(distinct user_id),1) over(order by if(behavior_type='pv',1,if(behavior_type='fav',2,if(behavior_type='cart',3,4)))) as 上一行為用戶人數(shù),
ifnull(count(distinct user_id)/lag(count(distinct user_id),1) over(order by if(behavior_type='pv',1,if(behavior_type='fav',2,if(behavior_type='cart',3,4)))),1) as 轉(zhuǎn)化率
from userbehavior_new
group by behavior_type;

select
behavior_type,
count(distinct user_id) as 用戶人數(shù),
lag(count(distinct user_id),1) over(order by if(behavior_type='pv',1,if(behavior_type='fav',2,if(behavior_type='cart',3,4)))) as 上一行為用戶人數(shù),
ifnull(count(distinct user_id)/lag(count(distinct user_id),1) over(order by if(behavior_type='pv',1,if(behavior_type='fav',2,if(behavior_type='cart',3,4)))),1) as 轉(zhuǎn)化率
from userbehavior_new
where behavior_type in ('pv','cart','buy')
group by behavior_type;

-- 每日瀏覽—加購—購買的轉(zhuǎn)化率
select
日期,
sum(if(behavior_type='pv',用戶人數(shù),0)) as 瀏覽人數(shù),
sum(if(behavior_type='cart',用戶人數(shù),0)) as 加購人數(shù),
sum(if(behavior_type='buy',用戶人數(shù),0)) as 購買人數(shù),
sum(if(behavior_type='cart',用戶人數(shù),0))/sum(if(behavior_type='pv',用戶人數(shù),0)) as 瀏覽_加購轉(zhuǎn)化率,
sum(if(behavior_type='buy',用戶人數(shù),0))/sum(if(behavior_type='cart',用戶人數(shù),0)) as 加購_購買轉(zhuǎn)化率
from
(select
日期,
behavior_type,
count(distinct user_id) as 用戶人數(shù)
from userbehavior_new
where behavior_type in ('pv','cart','buy')
group by 日期,behavior_type) as t
group by 日期;

-- 3.產(chǎn)品貢獻(xiàn)定量分析（帕累托分析）(累積銷售額百分比=累積銷售額/總銷售額)
select
item_category,
sum(amount) as 銷售額,
sum(sum(amount)) over(order by sum(amount) desc) as 累積銷售額,
sum(sum(amount)) over() as 總銷售額,
sum(sum(amount)) over(order by sum(amount) desc)/sum(sum(amount)) over() as 累積銷售額百分比
from userbehavior_new
where behavior_type='buy'
group by item_category
having sum(sum(amount)) over(order by sum(amount) desc)/sum(sum(amount)) over()<=0.8;#報錯：having子句中不能使用開窗函數(shù)

select *
from
(select
item_category,
sum(amount) as 銷售額,
sum(sum(amount)) over(order by sum(amount) desc) as 累積銷售額,
sum(sum(amount)) over() as 總銷售額,
sum(sum(amount)) over(order by sum(amount) desc)/sum(sum(amount)) over() as 累積銷售額百分比
from userbehavior_new
where behavior_type='buy'
group by item_category) as t
where 累積銷售額百分比<=0.8;

-- 4.用戶價值分析
-- 每個用戶消費(fèi)時間間隔、消費(fèi)頻次、消費(fèi)金額
select
user_id,
max(日期) as 最近一次消費(fèi)日期,
timestampdiff(day,max(日期),'2014-12-19') as 消費(fèi)時間間隔,
count(*) as 消費(fèi)頻次,
sum(amount) as 消費(fèi)金額
from userbehavior_new
where behavior_type='buy'
group by user_id;

-- RFM評分
select
user_id,
timestampdiff(day,max(日期),'2014-12-19') as R,
count(*) as F,
sum(amount) as M,
case when timestampdiff(day,max(日期),'2014-12-19')<=6 then 5
when timestampdiff(day,max(日期),'2014-12-19')<=12 then 4
when timestampdiff(day,max(日期),'2014-12-19')<=18 then 3
when timestampdiff(day,max(日期),'2014-12-19')<=24 then 2
else 1
end as R評分,
if(count(*)=1,1,if(count(*)=2,2,if(count(*)=3,3,if(count(*)=4,4,5)))) as F評分,
if(sum(amount)<100,1,if(sum(amount)<200,2,if(sum(amount)<300,3,if(sum(amount)<400,4,5)))) as M評分
from userbehavior_new
where behavior_type='buy'
group by user_id;

-- RFM均值
select
avg(R評分) as R均值,
avg(F評分) as F均值,
avg(M評分) as M均值
from
(select
user_id,
case when timestampdiff(day,max(日期),'2014-12-19')<=6 then 5
when timestampdiff(day,max(日期),'2014-12-19')<=12 then 4
when timestampdiff(day,max(日期),'2014-12-19')<=18 then 3
when timestampdiff(day,max(日期),'2014-12-19')<=24 then 2
else 1
end as R評分,
if(count(*)=1,1,if(count(*)=2,2,if(count(*)=3,3,if(count(*)=4,4,5)))) as F評分,
if(sum(amount)<100,1,if(sum(amount)<200,2,if(sum(amount)<300,3,if(sum(amount)<400,4,5)))) as M評分
from userbehavior_new
where behavior_type='buy'
group by user_id) as t;

-- RFM重要程度
select
*,
if(R評分>3.5984,'高','低') as R程度,
if(F評分>2.1039,'高','低') as F程度,
if(M評分>2.2051,'高','低') as M程度
from
(select
user_id,
timestampdiff(day,max(日期),'2014-12-19') as R,
count(*) as F,
sum(amount) as M,
case when timestampdiff(day,max(日期),'2014-12-19')<=6 then 5
when timestampdiff(day,max(日期),'2014-12-19')<=12 then 4
when timestampdiff(day,max(日期),'2014-12-19')<=18 then 3
when timestampdiff(day,max(日期),'2014-12-19')<=24 then 2
else 1
end as R評分,
if(count(*)=1,1,if(count(*)=2,2,if(count(*)=3,3,if(count(*)=4,4,5)))) as F評分,
if(sum(amount)<100,1,if(sum(amount)<200,2,if(sum(amount)<300,3,if(sum(amount)<400,4,5)))) as M評分
from userbehavior_new
where behavior_type='buy'
group by user_id) as t;

-- RFM用戶價值
select
*,
case when R程度='高' and F程度='高' and M程度='高' then '重要價值用戶'
when R程度='高' and F程度='低' and M程度='高' then '重要發(fā)展用戶'
when R程度='低' and F程度='高' and M程度='高' then '重要保持用戶'
when R程度='低' and F程度='低' and M程度='高' then '重要挽留用戶'
when R程度='高' and F程度='高' and M程度='低' then '一般價值用戶'
when R程度='高' and F程度='低' and M程度='低' then '一般發(fā)展用戶'
when R程度='低' and F程度='高' and M程度='低' then '一般保持用戶'
else '一般挽留用戶'
end as 用戶價值分類
from
(select
*,
if(R評分>3.5984,'高','低') as R程度,
if(F評分>2.1039,'高','低') as F程度,
if(M評分>2.2051,'高','低') as M程度
from
(select
user_id,
timestampdiff(day,max(日期),'2014-12-19') as R,
count(*) as F,
sum(amount) as M,
case when timestampdiff(day,max(日期),'2014-12-19')<=6 then 5
when timestampdiff(day,max(日期),'2014-12-19')<=12 then 4
when timestampdiff(day,max(日期),'2014-12-19')<=18 then 3
when timestampdiff(day,max(日期),'2014-12-19')<=24 then 2
else 1
end as R評分,
if(count(*)=1,1,if(count(*)=2,2,if(count(*)=3,3,if(count(*)=4,4,5)))) as F評分,
if(sum(amount)<100,1,if(sum(amount)<200,2,if(sum(amount)<300,3,if(sum(amount)<400,4,5)))) as M評分
from userbehavior_new
where behavior_type='buy'
group by user_id) as t1) as t2;

-- 字段處理：根據(jù)times字段增加計算字段用戶行為日期、周和小時，排除后續(xù)分析不需要的user_geohash字段，并將篩選后的結(jié)果保存到新表
select
user_id,
item_id,
item_category,
behavior_type,
date(times) as 日期,
hour(times) as 小時,
date_format(times,'%w') as 星期,
amount
from (select distinct * from userbehavior) as t;

-- 處理結(jié)果保存到視圖
create view userbehavior_new as
select
user_id,
item_id,
item_category,
behavior_type,
date(times) as 日期,
hour(times) as 小時,
date_format(times,'%w') as 星期,
amount
from (select distinct * from userbehavior) as t;

select * from userbehavior_new;

99999久久久久久亚洲,欧美人与禽猛交狂配,高清日韩av在线影院,一个人在线高清免费观看,啦啦啦在线视频免费观看www