data_analysis/tasks/tmp/tmp.sql

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;