-- Task: 01_全局用户特征_20250212 ID: 1023819179 Type: ODPS_SQL CREATE TABLE IF NOT EXISTS loghubods.mid_global_feature_20250212 ( mid STRING COMMENT 'mid' ,feature STRING COMMENT '特征JSON' ) COMMENT '全部曝光场景下的mid特征' PARTITIONED BY ( dt STRING COMMENT '天' ,hh STRING COMMENT '小时' ) LIFECYCLE 30 ; INSERT OVERWRITE TABLE loghubods.mid_global_feature_20250212 PARTITION (dt = '${dt}',hh = '${hh}') WITH t_exp AS -- 曝光特征 ( SELECT vid ,mid ,apptype ,page ,recommendpagetype ,province ,hotsencetype ,machineinfo_brand AS brand ,is_share ,return_n_uv ,return_1_uv ,share_cnt ,is_return_1 ,new_exposure_cnt ,GET_JSON_OBJECT(extend, "$.animationSceneType") AS animationSceneType ,UNIX_TIMESTAMP(TO_DATE('${dt}${hh}','YYYYMMDDHH')) AS ts_now ,UNIX_TIMESTAMP(TO_DATE('${dt}${hh}','YYYYMMDDHH')) - CAST(ts AS BIGINT) AS ts_diff FROM loghubods.dwd_recsys_alg_exposure_base_20250108 WHERE CONCAT(dt,hh) BETWEEN TO_CHAR(FROM_UNIXTIME(UNIX_TIMESTAMP(TO_DATE('${dt}${hh}','YYYYMMDDHH')) - 3600 * 24 * 7),'YYYYMMDDHH') AND TO_CHAR(FROM_UNIXTIME(UNIX_TIMESTAMP(TO_DATE('${dt}${hh}','YYYYMMDDHH')) - 3600 * 1),'YYYYMMDDHH') AND apptype NOT IN ("12") ) ,t_agg AS ( SELECT mid ,SUM(CASE WHEN ts_diff <= 3600 * 1 THEN 1 ELSE 0 END) AS exp_1h ,SUM(CASE WHEN ts_diff <= 3600 * 3 THEN 1 ELSE 0 END) AS exp_3h ,SUM(CASE WHEN ts_diff <= 3600 * 6 THEN 1 ELSE 0 END) AS exp_6h ,SUM(CASE WHEN ts_diff <= 3600 * 12 THEN 1 ELSE 0 END) AS exp_12h ,SUM(CASE WHEN ts_diff <= 3600 * 24 THEN 1 ELSE 0 END) AS exp_24h ,SUM(CASE WHEN ts_diff <= 3600 * 72 THEN 1 ELSE 0 END) AS exp_72h ,SUM(CASE WHEN ts_diff <= 3600 * 168 THEN 1 ELSE 0 END) AS exp_168h ,SUM(CASE WHEN ts_diff <= 3600 * 1 THEN is_share ELSE 0 END) AS is_share_1h ,SUM(CASE WHEN ts_diff <= 3600 * 3 THEN is_share ELSE 0 END) AS is_share_3h ,SUM(CASE WHEN ts_diff <= 3600 * 6 THEN is_share ELSE 0 END) AS is_share_6h ,SUM(CASE WHEN ts_diff <= 3600 * 12 THEN is_share ELSE 0 END) AS is_share_12h ,SUM(CASE WHEN ts_diff <= 3600 * 24 THEN is_share ELSE 0 END) AS is_share_24h ,SUM(CASE WHEN ts_diff <= 3600 * 72 THEN is_share ELSE 0 END) AS is_share_72h ,SUM(CASE WHEN ts_diff <= 3600 * 168 THEN is_share ELSE 0 END) AS is_share_168h ,SUM(CASE WHEN ts_diff <= 3600 * 1 THEN share_cnt ELSE 0 END) AS share_cnt_1h ,SUM(CASE WHEN ts_diff <= 3600 * 3 THEN share_cnt ELSE 0 END) AS share_cnt_3h ,SUM(CASE WHEN ts_diff <= 3600 * 6 THEN share_cnt ELSE 0 END) AS share_cnt_6h ,SUM(CASE WHEN ts_diff <= 3600 * 12 THEN share_cnt ELSE 0 END) AS share_cnt_12h ,SUM(CASE WHEN ts_diff <= 3600 * 24 THEN share_cnt ELSE 0 END) AS share_cnt_24h ,SUM(CASE WHEN ts_diff <= 3600 * 72 THEN share_cnt ELSE 0 END) AS share_cnt_72h ,SUM(CASE WHEN ts_diff <= 3600 * 168 THEN share_cnt ELSE 0 END) AS share_cnt_168h ,SUM(CASE WHEN ts_diff <= 3600 * 1 THEN is_return_1 ELSE 0 END) AS is_return_1_1h ,SUM(CASE WHEN ts_diff <= 3600 * 3 THEN is_return_1 ELSE 0 END) AS is_return_1_3h ,SUM(CASE WHEN ts_diff <= 3600 * 6 THEN is_return_1 ELSE 0 END) AS is_return_1_6h ,SUM(CASE WHEN ts_diff <= 3600 * 12 THEN is_return_1 ELSE 0 END) AS is_return_1_12h ,SUM(CASE WHEN ts_diff <= 3600 * 24 THEN is_return_1 ELSE 0 END) AS is_return_1_24h ,SUM(CASE WHEN ts_diff <= 3600 * 72 THEN is_return_1 ELSE 0 END) AS is_return_1_72h ,SUM(CASE WHEN ts_diff <= 3600 * 168 THEN is_return_1 ELSE 0 END) AS is_return_1_168h ,SUM(CASE WHEN ts_diff <= 3600 * 1 THEN return_1_uv ELSE 0 END) AS return_1_uv_1h ,SUM(CASE WHEN ts_diff <= 3600 * 3 THEN return_1_uv ELSE 0 END) AS return_1_uv_3h ,SUM(CASE WHEN ts_diff <= 3600 * 6 THEN return_1_uv ELSE 0 END) AS return_1_uv_6h ,SUM(CASE WHEN ts_diff <= 3600 * 12 THEN return_1_uv ELSE 0 END) AS return_1_uv_12h ,SUM(CASE WHEN ts_diff <= 3600 * 24 THEN return_1_uv ELSE 0 END) AS return_1_uv_24h ,SUM(CASE WHEN ts_diff <= 3600 * 72 THEN return_1_uv ELSE 0 END) AS return_1_uv_72h ,SUM(CASE WHEN ts_diff <= 3600 * 168 THEN return_1_uv ELSE 0 END) AS return_1_uv_168h ,SUM(CASE WHEN ts_diff <= 3600 * 1 THEN share_cnt ELSE 0 END) AS share_1h -- 曝光的分享次数当作用户的分享次数 ,SUM(CASE WHEN ts_diff <= 3600 * 3 THEN share_cnt ELSE 0 END) AS share_3h ,SUM(CASE WHEN ts_diff <= 3600 * 6 THEN share_cnt ELSE 0 END) AS share_6h ,SUM(CASE WHEN ts_diff <= 3600 * 12 THEN share_cnt ELSE 0 END) AS share_12h ,SUM(CASE WHEN ts_diff <= 3600 * 24 THEN share_cnt ELSE 0 END) AS share_24h ,SUM(CASE WHEN ts_diff <= 3600 * 72 THEN share_cnt ELSE 0 END) AS share_72h ,SUM(CASE WHEN ts_diff <= 3600 * 168 THEN share_cnt ELSE 0 END) AS share_168h ,SUM( CASE WHEN ts_diff <= 3600 * 1 AND page IN ("回流页","详情页") AND animationSceneType IS NULL THEN 1 ELSE 0 END ) AS click_1h -- 回流页的曝光当作用户的回流点击次数 ,SUM( CASE WHEN ts_diff <= 3600 * 3 AND page IN ("回流页","详情页") AND animationSceneType IS NULL THEN 1 ELSE 0 END ) AS click_3h ,SUM( CASE WHEN ts_diff <= 3600 * 6 AND page IN ("回流页","详情页") AND animationSceneType IS NULL THEN 1 ELSE 0 END ) AS click_6h ,SUM( CASE WHEN ts_diff <= 3600 * 12 AND page IN ("回流页","详情页") AND animationSceneType IS NULL THEN 1 ELSE 0 END ) AS click_12h ,SUM( CASE WHEN ts_diff <= 3600 * 24 AND page IN ("回流页","详情页") AND animationSceneType IS NULL THEN 1 ELSE 0 END ) AS click_24h ,SUM( CASE WHEN ts_diff <= 3600 * 72 AND page IN ("回流页","详情页") AND animationSceneType IS NULL THEN 1 ELSE 0 END ) AS click_72h ,SUM( CASE WHEN ts_diff <= 3600 * 168 AND page IN ("回流页","详情页") AND animationSceneType IS NULL THEN 1 ELSE 0 END ) AS click_168h FROM t_exp WHERE ts_diff >= 0 GROUP BY mid ) ,t_index AS ( SELECT * ,ROUND(COALESCE(is_share_1h / exp_1h,0),6) AS str_one_1h ,ROUND(COALESCE(return_1_uv_1h / is_share_1h,0),6) AS ros_one_1h ,ROUND(COALESCE(share_cnt_1h / exp_1h,0),6) AS str_1h ,ROUND(COALESCE(return_1_uv_1h / share_cnt_1h,0),6) AS ros_1h ,ROUND(COALESCE(is_return_1_1h / exp_1h,0),6) AS str_plus_1h ,ROUND(COALESCE(return_1_uv_1h / is_return_1_1h,0),6) AS ros_minus_1h ,ROUND(COALESCE(return_1_uv_1h / exp_1h,0),6) AS rovn_1h ,ROUND(COALESCE(is_share_3h / exp_3h,0),6) AS str_one_3h ,ROUND(COALESCE(return_1_uv_3h / is_share_3h,0),6) AS ros_one_3h ,ROUND(COALESCE(share_cnt_3h / exp_3h,0),6) AS str_3h ,ROUND(COALESCE(return_1_uv_3h / share_cnt_3h,0),6) AS ros_3h ,ROUND(COALESCE(is_return_1_3h / exp_3h,0),6) AS str_plus_3h ,ROUND(COALESCE(return_1_uv_3h / is_return_1_3h,0),6) AS ros_minus_3h ,ROUND(COALESCE(return_1_uv_3h / exp_3h,0),6) AS rovn_3h ,ROUND(COALESCE(is_share_6h / exp_6h,0),6) AS str_one_6h ,ROUND(COALESCE(return_1_uv_6h / is_share_6h,0),6) AS ros_one_6h ,ROUND(COALESCE(share_cnt_6h / exp_6h,0),6) AS str_6h ,ROUND(COALESCE(return_1_uv_6h / share_cnt_6h,0),6) AS ros_6h ,ROUND(COALESCE(is_return_1_6h / exp_6h,0),6) AS str_plus_6h ,ROUND(COALESCE(return_1_uv_6h / is_return_1_6h,0),6) AS ros_minus_6h ,ROUND(COALESCE(return_1_uv_6h / exp_6h,0),6) AS rovn_6h ,ROUND(COALESCE(is_share_12h / exp_12h,0),6) AS str_one_12h ,ROUND(COALESCE(return_1_uv_12h / is_share_12h,0),6) AS ros_one_12h ,ROUND(COALESCE(share_cnt_12h / exp_12h,0),6) AS str_12h ,ROUND(COALESCE(return_1_uv_12h / share_cnt_12h,0),6) AS ros_12h ,ROUND(COALESCE(is_return_1_12h / exp_12h,0),6) AS str_plus_12h ,ROUND(COALESCE(return_1_uv_12h / is_return_1_12h,0),6) AS ros_minus_12h ,ROUND(COALESCE(return_1_uv_12h / exp_12h,0),6) AS rovn_12h ,ROUND(COALESCE(is_share_24h / exp_24h,0),6) AS str_one_24h ,ROUND(COALESCE(return_1_uv_24h / is_share_24h,0),6) AS ros_one_24h ,ROUND(COALESCE(share_cnt_24h / exp_24h,0),6) AS str_24h ,ROUND(COALESCE(return_1_uv_24h / share_cnt_24h,0),6) AS ros_24h ,ROUND(COALESCE(is_return_1_24h / exp_24h,0),6) AS str_plus_24h ,ROUND(COALESCE(return_1_uv_24h / is_return_1_24h,0),6) AS ros_minus_24h ,ROUND(COALESCE(return_1_uv_24h / exp_24h,0),6) AS rovn_24h ,ROUND(COALESCE(is_share_72h / exp_72h,0),6) AS str_one_72h ,ROUND(COALESCE(return_1_uv_72h / is_share_72h,0),6) AS ros_one_72h ,ROUND(COALESCE(share_cnt_72h / exp_72h,0),6) AS str_72h ,ROUND(COALESCE(return_1_uv_72h / share_cnt_72h,0),6) AS ros_72h ,ROUND(COALESCE(is_return_1_72h / exp_72h,0),6) AS str_plus_72h ,ROUND(COALESCE(return_1_uv_72h / is_return_1_72h,0),6) AS ros_minus_72h ,ROUND(COALESCE(return_1_uv_72h / exp_72h,0),6) AS rovn_72h ,ROUND(COALESCE(is_share_168h / exp_168h,0),6) AS str_one_168h ,ROUND(COALESCE(return_1_uv_168h / is_share_168h,0),6) AS ros_one_168h ,ROUND(COALESCE(share_cnt_168h / exp_168h,0),6) AS str_168h ,ROUND(COALESCE(return_1_uv_168h / share_cnt_168h,0),6) AS ros_168h ,ROUND(COALESCE(is_return_1_168h / exp_168h,0),6) AS str_plus_168h ,ROUND(COALESCE(return_1_uv_168h / is_return_1_168h,0),6) AS ros_minus_168h ,ROUND(COALESCE(return_1_uv_168h / exp_168h,0),6) AS rovn_168h FROM t_agg ) ,t_result AS ( SELECT mid ,JSON_FORMAT(JSON_OBJECT( "exp_1h", CAST(exp_1h AS STRING), "exp_3h", CAST(exp_3h AS STRING), "exp_6h", CAST(exp_6h AS STRING), "exp_12h", CAST(exp_12h AS STRING), "exp_24h", CAST(exp_24h AS STRING), "exp_72h", CAST(exp_72h AS STRING), "exp_168h", CAST(exp_168h AS STRING), "is_share_1h", CAST(is_share_1h AS STRING), "is_share_3h", CAST(is_share_3h AS STRING), "is_share_6h", CAST(is_share_6h AS STRING), "is_share_12h", CAST(is_share_12h AS STRING), "is_share_24h", CAST(is_share_24h AS STRING), "is_share_72h", CAST(is_share_72h AS STRING), "is_share_168h", CAST(is_share_168h AS STRING), "share_cnt_1h", CAST(share_cnt_1h AS STRING), "share_cnt_3h", CAST(share_cnt_3h AS STRING), "share_cnt_6h", CAST(share_cnt_6h AS STRING), "share_cnt_12h", CAST(share_cnt_12h AS STRING), "share_cnt_24h", CAST(share_cnt_24h AS STRING), "share_cnt_72h", CAST(share_cnt_72h AS STRING), "share_cnt_168h", CAST(share_cnt_168h AS STRING), "is_return_1_1h", CAST(is_return_1_1h AS STRING), "is_return_1_3h", CAST(is_return_1_3h AS STRING), "is_return_1_6h", CAST(is_return_1_6h AS STRING), "is_return_1_12h", CAST(is_return_1_12h AS STRING), "is_return_1_24h", CAST(is_return_1_24h AS STRING), "is_return_1_72h", CAST(is_return_1_72h AS STRING), "is_return_1_168h", CAST(is_return_1_168h AS STRING), "return_1_uv_1h", CAST(return_1_uv_1h AS STRING), "return_1_uv_3h", CAST(return_1_uv_3h AS STRING), "return_1_uv_6h", CAST(return_1_uv_6h AS STRING), "return_1_uv_12h", CAST(return_1_uv_12h AS STRING), "return_1_uv_24h", CAST(return_1_uv_24h AS STRING), "return_1_uv_72h", CAST(return_1_uv_72h AS STRING), "return_1_uv_168h", CAST(return_1_uv_168h AS STRING), "share_1h", CAST(share_1h AS STRING), "share_3h", CAST(share_3h AS STRING), "share_6h", CAST(share_6h AS STRING), "share_12h", CAST(share_12h AS STRING), "share_24h", CAST(share_24h AS STRING), "share_72h", CAST(share_72h AS STRING), "share_168h", CAST(share_168h AS STRING), "click_1h", CAST(click_1h AS STRING), "click_3h", CAST(click_3h AS STRING), "click_6h", CAST(click_6h AS STRING), "click_12h", CAST(click_12h AS STRING), "click_24h", CAST(click_24h AS STRING), "click_72h", CAST(click_72h AS STRING), "click_168h", CAST(click_168h AS STRING), "str_one_1h", CAST(str_one_1h AS STRING), -- "ros_one_1h", CAST(ros_one_1h AS STRING), -- "str_1h", CAST(str_1h AS STRING), -- "ros_1h", CAST(ros_1h AS STRING), -- "str_plus_1h", CAST(str_plus_1h AS STRING), -- "ros_minus_1h", CAST(ros_minus_1h AS STRING), -- "rovn_1h", CAST(rovn_1h AS STRING), -- "str_one_3h", CAST(str_one_3h AS STRING), -- "ros_one_3h", CAST(ros_one_3h AS STRING), -- "str_3h", CAST(str_3h AS STRING), -- "ros_3h", CAST(ros_3h AS STRING), -- "str_plus_3h", CAST(str_plus_3h AS STRING), -- "ros_minus_3h", CAST(ros_minus_3h AS STRING), -- "rovn_3h", CAST(rovn_3h AS STRING), -- "str_one_6h", CAST(str_one_6h AS STRING), -- "ros_one_6h", CAST(ros_one_6h AS STRING), -- "str_6h", CAST(str_6h AS STRING), -- "ros_6h", CAST(ros_6h AS STRING), -- "str_plus_6h", CAST(str_plus_6h AS STRING), -- "ros_minus_6h", CAST(ros_minus_6h AS STRING), -- "rovn_6h", CAST(rovn_6h AS STRING), -- "str_one_12h", CAST(str_one_12h AS STRING), -- "ros_one_12h", CAST(ros_one_12h AS STRING), -- "str_12h", CAST(str_12h AS STRING), -- "ros_12h", CAST(ros_12h AS STRING), -- "str_plus_12h", CAST(str_plus_12h AS STRING), -- "ros_minus_12h", CAST(ros_minus_12h AS STRING), -- "rovn_12h", CAST(rovn_12h AS STRING), -- "str_one_24h", CAST(str_one_24h AS STRING), -- "ros_one_24h", CAST(ros_one_24h AS STRING), -- "str_24h", CAST(str_24h AS STRING), -- "ros_24h", CAST(ros_24h AS STRING), -- "str_plus_24h", CAST(str_plus_24h AS STRING), -- "ros_minus_24h", CAST(ros_minus_24h AS STRING), -- "rovn_24h", CAST(rovn_24h AS STRING), -- "str_one_72h", CAST(str_one_72h AS STRING), -- "ros_one_72h", CAST(ros_one_72h AS STRING), -- "str_72h", CAST(str_72h AS STRING), -- "ros_72h", CAST(ros_72h AS STRING), -- "str_plus_72h", CAST(str_plus_72h AS STRING), -- "ros_minus_72h", CAST(ros_minus_72h AS STRING), -- "rovn_72h", CAST(rovn_72h AS STRING), -- "str_one_168h", CAST(str_one_168h AS STRING), -- "ros_one_168h", CAST(ros_one_168h AS STRING), -- "str_168h", CAST(str_168h AS STRING), -- "ros_168h", CAST(ros_168h AS STRING), -- "str_plus_168h", CAST(str_plus_168h AS STRING), -- "ros_minus_168h", CAST(ros_minus_168h AS STRING), "rovn_168h", CAST(rovn_168h AS STRING) )) FROM t_index )SELECT * FROM t_result ;