set odps.sql.select.output.showcolumntype=true; WITH t_raw AS ( SELECT * ,REPLACE(GET_JSON_OBJECT(extend_alg,'$.scoresMap'),"\\","") AS scoresmap ,CASE WHEN page IN ("回流后沉浸页&内页feed","详情后沉浸页","首页feed","详情页") THEN "推荐" WHEN page IN ("回流页","其他") THEN "非推荐" ELSE "其他" END AS page_type FROM loghubods.dwd_recsys_alg_sample_all_20250212 WHERE dt = '20260517' AND abcode IN ("ab0","ab1","ab2","ab3","ab4","ab5","ab6","ab7","ab8","ab9") AND abcode NOT IN ("ab100") AND extend_alg IS NOT NULL AND GET_JSON_OBJECT(extend_alg,'$.scoresMap') IS NOT NULL ) ,t_base AS ( SELECT dt ,CASE WHEN apptype IN ("0") THEN "0" WHEN apptype IN ("4") THEN "4" ELSE "其他" END AS apptype ,page_type ,page ,mid ,vid ,share_cnt ,is_return_noself ,return_n_uv ,return_n_uv_noself ,CAST(GET_JSON_OBJECT(scoresmap,'$.fmRov') AS DOUBLE) AS a_str_pred ,1.22 * pow(CAST(GET_JSON_OBJECT(scoresmap,'$.NorXGBScore') AS DOUBLE), 1.15) AS bn_ros_pred ,GET_JSON_OBJECT(v1_feature,'$.title') AS vid_title ,CAST(GET_JSON_OBJECT(extend_alg,'$.sortScore') AS DOUBLE) AS sortScore ,CAST(GET_JSON_OBJECT(extend_alg,'$.rovScore') AS DOUBLE) AS rovScore ,GET_JSON_OBJECT(extend_alg,'$.pushFrom') AS pushFrom ,CAST(GET_JSON_OBJECT(extend_alg,'$.scoreRos') AS DOUBLE) AS scoreRos ,CAST(GET_JSON_OBJECT(extend_alg,'$.scoreStr') AS DOUBLE) AS scoreStr ,CAST(GET_JSON_OBJECT(extend_alg,'$.pushfromrank') AS BIGINT) AS pushfromrank ,CAST(GET_JSON_OBJECT(b1_feature,'$.exp_168h') AS DOUBLE) AS b1_exp_168h ,CAST(GET_JSON_OBJECT(b1_feature,'$.return_1_uv_1h') AS DOUBLE) / NULLIF(CAST(GET_JSON_OBJECT(b1_feature,'$.exp_1h') AS DOUBLE), 0) AS b1_rovn_1h ,CAST(GET_JSON_OBJECT(b1_feature,'$.return_1_uv_24h') AS DOUBLE) / NULLIF(CAST(GET_JSON_OBJECT(b1_feature,'$.exp_24h') AS DOUBLE), 0) AS b1_rovn_24h ,CAST(GET_JSON_OBJECT(b1_feature,'$.rovn_168h') AS DOUBLE) AS b1_rovn_168h ,CAST(GET_JSON_OBJECT(b1_feature,'$.is_share_168h') AS DOUBLE) / NULLIF(CAST(GET_JSON_OBJECT(b1_feature,'$.exp_168h') AS DOUBLE), 0) AS b1_str_168h ,CAST(GET_JSON_OBJECT(b1_feature,'$.return_1_uv_72h') AS DOUBLE) / NULLIF(CAST(GET_JSON_OBJECT(b1_feature,'$.exp_72h') AS DOUBLE), 0) AS b1_rovn_72h ,CAST(GET_JSON_OBJECT(b1_feature,'$.is_share_24h') AS DOUBLE) / NULLIF(CAST(GET_JSON_OBJECT(b1_feature,'$.exp_24h') AS DOUBLE), 0) AS b1_str_24h ,CAST(GET_JSON_OBJECT(b1_feature,'$.str_one_1h') AS DOUBLE) AS b1_str_one_1h ,CAST(GET_JSON_OBJECT(b1_feature,'$.share_cnt_168h') AS DOUBLE) AS b1_share_cnt_168h ,CAST(GET_JSON_OBJECT(b2_feature,'$.return_n_uv_168h') AS DOUBLE) / NULLIF(CAST(GET_JSON_OBJECT(b2_feature,'$.exp_168h') AS DOUBLE), 0) AS b2_rovn_168h ,CAST(GET_JSON_OBJECT(b2_feature,'$.exp_168h') AS DOUBLE) AS b2_exp_168h ,CAST(GET_JSON_OBJECT(b2_feature,'$.return_n_uv_1h') AS DOUBLE) / NULLIF(CAST(GET_JSON_OBJECT(b2_feature,'$.exp_1h') AS DOUBLE), 0) AS b2_rovn_1h ,CAST(GET_JSON_OBJECT(b2_feature,'$.return_n_uv_24h') AS DOUBLE) / NULLIF(CAST(GET_JSON_OBJECT(b2_feature,'$.exp_24h') AS DOUBLE), 0) AS b2_rovn_24h ,CAST(GET_JSON_OBJECT(b2_feature,'$.is_share_168h') AS DOUBLE) / NULLIF(CAST(GET_JSON_OBJECT(b2_feature,'$.exp_168h') AS DOUBLE), 0) AS b2_str_168h ,CAST(GET_JSON_OBJECT(b2_feature,'$.new_exposure_cnt_168h') AS DOUBLE) AS b2_new_exp_168h ,CAST(GET_JSON_OBJECT(b3_feature,'$.return_n_uv_168h') AS DOUBLE) / NULLIF(CAST(GET_JSON_OBJECT(b3_feature,'$.exp_168h') AS DOUBLE), 0) AS b3_rovn_168h ,CAST(GET_JSON_OBJECT(b3_feature,'$.exp_168h') AS DOUBLE) AS b3_exp_168h ,CAST(GET_JSON_OBJECT(b12_feature,'$.return_n_uv_30d') AS DOUBLE) / NULLIF(CAST(GET_JSON_OBJECT(b12_feature,'$.exp_30d') AS DOUBLE), 0) AS b12_rovn_30d ,CAST(GET_JSON_OBJECT(b12_feature,'$.exp_30d') AS DOUBLE) AS b12_exp_30d ,CAST(GET_JSON_OBJECT(b12_feature,'$.return_n_uv_7d') AS DOUBLE) / NULLIF(CAST(GET_JSON_OBJECT(b12_feature,'$.exp_7d') AS DOUBLE), 0) AS b12_rovn_7d ,CAST(GET_JSON_OBJECT(b12_feature,'$.return_n_uv_60d') AS DOUBLE) / NULLIF(CAST(GET_JSON_OBJECT(b12_feature,'$.exp_60d') AS DOUBLE), 0) AS b12_rovn_60d ,CAST(GET_JSON_OBJECT(b12_feature,'$.is_share_30d') AS DOUBLE) / NULLIF(CAST(GET_JSON_OBJECT(b12_feature,'$.exp_30d') AS DOUBLE), 0) AS b12_str_30d ,CAST(GET_JSON_OBJECT(b12_feature,'$.exp_60d') AS DOUBLE) AS b12_exp_60d ,CAST(GET_JSON_OBJECT(c1_feature,'$.rovn_168h') AS DOUBLE) AS c1_rovn_168h ,CAST(GET_JSON_OBJECT(c1_feature,'$.exp_168h') AS DOUBLE) AS c1_exp_168h ,CAST(GET_JSON_OBJECT(c1_feature,'$.is_share_168h') AS DOUBLE) / NULLIF(CAST(GET_JSON_OBJECT(c1_feature,'$.exp_168h') AS DOUBLE), 0) AS c1_str_168h ,CAST(GET_JSON_OBJECT(c1_feature,'$.return_1_uv_24h') AS DOUBLE) / NULLIF(CAST(GET_JSON_OBJECT(c1_feature,'$.exp_24h') AS DOUBLE), 0) AS c1_rovn_24h ,CAST(GET_JSON_OBJECT(c1_feature,'$.click_168h') AS DOUBLE) AS c1_click_168h ,CAST(GET_JSON_OBJECT(b8_feature,'$.return_n_uv_168h') AS DOUBLE) / NULLIF(CAST(GET_JSON_OBJECT(b8_feature,'$.exp_168h') AS DOUBLE), 0) AS b8_rovn_168h ,CAST(GET_JSON_OBJECT(b8_feature,'$.exp_168h') AS DOUBLE) AS b8_exp_168h ,CAST(GET_JSON_OBJECT(b9_feature,'$.return_n_uv_168h') AS DOUBLE) / NULLIF(CAST(GET_JSON_OBJECT(b9_feature,'$.exp_168h') AS DOUBLE), 0) AS b9_rovn_168h ,CAST(GET_JSON_OBJECT(b9_feature,'$.exp_168h') AS DOUBLE) AS b9_exp_168h ,CAST(GET_JSON_OBJECT(b10_feature,'$.return_n_uv_168h') AS DOUBLE) / NULLIF(CAST(GET_JSON_OBJECT(b10_feature,'$.exp_168h') AS DOUBLE), 0) AS b10_rovn_168h ,CAST(GET_JSON_OBJECT(b10_feature,'$.exp_168h') AS DOUBLE) AS b10_exp_168h ,CAST(GET_JSON_OBJECT(d3_feature,'$.rovn') AS DOUBLE) AS d3_rovn ,CAST(GET_JSON_OBJECT(d3_feature,'$.exp') AS DOUBLE) AS d3_exp ,GET_JSON_OBJECT(v1_feature,'$.channel') AS channel ,GET_JSON_OBJECT(v1_feature,'$.merge_first_level_cate') AS merge_cate1 ,CAST(GET_JSON_OBJECT(v1_feature,'$.total_time') AS DOUBLE) AS total_time ,GET_JSON_OBJECT(v1_feature,'$.merge_second_level_cate') AS merge_cate2 ,GET_JSON_OBJECT(v1_feature,'$.vid_source') AS vid_source ,GET_JSON_OBJECT(v1_feature,'$.is_fes') AS is_fes ,GET_JSON_OBJECT(v1_feature,'$.attribute_province') AS attribute_province ,CAST(GET_JSON_OBJECT(extend, '$.extParams.sequence') AS BIGINT) AS seq_position FROM t_raw ) ,t_valid AS ( SELECT * FROM t_base WHERE a_str_pred IS NOT NULL AND bn_ros_pred IS NOT NULL ) ,t_pushfrom_ranked AS ( SELECT dt ,COALESCE(apptype, 'all') AS apptype ,COALESCE(page_type, 'all') AS page_type ,COALESCE(page, 'all') AS page ,vid ,pushFrom ,COUNT(1) AS pf_cnt ,ROW_NUMBER() OVER ( PARTITION BY dt, COALESCE(apptype,'all'), COALESCE(page_type,'all'), COALESCE(page,'all'), vid ORDER BY COUNT(1) DESC ) AS pf_rank FROM t_valid GROUP BY dt, apptype, page_type, page, vid, pushFrom GROUPING SETS ( (dt, apptype, page_type, page, vid, pushFrom), (dt, apptype, page_type, vid, pushFrom), (dt, apptype, page, vid, pushFrom), (dt, page_type, page, vid, pushFrom), (dt, apptype, vid, pushFrom), (dt, page_type, vid, pushFrom), (dt, page, vid, pushFrom), (dt, vid, pushFrom) ) ) ,t_pushfrom_mode AS ( SELECT dt, apptype, page_type, page, vid, pushFrom AS top_pushFrom FROM t_pushfrom_ranked WHERE pf_rank = 1 ) ,t_vid_agg_base AS ( SELECT dt ,COALESCE(apptype, 'all') AS apptype ,COALESCE(page_type, 'all') AS page_type ,COALESCE(page, 'all') AS page ,vid ,MAX(vid_title) AS vid_title ,COUNT(1) AS exp_cnt ,COALESCE(SUM(share_cnt),0) AS share_cnt ,COALESCE(SUM(return_n_uv),0) AS return_cnt ,SUM(is_return_noself) AS is_return_noself_cnt ,SUM(return_n_uv_noself) AS return_noself_sum ,SUM(a_str_pred) AS a_strx_pred_sum ,SUM(CASE WHEN is_return_noself = 1 THEN bn_ros_pred END) AS bn_rosx_pred_wsum ,SUM(CASE WHEN is_return_noself = 1 THEN 1 END) AS bn_rosx_pred_cnt ,SUM(a_str_pred * bn_ros_pred) AS bn_rovx_pred_sum ,round(COALESCE(SUM(share_cnt) / COUNT(1),0),6) AS a_str ,round(COALESCE(SUM(return_n_uv) / SUM(share_cnt),0),6) AS bn_ros ,round(COALESCE(SUM(return_n_uv) / COUNT(1),0),6) AS bn_rov ,round(COALESCE(SUM(is_return_noself) / COUNT(1),0),6) AS a_strx ,round(COALESCE(SUM(return_n_uv_noself) / NULLIF(SUM(is_return_noself),0),0),6) AS bn_rosx ,round(COALESCE(SUM(return_n_uv_noself) / COUNT(1),0),6) AS bn_rovx ,round(AVG(a_str_pred),6) AS a_strx_pred ,round(AVG(CASE WHEN is_return_noself = 1 THEN bn_ros_pred END),6) AS bn_rosx_pred ,round(AVG(a_str_pred * bn_ros_pred),6) AS bn_rovx_pred ,round((SUM(is_return_noself) / COUNT(1)) / NULLIF(SUM(a_str_pred) / COUNT(1), 0), 4) AS a_strx_copc ,round(AVG(CASE WHEN is_return_noself = 1 THEN return_n_uv_noself END) / NULLIF(AVG(CASE WHEN is_return_noself = 1 THEN bn_ros_pred END), 0), 4) AS bn_rosx_copc ,round((SUM(return_n_uv_noself) / COUNT(1)) / NULLIF(AVG(a_str_pred * bn_ros_pred), 0), 4) AS bn_rovx_copc ,round(AVG(sortScore),4) AS avg_sortScore ,round(AVG(rovScore),4) AS avg_rovScore ,SUM(sortScore) AS sortScore_sum ,SUM(rovScore) AS rovScore_sum ,round(AVG(scoreRos),4) AS avg_scoreRos ,SUM(scoreRos) AS scoreRos_sum ,round(AVG(scoreStr),4) AS avg_scoreStr ,SUM(scoreStr) AS scoreStr_sum ,round(AVG(pushfromrank),1) AS avg_pushfromrank ,SUM(pushfromrank) AS pushfromrank_sum ,round(AVG(b1_exp_168h),0) AS b1_exp_168h ,SUM(b1_exp_168h) AS b1_exp_168h_sum ,round(AVG(b1_rovn_1h),6) AS b1_rovn_1h ,SUM(b1_rovn_1h) AS b1_rovn_1h_sum ,round(AVG(b1_rovn_24h),6) AS b1_rovn_24h ,SUM(b1_rovn_24h) AS b1_rovn_24h_sum ,round(AVG(b1_rovn_168h),6) AS b1_rovn_168h ,SUM(b1_rovn_168h) AS b1_rovn_168h_sum ,round(AVG(b1_str_168h),6) AS b1_str_168h ,SUM(b1_str_168h) AS b1_str_168h_sum ,round(AVG(b1_rovn_72h),6) AS b1_rovn_72h ,SUM(b1_rovn_72h) AS b1_rovn_72h_sum ,round(AVG(b1_str_24h),6) AS b1_str_24h ,SUM(b1_str_24h) AS b1_str_24h_sum ,round(AVG(b1_str_one_1h),6) AS b1_str_one_1h ,SUM(b1_str_one_1h) AS b1_str_one_1h_sum ,round(AVG(b1_share_cnt_168h),0) AS b1_share_cnt_168h ,SUM(b1_share_cnt_168h) AS b1_share_cnt_168h_sum ,round(AVG(b2_rovn_168h),6) AS b2_rovn_168h ,SUM(b2_rovn_168h) AS b2_rovn_168h_sum ,round(AVG(b2_exp_168h),0) AS b2_exp_168h ,SUM(b2_exp_168h) AS b2_exp_168h_sum ,round(AVG(b2_rovn_1h),6) AS b2_rovn_1h ,SUM(b2_rovn_1h) AS b2_rovn_1h_sum ,round(AVG(b2_rovn_24h),6) AS b2_rovn_24h ,SUM(b2_rovn_24h) AS b2_rovn_24h_sum ,round(AVG(b2_str_168h),6) AS b2_str_168h ,SUM(b2_str_168h) AS b2_str_168h_sum ,round(AVG(b2_new_exp_168h),0) AS b2_new_exp_168h ,SUM(b2_new_exp_168h) AS b2_new_exp_168h_sum ,round(AVG(b3_rovn_168h),6) AS b3_rovn_168h ,SUM(b3_rovn_168h) AS b3_rovn_168h_sum ,round(AVG(b3_exp_168h),0) AS b3_exp_168h ,SUM(b3_exp_168h) AS b3_exp_168h_sum ,round(AVG(b12_rovn_30d),6) AS b12_rovn_30d ,SUM(b12_rovn_30d) AS b12_rovn_30d_sum ,round(AVG(b12_exp_30d),0) AS b12_exp_30d ,SUM(b12_exp_30d) AS b12_exp_30d_sum ,round(AVG(b12_rovn_7d),6) AS b12_rovn_7d ,SUM(b12_rovn_7d) AS b12_rovn_7d_sum ,round(AVG(b12_rovn_60d),6) AS b12_rovn_60d ,SUM(b12_rovn_60d) AS b12_rovn_60d_sum ,round(AVG(b12_str_30d),6) AS b12_str_30d ,SUM(b12_str_30d) AS b12_str_30d_sum ,round(AVG(b12_exp_60d),0) AS b12_exp_60d ,SUM(b12_exp_60d) AS b12_exp_60d_sum ,round(AVG(c1_rovn_168h),6) AS avg_c1_rovn_168h ,SUM(c1_rovn_168h) AS c1_rovn_168h_sum ,round(AVG(c1_exp_168h),0) AS avg_c1_exp_168h ,SUM(c1_exp_168h) AS c1_exp_168h_sum ,round(AVG(c1_str_168h),6) AS avg_c1_str_168h ,SUM(c1_str_168h) AS c1_str_168h_sum ,round(AVG(c1_rovn_24h),6) AS avg_c1_rovn_24h ,SUM(c1_rovn_24h) AS c1_rovn_24h_sum ,round(AVG(c1_click_168h),0) AS avg_c1_click_168h ,SUM(c1_click_168h) AS c1_click_168h_sum ,round(AVG(b8_rovn_168h),6) AS b8_rovn_168h ,SUM(b8_rovn_168h) AS b8_rovn_168h_sum ,round(AVG(b8_exp_168h),0) AS b8_exp_168h ,SUM(b8_exp_168h) AS b8_exp_168h_sum ,round(AVG(b9_rovn_168h),6) AS b9_rovn_168h ,SUM(b9_rovn_168h) AS b9_rovn_168h_sum ,round(AVG(b9_exp_168h),0) AS b9_exp_168h ,SUM(b9_exp_168h) AS b9_exp_168h_sum ,round(AVG(b10_rovn_168h),6) AS b10_rovn_168h ,SUM(b10_rovn_168h) AS b10_rovn_168h_sum ,round(AVG(b10_exp_168h),0) AS b10_exp_168h ,SUM(b10_exp_168h) AS b10_exp_168h_sum ,round(AVG(d3_rovn),6) AS d3_rovn ,SUM(d3_rovn) AS d3_rovn_sum ,round(AVG(d3_exp),0) AS d3_exp ,SUM(d3_exp) AS d3_exp_sum ,MAX(channel) AS channel ,MAX(merge_cate1) AS merge_cate1 ,round(AVG(total_time),0) AS total_time ,SUM(total_time) AS total_time_sum ,MAX(merge_cate2) AS merge_cate2 ,MAX(vid_source) AS vid_source ,MAX(is_fes) AS is_fes ,MAX(attribute_province) AS attribute_province ,round(AVG(seq_position),1) AS avg_seq_position ,SUM(seq_position) AS seq_position_sum FROM t_valid GROUP BY dt, apptype, page_type, page, vid GROUPING SETS ( (dt, apptype, page_type, page, vid), (dt, apptype, page_type, vid), (dt, apptype, page, vid), (dt, page_type, page, vid), (dt, apptype, vid), (dt, page_type, vid), (dt, page, vid), (dt, vid) ) ) ,t_vid_agg AS ( SELECT a.* ,b.top_pushFrom FROM t_vid_agg_base a LEFT JOIN t_pushfrom_mode b ON a.dt = b.dt AND a.apptype = b.apptype AND a.page_type = b.page_type AND a.page = b.page AND a.vid = b.vid ) ,t_ranked AS ( SELECT * ,ROW_NUMBER() OVER (PARTITION BY dt, apptype, page_type, page ORDER BY exp_cnt DESC) AS exp_rank FROM t_vid_agg ) ,t_top20 AS ( SELECT * ,ROW_NUMBER() OVER (PARTITION BY dt, apptype, page_type, page ORDER BY bn_rov DESC) AS bn_rovn_rank ,ROW_NUMBER() OVER (PARTITION BY dt, apptype, page_type, page ORDER BY avg_seq_position ASC) AS seq_rank ,ROW_NUMBER() OVER (PARTITION BY dt, apptype, page_type, page ORDER BY avg_sortScore DESC) AS score_rank FROM t_ranked WHERE exp_rank <= 20 ) ,t_other_summary AS ( SELECT dt, apptype, page_type, page ,'其他' AS vid ,CAST(NULL AS STRING) AS vid_title ,CAST(NULL AS BIGINT) AS exp_rank ,CAST(NULL AS BIGINT) AS bn_rovn_rank ,CAST(NULL AS BIGINT) AS seq_rank ,CAST(NULL AS BIGINT) AS score_rank ,SUM(exp_cnt) AS exp_cnt ,SUM(share_cnt) AS share_cnt ,SUM(return_cnt) AS return_cnt ,round(SUM(share_cnt) * 1.0 / NULLIF(SUM(exp_cnt), 0), 6) AS a_str ,round(SUM(return_cnt) * 1.0 / NULLIF(SUM(share_cnt), 0), 6) AS bn_ros ,round(SUM(return_cnt) * 1.0 / NULLIF(SUM(exp_cnt), 0), 6) AS bn_rov ,round(SUM(is_return_noself_cnt) * 1.0 / NULLIF(SUM(exp_cnt), 0), 6) AS a_strx ,round(SUM(return_noself_sum) * 1.0 / NULLIF(SUM(is_return_noself_cnt), 0), 6) AS bn_rosx ,round(SUM(return_noself_sum) * 1.0 / NULLIF(SUM(exp_cnt), 0), 6) AS bn_rovx ,round(SUM(a_strx_pred_sum) / NULLIF(SUM(exp_cnt), 0), 6) AS a_strx_pred ,round(SUM(bn_rosx_pred_wsum) / NULLIF(SUM(bn_rosx_pred_cnt), 0), 6) AS bn_rosx_pred ,round(SUM(bn_rovx_pred_sum) / NULLIF(SUM(exp_cnt), 0), 6) AS bn_rovx_pred ,round(SUM(is_return_noself_cnt) * 1.0 / NULLIF(SUM(a_strx_pred_sum), 0), 4) AS a_strx_copc ,round((SUM(return_noself_sum) * 1.0 / NULLIF(SUM(is_return_noself_cnt), 0)) / NULLIF(SUM(bn_rosx_pred_wsum) / NULLIF(SUM(bn_rosx_pred_cnt), 0), 0), 4) AS bn_rosx_copc ,round(SUM(return_noself_sum) * 1.0 / NULLIF(SUM(bn_rovx_pred_sum), 0), 4) AS bn_rovx_copc ,round(SUM(sortScore_sum) / NULLIF(SUM(exp_cnt), 0), 4) AS avg_sortScore ,round(SUM(rovScore_sum) / NULLIF(SUM(exp_cnt), 0), 4) AS avg_rovScore ,CAST(NULL AS STRING) AS top_pushFrom ,round(SUM(seq_position_sum) / NULLIF(SUM(exp_cnt), 0), 1) AS avg_seq_position ,round(SUM(scoreRos_sum) / NULLIF(SUM(exp_cnt), 0), 4) AS avg_scoreRos ,round(SUM(scoreStr_sum) / NULLIF(SUM(exp_cnt), 0), 4) AS avg_scoreStr ,round(SUM(pushfromrank_sum) / NULLIF(SUM(exp_cnt), 0), 1) AS avg_pushfromrank ,round(SUM(b1_exp_168h_sum) / NULLIF(SUM(exp_cnt), 0), 0) AS b1_exp_168h ,round(SUM(b1_rovn_1h_sum) / NULLIF(SUM(exp_cnt), 0), 6) AS b1_rovn_1h ,round(SUM(b1_rovn_24h_sum) / NULLIF(SUM(exp_cnt), 0), 6) AS b1_rovn_24h ,round(SUM(b1_rovn_168h_sum) / NULLIF(SUM(exp_cnt), 0), 6) AS b1_rovn_168h ,round(SUM(b1_str_168h_sum) / NULLIF(SUM(exp_cnt), 0), 6) AS b1_str_168h ,round(SUM(b1_rovn_72h_sum) / NULLIF(SUM(exp_cnt), 0), 6) AS b1_rovn_72h ,round(SUM(b1_str_24h_sum) / NULLIF(SUM(exp_cnt), 0), 6) AS b1_str_24h ,round(SUM(b1_str_one_1h_sum) / NULLIF(SUM(exp_cnt), 0), 6) AS b1_str_one_1h ,round(SUM(b1_share_cnt_168h_sum) / NULLIF(SUM(exp_cnt), 0), 0) AS b1_share_cnt_168h ,round(SUM(b2_rovn_168h_sum) / NULLIF(SUM(exp_cnt), 0), 6) AS b2_rovn_168h ,round(SUM(b2_exp_168h_sum) / NULLIF(SUM(exp_cnt), 0), 0) AS b2_exp_168h ,round(SUM(b2_rovn_1h_sum) / NULLIF(SUM(exp_cnt), 0), 6) AS b2_rovn_1h ,round(SUM(b2_rovn_24h_sum) / NULLIF(SUM(exp_cnt), 0), 6) AS b2_rovn_24h ,round(SUM(b2_str_168h_sum) / NULLIF(SUM(exp_cnt), 0), 6) AS b2_str_168h ,round(SUM(b2_new_exp_168h_sum) / NULLIF(SUM(exp_cnt), 0), 0) AS b2_new_exp_168h ,round(SUM(b3_rovn_168h_sum) / NULLIF(SUM(exp_cnt), 0), 6) AS b3_rovn_168h ,round(SUM(b3_exp_168h_sum) / NULLIF(SUM(exp_cnt), 0), 0) AS b3_exp_168h ,round(SUM(b12_rovn_30d_sum) / NULLIF(SUM(exp_cnt), 0), 6) AS b12_rovn_30d ,round(SUM(b12_exp_30d_sum) / NULLIF(SUM(exp_cnt), 0), 0) AS b12_exp_30d ,round(SUM(b12_rovn_7d_sum) / NULLIF(SUM(exp_cnt), 0), 6) AS b12_rovn_7d ,round(SUM(b12_rovn_60d_sum) / NULLIF(SUM(exp_cnt), 0), 6) AS b12_rovn_60d ,round(SUM(b12_str_30d_sum) / NULLIF(SUM(exp_cnt), 0), 6) AS b12_str_30d ,round(SUM(b12_exp_60d_sum) / NULLIF(SUM(exp_cnt), 0), 0) AS b12_exp_60d ,round(SUM(c1_rovn_168h_sum) / NULLIF(SUM(exp_cnt), 0), 6) AS avg_c1_rovn_168h ,round(SUM(c1_exp_168h_sum) / NULLIF(SUM(exp_cnt), 0), 0) AS avg_c1_exp_168h ,round(SUM(c1_str_168h_sum) / NULLIF(SUM(exp_cnt), 0), 6) AS avg_c1_str_168h ,round(SUM(c1_rovn_24h_sum) / NULLIF(SUM(exp_cnt), 0), 6) AS avg_c1_rovn_24h ,round(SUM(c1_click_168h_sum) / NULLIF(SUM(exp_cnt), 0), 0) AS avg_c1_click_168h ,round(SUM(b8_rovn_168h_sum) / NULLIF(SUM(exp_cnt), 0), 6) AS b8_rovn_168h ,round(SUM(b8_exp_168h_sum) / NULLIF(SUM(exp_cnt), 0), 0) AS b8_exp_168h ,round(SUM(b9_rovn_168h_sum) / NULLIF(SUM(exp_cnt), 0), 6) AS b9_rovn_168h ,round(SUM(b9_exp_168h_sum) / NULLIF(SUM(exp_cnt), 0), 0) AS b9_exp_168h ,round(SUM(b10_rovn_168h_sum) / NULLIF(SUM(exp_cnt), 0), 6) AS b10_rovn_168h ,round(SUM(b10_exp_168h_sum) / NULLIF(SUM(exp_cnt), 0), 0) AS b10_exp_168h ,round(SUM(d3_rovn_sum) / NULLIF(SUM(exp_cnt), 0), 6) AS d3_rovn ,round(SUM(d3_exp_sum) / NULLIF(SUM(exp_cnt), 0), 0) AS d3_exp ,CAST(NULL AS STRING) AS channel ,CAST(NULL AS STRING) AS merge_cate1 ,round(SUM(total_time_sum) / NULLIF(SUM(exp_cnt), 0), 0) AS total_time ,CAST(NULL AS STRING) AS merge_cate2 ,CAST(NULL AS STRING) AS vid_source ,CAST(NULL AS STRING) AS is_fes ,CAST(NULL AS STRING) AS attribute_province FROM t_ranked WHERE exp_rank > 20 GROUP BY dt, apptype, page_type, page ) ,t_all_summary AS ( SELECT dt ,COALESCE(apptype, 'all') AS apptype ,COALESCE(page_type, 'all') AS page_type ,COALESCE(page, 'all') AS page ,'all' AS vid ,CAST(NULL AS STRING) AS vid_title ,CAST(NULL AS BIGINT) AS exp_rank ,CAST(NULL AS BIGINT) AS bn_rovn_rank ,CAST(NULL AS BIGINT) AS seq_rank ,CAST(NULL AS BIGINT) AS score_rank ,COUNT(1) AS exp_cnt ,COALESCE(SUM(share_cnt),0) AS share_cnt ,COALESCE(SUM(return_n_uv),0) AS return_cnt ,round(COALESCE(SUM(share_cnt) / COUNT(1),0),6) AS a_str ,round(COALESCE(SUM(return_n_uv) / SUM(share_cnt),0),6) AS bn_ros ,round(COALESCE(SUM(return_n_uv) / COUNT(1),0),6) AS bn_rov ,round(COALESCE(SUM(is_return_noself) / COUNT(1),0),6) AS a_strx ,round(COALESCE(SUM(return_n_uv_noself) / NULLIF(SUM(is_return_noself),0),0),6) AS bn_rosx ,round(COALESCE(SUM(return_n_uv_noself) / COUNT(1),0),6) AS bn_rovx ,round(AVG(a_str_pred),6) AS a_strx_pred ,round(AVG(CASE WHEN is_return_noself = 1 THEN bn_ros_pred END),6) AS bn_rosx_pred ,round(AVG(a_str_pred * bn_ros_pred),6) AS bn_rovx_pred ,round((SUM(is_return_noself) / COUNT(1)) / NULLIF(SUM(a_str_pred) / COUNT(1), 0), 4) AS a_strx_copc ,round(AVG(CASE WHEN is_return_noself = 1 THEN return_n_uv_noself END) / NULLIF(AVG(CASE WHEN is_return_noself = 1 THEN bn_ros_pred END), 0), 4) AS bn_rosx_copc ,round((SUM(return_n_uv_noself) / COUNT(1)) / NULLIF(AVG(a_str_pred * bn_ros_pred), 0), 4) AS bn_rovx_copc ,round(AVG(sortScore),4) AS avg_sortScore ,round(AVG(rovScore),4) AS avg_rovScore ,CAST(NULL AS STRING) AS top_pushFrom ,round(AVG(seq_position),1) AS avg_seq_position ,round(AVG(scoreRos),4) AS avg_scoreRos ,round(AVG(scoreStr),4) AS avg_scoreStr ,round(AVG(pushfromrank),1) AS avg_pushfromrank ,round(AVG(b1_exp_168h),0) AS b1_exp_168h ,round(AVG(b1_rovn_1h),6) AS b1_rovn_1h ,round(AVG(b1_rovn_24h),6) AS b1_rovn_24h ,round(AVG(b1_rovn_168h),6) AS b1_rovn_168h ,round(AVG(b1_str_168h),6) AS b1_str_168h ,round(AVG(b1_rovn_72h),6) AS b1_rovn_72h ,round(AVG(b1_str_24h),6) AS b1_str_24h ,round(AVG(b1_str_one_1h),6) AS b1_str_one_1h ,round(AVG(b1_share_cnt_168h),0) AS b1_share_cnt_168h ,round(AVG(b2_rovn_168h),6) AS b2_rovn_168h ,round(AVG(b2_exp_168h),0) AS b2_exp_168h ,round(AVG(b2_rovn_1h),6) AS b2_rovn_1h ,round(AVG(b2_rovn_24h),6) AS b2_rovn_24h ,round(AVG(b2_str_168h),6) AS b2_str_168h ,round(AVG(b2_new_exp_168h),0) AS b2_new_exp_168h ,round(AVG(b3_rovn_168h),6) AS b3_rovn_168h ,round(AVG(b3_exp_168h),0) AS b3_exp_168h ,round(AVG(b12_rovn_30d),6) AS b12_rovn_30d ,round(AVG(b12_exp_30d),0) AS b12_exp_30d ,round(AVG(b12_rovn_7d),6) AS b12_rovn_7d ,round(AVG(b12_rovn_60d),6) AS b12_rovn_60d ,round(AVG(b12_str_30d),6) AS b12_str_30d ,round(AVG(b12_exp_60d),0) AS b12_exp_60d ,round(AVG(c1_rovn_168h),6) AS avg_c1_rovn_168h ,round(AVG(c1_exp_168h),0) AS avg_c1_exp_168h ,round(AVG(c1_str_168h),6) AS avg_c1_str_168h ,round(AVG(c1_rovn_24h),6) AS avg_c1_rovn_24h ,round(AVG(c1_click_168h),0) AS avg_c1_click_168h ,round(AVG(b8_rovn_168h),6) AS b8_rovn_168h ,round(AVG(b8_exp_168h),0) AS b8_exp_168h ,round(AVG(b9_rovn_168h),6) AS b9_rovn_168h ,round(AVG(b9_exp_168h),0) AS b9_exp_168h ,round(AVG(b10_rovn_168h),6) AS b10_rovn_168h ,round(AVG(b10_exp_168h),0) AS b10_exp_168h ,round(AVG(d3_rovn),6) AS d3_rovn ,round(AVG(d3_exp),0) AS d3_exp ,CAST(NULL AS STRING) AS channel ,CAST(NULL AS STRING) AS merge_cate1 ,round(AVG(total_time),0) AS total_time ,CAST(NULL AS STRING) AS merge_cate2 ,CAST(NULL AS STRING) AS vid_source ,CAST(NULL AS STRING) AS is_fes ,CAST(NULL AS STRING) AS attribute_province FROM t_valid GROUP BY dt, apptype, page_type, page GROUPING SETS ( (dt, apptype, page_type, page), (dt, apptype, page_type), (dt, apptype, page), (dt, page_type, page), (dt, apptype), (dt, page_type), (dt, page), (dt) ) ) SELECT dt ,apptype ,page_type ,page ,vid ,vid_title ,exp_rank ,bn_rovn_rank ,seq_rank ,score_rank ,round(exp_cnt * 1.0 / MAX(CASE WHEN vid = 'all' THEN exp_cnt END) OVER (PARTITION BY dt, apptype, page_type, page), 4) AS exp_pct ,exp_cnt, share_cnt, return_cnt ,a_str, bn_ros, bn_rov ,a_strx, bn_rosx, bn_rovx ,a_strx_pred, bn_rosx_pred, bn_rovx_pred ,a_strx_copc, bn_rosx_copc, bn_rovx_copc ,avg_sortScore, avg_rovScore ,top_pushFrom ,avg_seq_position ,avg_scoreRos, avg_scoreStr, avg_pushfromrank ,b1_exp_168h, b1_rovn_1h, b1_rovn_24h, b1_rovn_168h, b1_str_168h ,b1_rovn_72h, b1_str_24h, b1_str_one_1h, b1_share_cnt_168h ,b2_rovn_168h, b2_exp_168h ,b2_rovn_1h, b2_rovn_24h, b2_str_168h, b2_new_exp_168h ,b3_rovn_168h, b3_exp_168h ,b12_rovn_30d, b12_exp_30d ,b12_rovn_7d, b12_rovn_60d, b12_str_30d, b12_exp_60d ,avg_c1_rovn_168h, avg_c1_exp_168h ,avg_c1_str_168h, avg_c1_rovn_24h, avg_c1_click_168h ,b8_rovn_168h ,b8_exp_168h ,b9_rovn_168h, b9_exp_168h ,b10_rovn_168h, b10_exp_168h ,d3_rovn, d3_exp ,channel, merge_cate1, total_time ,merge_cate2, vid_source, is_fes, attribute_province FROM ( SELECT dt,apptype,page_type,page,vid,vid_title,exp_rank,bn_rovn_rank,seq_rank,score_rank ,exp_cnt,share_cnt,return_cnt ,a_str,bn_ros,bn_rov,a_strx,bn_rosx,bn_rovx ,a_strx_pred,bn_rosx_pred,bn_rovx_pred ,a_strx_copc,bn_rosx_copc,bn_rovx_copc ,avg_sortScore,avg_rovScore,top_pushFrom ,avg_seq_position ,avg_scoreRos,avg_scoreStr,avg_pushfromrank ,b1_exp_168h,b1_rovn_1h,b1_rovn_24h,b1_rovn_168h,b1_str_168h ,b1_rovn_72h,b1_str_24h,b1_str_one_1h,b1_share_cnt_168h ,b2_rovn_168h,b2_exp_168h ,b2_rovn_1h,b2_rovn_24h,b2_str_168h,b2_new_exp_168h ,b3_rovn_168h,b3_exp_168h ,b12_rovn_30d,b12_exp_30d ,b12_rovn_7d,b12_rovn_60d,b12_str_30d,b12_exp_60d ,avg_c1_rovn_168h,avg_c1_exp_168h ,avg_c1_str_168h,avg_c1_rovn_24h,avg_c1_click_168h ,b8_rovn_168h ,b8_exp_168h ,b9_rovn_168h,b9_exp_168h ,b10_rovn_168h,b10_exp_168h ,d3_rovn,d3_exp ,channel,merge_cate1,total_time ,merge_cate2,vid_source,is_fes,attribute_province FROM t_top20 UNION ALL SELECT * FROM t_other_summary UNION ALL SELECT * FROM t_all_summary ) t ORDER BY dt DESC, apptype, page_type, page, exp_cnt DESC;