51 lines
2.3 KiB
MySQL
51 lines
2.3 KiB
MySQL
|
|
||
|
|
-- 【小悦到家完单率】
|
||
|
|
select
|
||
|
|
${IF(LEN(p_shop)>0,"d.comm_name","d.organ_name")} AS 分析维度
|
||
|
|
,count(*) order_num -- 订单数量
|
||
|
|
,sum(case when a.confirm_ym is null then 0 else 1 end) order_finish_num -- 订单完成数量
|
||
|
|
,IF(count(*) = 0,0,sum(case when a.confirm_ym is null then 0 else 1 end) / count(*)) AS order_finish_rate -- 完单率
|
||
|
|
from
|
||
|
|
(
|
||
|
|
select
|
||
|
|
orderNo -- 订单号
|
||
|
|
, date_format(createTime,'%Y-%m') create_ym -- 订单创建时间
|
||
|
|
, date_format(confirmTime,'%Y-%m') confirm_ym -- 订单收货时间
|
||
|
|
, vid -- 项目id
|
||
|
|
from dwd_innoveco_order_info_d -- 订单信息
|
||
|
|
)a
|
||
|
|
inner join
|
||
|
|
(
|
||
|
|
select
|
||
|
|
orderNo
|
||
|
|
,skuid
|
||
|
|
,categoryLevel1
|
||
|
|
,goodsTitle -- 商品名称
|
||
|
|
from dwd_innoveco_order_item_d -- 订单项信息
|
||
|
|
where skuid in ('210627208100769','210627209100769','210627910100769','210625842100769','212609619100769','212609630100769','212603476100769','212603435100769','212603991100769','212603992100769','212603993100769','212300126100769','212298914100769','212299441100769','212299442100769','212299443100769','212299444100769','212297464100769','212297465100769','212296013100769','212314094100769','212314095100769','212314096100769','212328078100769','212328079100769','212294902100769','212292070100769','212287875100769','218885780100769','218884653100769','218884327100769','218692804100769','218691252100769','217847406100769','217479477100769')
|
||
|
|
) b
|
||
|
|
on a.orderNo = b.orderNo
|
||
|
|
left join
|
||
|
|
(
|
||
|
|
select
|
||
|
|
DISTINCT
|
||
|
|
organ_code
|
||
|
|
,organ_name
|
||
|
|
,comm_id
|
||
|
|
,comm_name
|
||
|
|
,weim_comm_id
|
||
|
|
from dim_organ_mapping
|
||
|
|
) d
|
||
|
|
on a.vid = d.weim_comm_id
|
||
|
|
where 1=1
|
||
|
|
${IF(p_timetype = "本月","/*","")}
|
||
|
|
${IF(LEN(p_startdate)>0,"AND a.create_ym >= '"+FORMAT(p_startdate,"yyyy-MM")+"'","")}
|
||
|
|
${IF(LEN(p_enddate)>0,"AND a.create_ym <= '"+FORMAT(p_enddate,"yyyy-MM")+"'","")}
|
||
|
|
${IF(p_timetype = "本月","*/","")}
|
||
|
|
${IF(p_timetype = "本月","","/*")}
|
||
|
|
AND a.create_ym = DATE_FORMAT(NOW(),'%Y-%m')
|
||
|
|
${IF(p_timetype = "本月","","*/")}
|
||
|
|
${IF(LEN(p_area)>0," AND d.organ_code IN ('"+JOINARRAY(p_area,"','")+"')", "")}
|
||
|
|
${IF(LEN(p_shop)>0," AND d.comm_id IN ('"+JOINARRAY(p_shop,"','")+"')", "")}
|
||
|
|
${if(left(fine_role,2)=="项目","and d.comm_name = '"+GETUSERDEPARTMENTS(3)+"'","")}
|
||
|
|
GROUP BY 分析维度
|