CREATE TABLE IF NOT EXISTS ods_user_trace_data
(
    md5                     STRING COMMENT '用户uid的md5值前8位',
    uid                     STRING COMMENT '用户uid',
    ts                      BIGINT COMMENT '用户操作时间戳',
    ip                      STRING COMMENT 'ip地址',
    status                  BIGINT COMMENT '服务器返回状态码',
    bytes                   BIGINT COMMENT '返回给客户端的字节数',
    device_brand            STRING COMMENT '设备品牌',
    device                  STRING COMMENT '终端型号',
    system_type             STRING COMMENT '系统类型，Android、IOS、ipad、Windows_phone',
    customize_event         STRING COMMENT '自定义事件：登录/退出/购买/注册/点击/后台/切换用户/浏览/评论',
    use_time                BIGINT COMMENT 'APP单次使用时长，当事件为退出、后台、切换用户时有该项',
    customize_event_content STRING COMMENT '用户关注内容信息，在customize_event为浏览和评论时，包含该列'
) 
PARTITIONED BY
(
    dt STRING  --以dt作为时间分区，单位为天。
);

CREATE TABLE IF NOT EXISTS dw_user_trace_data
(
    uid                     STRING COMMENT '用户uid',
    device_brand            STRING COMMENT '设备品牌',
    device                  STRING COMMENT '终端型号',
    system_type             STRING COMMENT '系统类型，Android、IOS、ipad、Windows_phone',
    customize_event         STRING COMMENT '自定义事件：登录/退出/购买/注册/点击/后台/切换用户/浏览/评论',
    use_time                BIGINT COMMENT 'APP单次使用时长，当事件为退出、后台、切换用户时有该项',
    customize_event_content STRING COMMENT '用户关注内容信息，在customize_event为浏览和评论时，包含该列'
) 
PARTITIONED BY
(
    dt STRING  --以dt作为时间分区，单位为天。
);

CREATE TABLE IF NOT EXISTS rpt_user_trace_data
(
    browse      STRING COMMENT '浏览量',
    click       STRING COMMENT '点击量',
    purchase    STRING COMMENT '购买量',
    browse_rate STRING COMMENT '浏览转化率',
    click_rate  STRING COMMENT '点击转化量'
) 
PARTITIONED BY
(
    dt STRING  --以dt作为时间分区，单位为天。
);

insert OVERWRITE table rpt_user_trace_data PARTITION (dt=${bdp.system.bizdate})
SELECT browse as 浏览量
      ,click as 点击量
      ,purchase as 购买量
      ,concat(round((click/browse)*100,2),'%') as 点击转化率
      ,concat(round((purchase/click)*100,2),'%') as 购买转化率 
from
(SELECT dt,count(1) browse from dw_user_trace_data where customize_event='browse' 
 and dt = ${bdp.system.bizdate} group by dt) a
left JOIN
(select dt,count(1) click from dw_user_trace_data where customize_event='click' 
 and dt = ${bdp.system.bizdate} group by dt) b
on a.dt=b.dt
left JOIN
(select dt,count(1) purchase from dw_user_trace_data where customize_event='purchase' 
and dt = ${bdp.system.bizdate} group by dt)c 
on  a.dt=c.dt 
;