在排序后划等份
先上代码
做一个店铺综合分的探索项目
drop table mengtuitmp.lsc_new_haodian_v2_result ;
create table mengtuitmp.lsc_new_haodian_v2_result stored as parquet as
select
a.mall_id
,a.mall_name
,a.main_cat_name
,b.main_cat_id
,b.gmv_1m
,b.brand_tonality_score --品牌调性分
,b.punish_score --罚款扣分
-- 是否样本量不足
,case when b.total_order_cnt is null then null else if(b.total_order_cnt <=100,1,0) end as is_sample_short__quick_delivery_score
,case when b.refund_money_num_15d is null then null else 0 end as is_sample_short__refund_money_dura_score -- 这两个指标不管样本量
,case when b.refund_goods_num_15d is null then null else 0 end as is_sample_short__refund_goods_dura_score -- 这两个指标不管样本量
,case when b.order_num_15d is null then null else if(b.order_num_15d <=30,1,0) end as is_sample_short__dispute_order_rate_score
,case when b.consult_uv_15d is null then null else if(b.consult_uv_15d <=15,1,0) end as is_sample_short__resp_rate_score
,case when b.consult_uv_15d is null then null else if(b.consult_uv_15d <=15,1,0) end as is_sample_short__avg_resp_dura_score
,case when b.evaluate_cnt_15d is null then null else if(b.evaluate_cnt_15d <=15,1,0) end as is_sample_short__solved_rate_score
,case when b.description_cnt_1m is null then null else if(b.description_cnt_1m <=30,1,0) end as is_sample_short__description_rating_score
,case when b.shipping_cnt_1m is null then null else if(b.shipping_cnt_1m <=30,1,0) end as is_sample_short__shipping_rating_score
,case when b.service_cnt_1m is null then null else if(b.service_cnt_1m <=30,1,0) end as is_sample_short__service_rating_score
-- 指标绝对值
,b.hegui_order_rate as hegui_order_rate -- 极速发货订单占比
,max(b.hegui_order_rate) over(partition by b.main_cat_id) as hegui_order_rate_max -- 极速发货订单占比最大值
,min(b.hegui_order_rate) over(partition by b.main_cat_id) as hegui_order_rate_min -- 极速发货订单占比最xiao值
,round(b.sum_refund_money_dura_15d/ b.refund_money_num_15d,0)/3600 as refund_money_dura_15d -- 仅退款处理时长小时
,max(round(b.sum_refund_money_dura_15d/ b.refund_money_num_15d,0)/3600) over(partition by b.main_cat_id) as refund_money_dura_15d_max -- 仅退款处理时长小时最大值
,min(round(b.sum_refund_money_dura_15d/ b.refund_money_num_15d,0)/3600) over(partition by b.main_cat_id) as refund_money_dura_15d_min -- 仅退款处理时长小时最xiao值
,round(b.sum_refund_goods_dura_15d/ b.refund_goods_num_15d ,0)/3600 as refund_goods_dura_15d -- 退货退款处理时长小时
,max(round(b.sum_refund_goods_dura_15d/ b.refund_goods_num_15d ,0)/3600) over(partition by b.main_cat_id) as refund_goods_dura_15d_max -- 退货退款处理时长小时最大值
,min(round(b.sum_refund_goods_dura_15d/ b.refund_goods_num_15d ,0)/3600) over(partition by b.main_cat_id) as refund_goods_dura_15d_min -- 退货退款处理时长小时最xiao值
,round(nvl(b.dispute_order_num_15d,0)/b.order_num_15d,4) as dispute_order_rate_15d -- 平台介入率
,max(round(nvl(b.dispute_order_num_15d,0)/b.order_num_15d,4)) over(partition by b.main_cat_id) as dispute_order_rate_15d_max -- 平台介入率最大值
,min(round(nvl(b.dispute_order_num_15d,0)/b.order_num_15d,4)) over(partition by b.main_cat_id) as dispute_order_rate_15d_min -- 平台介入率最xiao值
,round(b.resp_uv_15d/b.consult_uv_15d,4) as resp_rate_15d -- 回复率
,max(round(b.resp_uv_15d/b.consult_uv_15d,4)) over(partition by b.main_cat_id) as resp_rate_15d_max -- 回复率最大值
,min(round(b.resp_uv_15d/b.consult_uv_15d,4)) over(partition by b.main_cat_id) as resp_rate_15d_min -- 回复率最xiao值
,round(b.sum_resp_dura_15d/b.resp_cnt_15d,4) as avg_resp_dura_15d -- 平均回复时长秒
,max(round(b.sum_resp_dura_15d/b.resp_cnt_15d,4)) over(partition by b.main_cat_id) as avg_resp_dura_15d_max -- 极速发货订单占比最大值
,min(round(b.sum_resp_dura_15d/b.resp_cnt_15d,4)) over(partition by b.main_cat_id) as avg_resp_dura_15d_min -- 极速发货订单占比最xiao值
,round(b.solved_cnt_15d/b.evaluate_cnt_15d,4) as solved_rate_15d -- 问题解决率
,max(round(b.solved_cnt_15d/b.evaluate_cnt_15d,4)) over(partition by b.main_cat_id) as solved_rate_15d_max -- 极速发货订单占比最大值
,min(round(b.solved_cnt_15d/b.evaluate_cnt_15d,4)) over(partition by b.main_cat_id) as solved_rate_15d_min -- 极速发货订单占比最xiao值
,b.shipping_rating_1m_ranking -- '类目内近30天物流分排名',
,b.description_rating_1m_ranking-- '类目内近30天商品分排名',
,b.service_rating_1m_ranking --'类目内近30天服务分排名',
,b.main_cat_mall_cnt --近30天类目内总商铺数
,b.shipping_rating_1m --近30日物流均分
,b.description_rating_1m
,b.service_rating_1m
,b.dsr_rating_cnt_1m --dsr评价数
from (
select
*
from mengtuidm.mid_mall_onsale_all_list_ds as a
where 1=1
and a.dt = date_add(current_date,-1)
and a.is_pay_deposit = 1 -- 交了保证金
and a.is_quit = 0 -- 未提交退店
and a.is_blocked_mall = 0 -- 排除屏蔽的店铺
) as a -- 计算好店的基础店铺
left join mengtuidw.dws_prd_mall_score_v2_ds as b -- 陶玉清的指标表
on a.mall_id = b.mall_id and b.dt = date_add(current_date,-1)
where 1=1
;
drop table if exists mengtuitmp.lsc_new_haodian_v2_result_mall_cnt ;
create table mengtuitmp.lsc_new_haodian_v2_result_mall_cnt as
select
a.mall_id
,a.mall_name
,a.main_cat_name
,a.gmv_1m
,a.brand_tonality_score --品牌调性分
,a.punish_score --罚款扣分
,case when a.hegui_order_rate is null then null when a.hegui_order_rate < b.x1_2_hegui_order_rate then -1 when is_sample_short__quick_delivery_score = 1 or (a.hegui_order_rate >= b.x1_2_hegui_order_rate and a.hegui_order_rate < c.x2_2_hegui_order_rate ) then 1 else 3 end as quick_delivery_score_level
,case when a.hegui_order_rate is null then 80 --如果为null 则归到中 但是分数为80
when a.hegui_order_rate < b.x1_2_hegui_order_rate then (a.hegui_order_rate-0)/(b.x1_2_hegui_order_rate-0)*15+55
when a.hegui_order_rate >= b.x1_2_hegui_order_rate and a.hegui_order_rate < c.x2_2_hegui_order_rate then (a.hegui_order_rate-b.x1_2_hegui_order_rate)/(c.x2_2_hegui_order_rate-b.x1_2_hegui_order_rate)*20+70
when is_sample_short__quick_delivery_score=1 and (a.hegui_order_rate >= c.x2_2_hegui_order_rate) then 80
else (a.hegui_order_rate-c.x2_2_hegui_order_rate)/(1-c.x2_2_hegui_order_rate)*10+90 end as quick_delivery_score
--仅退款时常小时
,case when a.refund_money_dura_15d is null then null when a.refund_money_dura_15d > b.x1_1_refund_money_dura_15d then -1
when is_sample_short__refund_money_dura_score = 1 or (a.refund_money_dura_15d <= b.x1_1_refund_money_dura_15d
and a.refund_money_dura_15d > c.x2_1_refund_money_dura_15d ) then 1
else 3 end as refund_money_dura_score_level
,case when a.refund_money_dura_15d is null then 80 --如果为null 则归到中 但是分数为80
when a.refund_money_dura_15d > b.x1_1_refund_money_dura_15d then case when a.refund_money_dura_15d>48 then 55
else (48-a.refund_money_dura_15d)/(48-b.x1_1_refund_money_dura_15d)*15+55 end
when a.refund_money_dura_15d > c.x2_1_refund_money_dura_15d and a.refund_money_dura_15d <= b.x1_1_refund_money_dura_15d then (b.x1_1_refund_money_dura_15d-a.refund_money_dura_15d)/(b.x1_1_refund_money_dura_15d-c.x2_1_refund_money_dura_15d)*20+70
when is_sample_short__refund_money_dura_score=1 and (a.refund_money_dura_15d <= c.x2_1_refund_money_dura_15d) then 80
else (c.x2_1_refund_money_dura_15d-a.refund_money_dura_15d)/(c.x2_1_refund_money_dura_15d-0)*10+90 end as refund_money_dura_score
--退货退款时常小时
,case when a.refund_goods_dura_15d is null then null when a.refund_goods_dura_15d > b.x1_1_refund_goods_dura_15d then -1
when is_sample_short__refund_goods_dura_score = 1 or (a.refund_goods_dura_15d <= b.x1_1_refund_goods_dura_15d
and a.refund_goods_dura_15d > c.x2_1_refund_goods_dura_15d ) then 1 else 3 end as refund_goods_dura_score_level
,case when a.refund_goods_dura_15d is null then 80 --如果为null 则归到中 但是分数为80
when a.refund_goods_dura_15d > b.x1_1_refund_goods_dura_15d then case when a.refund_goods_dura_15d>=360 then 55
else (360-a.refund_goods_dura_15d)/(360-b.x1_1_refund_goods_dura_15d)*15+55 end
when a.refund_goods_dura_15d > c.x2_1_refund_goods_dura_15d and a.refund_goods_dura_15d <= b.x1_1_refund_goods_dura_15d then (b.x1_1_refund_goods_dura_15d-a.refund_goods_dura_15d)/(b.x1_1_refund_goods_dura_15d-c.x2_1_refund_goods_dura_15d)*20+70
when is_sample_short__refund_goods_dura_score=1 and (a.refund_goods_dura_15d <= c.x2_1_refund_goods_dura_15d) then 80
else (c.x2_1_refund_goods_dura_15d-a.refund_goods_dura_15d)/(c.x2_1_refund_goods_dura_15d-0)*10+90 end as refund_goods_dura_score
--越小越好_平台介入率
,case when a.dispute_order_rate_15d is null then null when a.dispute_order_rate_15d > b.x1_1_dispute_order_rate_15d then -1
when is_sample_short__dispute_order_rate_score = 1 or (a.dispute_order_rate_15d <= b.x1_1_dispute_order_rate_15d
and a.dispute_order_rate_15d > c.x2_1_dispute_order_rate_15d) then 1
else 3 end as dispute_order_rate_score_level
,case when a.dispute_order_rate_15d is null then 80 --如果为null 则归到中 但是分数为80
when a.dispute_order_rate_15d > b.x1_1_dispute_order_rate_15d then case when a.dispute_order_rate_15d>360 then 55
else (360-a.dispute_order_rate_15d)/(360-b.x1_1_dispute_order_rate_15d)*15+55 end
when a.dispute_order_rate_15d > c.x2_1_dispute_order_rate_15d and a.dispute_order_rate_15d <= b.x1_1_dispute_order_rate_15d then (b.x1_1_dispute_order_rate_15d-a.dispute_order_rate_15d)/(b.x1_1_dispute_order_rate_15d-c.x2_1_dispute_order_rate_15d)*20+70
when is_sample_short__dispute_order_rate_score=1 and (a.dispute_order_rate_15d <= c.x2_1_dispute_order_rate_15d) then 80
else (c.x2_1_dispute_order_rate_15d-a.dispute_order_rate_15d)/(c.x2_1_dispute_order_rate_15d-0)*10+90 end as dispute_order_rate_score
--越大越好_回复率
,case when a.resp_rate_15d is null then null when a.resp_rate_15d < b.x1_2_resp_rate_15d then -1
when is_sample_short__resp_rate_score = 1 or (a.resp_rate_15d >= b.x1_2_resp_rate_15d
and a.resp_rate_15d < c.x2_2_resp_rate_15d ) then 1
else 3 end as resp_rate_score_level
,case when a.resp_rate_15d is null then 80 --如果为null 则归到中 但是分数为80
when a.resp_rate_15d < b.x1_2_resp_rate_15d then (a.resp_rate_15d-0)/(b.x1_2_resp_rate_15d-0)*15+55
when a.resp_rate_15d >= b.x1_2_resp_rate_15d and a.resp_rate_15d < c.x2_2_resp_rate_15d then (a.resp_rate_15d-b.x1_2_resp_rate_15d)/(c.x2_2_resp_rate_15d-b.x1_2_resp_rate_15d)*20+70
when is_sample_short__resp_rate_score=1 and (a.resp_rate_15d >= c.x2_2_resp_rate_15d) then 80
else (a.resp_rate_15d-c.x2_2_resp_rate_15d)/(1-c.x2_2_resp_rate_15d)*10+90 end as resp_rate_score
--越小越好_平均回复时长秒
,case when a.avg_resp_dura_15d is null then null when a.avg_resp_dura_15d > b.x1_1_avg_resp_dura_15d then -1
when is_sample_short__avg_resp_dura_score = 1 or (a.avg_resp_dura_15d <= b.x1_1_avg_resp_dura_15d
and a.avg_resp_dura_15d > c.x2_1_avg_resp_dura_15d ) then 1
else 3 end as avg_resp_dura_score_level
,case when a.avg_resp_dura_15d is null then 80 --如果为null 则归到中 但是分数为80
when a.avg_resp_dura_15d > b.x1_1_avg_resp_dura_15d then case when a.avg_resp_dura_15d>=86400 then 55
else (86400-a.avg_resp_dura_15d)/(86400-b.x1_1_avg_resp_dura_15d)*15+55 end
when a.avg_resp_dura_15d > c.x2_1_avg_resp_dura_15d and a.avg_resp_dura_15d <= b.x1_1_avg_resp_dura_15d then (b.x1_1_avg_resp_dura_15d-a.avg_resp_dura_15d)/(b.x1_1_avg_resp_dura_15d-c.x2_1_avg_resp_dura_15d)*20+70
when is_sample_short__avg_resp_dura_score=1 and (a.avg_resp_dura_15d <= c.x2_1_avg_resp_dura_15d) then 80
else (c.x2_1_avg_resp_dura_15d-a.avg_resp_dura_15d)/(c.x2_1_avg_resp_dura_15d-0)*10+90 end as avg_resp_dura_score
----越大越好_问题解决率
,case when a.solved_rate_15d is null then null when a.solved_rate_15d < b.x1_2_solved_rate_15d then -1
when is_sample_short__solved_rate_score = 1 or (a.solved_rate_15d >= b.x1_2_solved_rate_15d
and a.solved_rate_15d < c.x2_2_solved_rate_15d ) then 1
else 3 end as solved_rate_score_level
,case when a.solved_rate_15d is null then 80 --如果为null 则归到中 但是分数为80
when a.solved_rate_15d < b.x1_2_solved_rate_15d then (a.solved_rate_15d-0)/(b.x1_2_solved_rate_15d-0)*15+55
when a.solved_rate_15d >= b.x1_2_solved_rate_15d and a.solved_rate_15d < c.x2_2_solved_rate_15d then (a.solved_rate_15d-b.x1_2_solved_rate_15d)/(c.x2_2_solved_rate_15d-b.x1_2_solved_rate_15d)*20+70
when is_sample_short__solved_rate_score=1 and (a.solved_rate_15d >= c.x2_2_solved_rate_15d) then 80
else (a.solved_rate_15d-c.x2_2_solved_rate_15d)/(1-c.x2_2_solved_rate_15d)*10+90 end as solved_rate_score
,case when a.shipping_rating_1m is null then null when a.shipping_rating_1m < b.x1_shipping_rating then -1 when dsr_rating_cnt_1m <=30 or (a.shipping_rating_1m >= b.x1_shipping_rating and a.shipping_rating_1m < b.x2_shipping_rating ) then 1 else 3 end as shipping_rate_score_level-- '类目内近30天物流分排名',
,case when a.shipping_rating_1m is null then 80 --如果为null 则归到中 但是分数为80
when a.shipping_rating_1m < d.x1_shipping_rating then (a.shipping_rating_1m-1)/(b.x1_shipping_rating-1)*15+55
when a.shipping_rating_1m >= d.x1_shipping_rating and a.shipping_rating_1m < d.x2_shipping_rating then (a.shipping_rating_1m-d.x1_shipping_rating)/(d.x2_shipping_rating-d.x1_shipping_rating)*20+70
when a.dsr_rating_cnt_1m<=30 and (a.shipping_rating_1m >= d.x2_shipping_rating) then 80
else (a.shipping_rating_1m-d.x2_shipping_rating)/(5-d.x2_shipping_rating)*10+90 end as shipping_rate_score
,case when a.description_rating_1m is null then null when a.description_rating_1m < b.x1_description_rating then -1 when dsr_rating_cnt_1m <=30 or (a.description_rating_1m >= b.x1_description_rating and a.description_rating_1m < b.x2_description_rating ) then 1 else 3 end as description_rate_score_level -- '类目内近30天商品分排名'
,case when a.description_rating_1m is null then 80 --如果为null 则归到中 但是分数为80
when a.description_rating_1m < d.x1_description_rating then (a.description_rating_1m-1)/(b.x1_description_rating-1)*15+55
when a.description_rating_1m >= d.x1_description_rating and a.description_rating_1m < d.x2_description_rating then (a.description_rating_1m-d.x1_description_rating)/(d.x2_description_rating-d.x1_description_rating)*20+70
when a.dsr_rating_cnt_1m<=30 and (a.description_rating_1m >= d.x2_description_rating) then 80
else (a.description_rating_1m-d.x2_description_rating)/(5-d.x2_description_rating)*10+90 end as description_rate_score
,case when a.service_rating_1m is null then null when a.service_rating_1m < b.x1_service_rating then -1 when dsr_rating_cnt_1m <=30 or (a.service_rating_1m >= b.x1_service_rating and a.service_rating_1m < b.x2_service_rating ) then 1 else 3 end as service_rate_score_level -- '类目内近30天服务分排名'
,case when a.service_rating_1m is null then 80 --如果为null 则归到中 但是分数为80
when a.service_rating_1m < d.x1_service_rating then (a.service_rating_1m-1)/(b.x1_service_rating-1)*15+55
when a.service_rating_1m >= d.x1_service_rating and a.service_rating_1m < d.x2_service_rating then (a.service_rating_1m-d.x1_service_rating)/(d.x2_service_rating-d.x1_service_rating)*20+70
when a.dsr_rating_cnt_1m<=30 and (a.service_rating_1m >= d.x2_service_rating) then 80
else (a.service_rating_1m-d.x2_service_rating)/(5-d.x2_service_rating)*10+90 end as service_rate_score
from mengtuitmp.lsc_new_haodian_v2_result as a
left join test.lsc_new_haodian_v2_result_x1_zhoulv as b on 1=1
left join test.lsc_new_haodian_v2_result_x2_zhoulv as c on 1=1
left join test.lsc_newhaodian_v4_dim_dsr_x1x2 as d on 1=1 ;
drop table mengtuidw.dws_prd_mall_score_v3_ds;
create table mengtuidw.dws_prd_mall_score_v3_ds as
select
quick_delivery_score+refund_money_dura_score+dispute_order_rate_score+refund_goods_dura_score+resp_rate_score+avg_resp_dura_score+solved_rate_score+shipping_rate_score+description_rate_score+service_rate_score as mall_score
,*
from mengtuitmp.lsc_new_haodian_v2_result_mall_cnt;
扫一扫
5332
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
