-- ===================================================================== -- 曝光回流链路 CUBE 聚合表 (基于 wide 宽表, 增加用户/品类维度 + 模型预估指标) -- 维度: user_type × hh_bucket × head_merge_leve2 × vid_merge_leve2 × vid_id (CUBE) -- 参考: de.sql + dwd_recsys_alg_exposure_agg_20260209 -- ===================================================================== -- -- DROP TABLE IF EXISTS loghubods.dwd_recsys_alg_exposure_agg_wide_20260209; -- CREATE TABLE IF NOT EXISTS loghubods.dwd_recsys_alg_exposure_agg_wide_20260209 ( -- -- ==================== 维度列 ==================== -- dt STRING COMMENT '日期' -- ,user_type STRING COMMENT '用户拉活量分层(R0&新用户/R1-50/R_180_330等，汇总为SUM)' -- ,hh_bucket STRING COMMENT '小时段(00-03/04-07/.../20-23，汇总为SUM)' -- ,head_merge_leve2 STRING COMMENT '进入内容品类(headvideoid品类，汇总为SUM)' -- ,vid_merge_leve2 STRING COMMENT '推荐内容品类(vid品类，TOP10曝光+其他，汇总为SUM)' -- ,vid_id STRING COMMENT '内容id(品类曝光TOP1+其他，汇总为SUM)' -- -- ==================== 基础流量 ==================== -- ,exposure_cnt BIGINT COMMENT '曝光次数' -- ,exposure_uv BIGINT COMMENT '曝光人数(mid去重)' -- ,vid_cnt BIGINT COMMENT '视频个数(vid去重)' -- ,exposure_per_user DOUBLE COMMENT '人均曝光次数 = 曝光次数/曝光人数' -- -- ==================== 分享 & 回流漏斗 ==================== -- ,share_exposure_cnt BIGINT COMMENT '产生分享的曝光数' -- ,share_cnt BIGINT COMMENT '分享总次数' -- ,return_exposure_cnt BIGINT COMMENT '产生回流的曝光数(含自身) = SUM(is_return_n)' -- ,return_exposure_cnt_noself BIGINT COMMENT '产生回流的曝光数(非自身) = SUM(is_return_noself)' -- ,return_uv BIGINT COMMENT '回流人数(含自身) = SUM(return_n_uv)' -- ,return_uv_noself BIGINT COMMENT '回流人数(非自身) = SUM(return_n_uv_noself)' -- ,share_rate DOUBLE COMMENT '分享率 = share_exposure_cnt/exposure_cnt' -- ,return_rate DOUBLE COMMENT '回流率(含自身) = return_exposure_cnt/exposure_cnt' -- ,return_rate_noself DOUBLE COMMENT '回流率(非自身) = return_exposure_cnt_noself/exposure_cnt' -- ,share_return_rate DOUBLE COMMENT '分享→回流转化率(非自身) = return_exposure_cnt_noself/share_exposure_cnt' -- -- ==================== 模型预估: STR (曝光→非自身回流概率) ==================== -- ,str_real DOUBLE COMMENT '= return_rate_noself, 模型label' -- ,str_pred DOUBLE COMMENT 'STR预估 = SUM(str_pred)/exposure_cnt' -- ,str_copc DOUBLE COMMENT 'STR copc = str_real/str_pred' -- ,str_mae DOUBLE COMMENT 'STR MAE = AVG(|str_pred - str_real|)' -- ,str_var DOUBLE COMMENT 'STR VAR = VARIANCE(str_pred - str_real)' -- -- ==================== 模型预估: ROSN (条件回流UV, 非自身) ==================== -- ,rosn_real DOUBLE COMMENT '= return_uv_noself/return_exposure_cnt_noself, 模型label' -- ,rosn_pred DOUBLE COMMENT 'ROSN预估 = SUM(rosn_pred WHERE is_return_noself=1)/SUM(is_return_noself)' -- ,rosn_copc DOUBLE COMMENT 'ROSN copc = rosn_real/rosn_pred' -- ,rosn_pred_origin DOUBLE COMMENT 'ROSN原始预估均值 = AVG(rosn_pred_origin)' -- ,rosn_mae DOUBLE COMMENT 'ROSN MAE = AVG(|rosn_pred - rosn_real|) WHERE is_return_noself=1' -- ,rosn_var DOUBLE COMMENT 'ROSN VAR = VARIANCE(rosn_pred - rosn_real) WHERE is_return_noself=1' -- -- ==================== 模型预估: ROVN (STR×ROSN) ==================== -- ,rovn_real DOUBLE COMMENT '= return_uv_noself/exposure_cnt, 模型label' -- ,rovn_pred DOUBLE COMMENT 'rovn预估 = AVG(str_pred*rosn_pred)' -- ,rovn_copc DOUBLE COMMENT 'rovn copc = rovn_real/rovn_pred' -- ,rovn_mae DOUBLE COMMENT 'rovn MAE = AVG(|rovn_pred - rovn_real|)' -- ,rovn_var DOUBLE COMMENT 'rovn VAR = VARIANCE(rovn_pred - rovn_real)' -- ,sortscore_avg DOUBLE COMMENT 'sortscore均值' -- -- ==================== B链 (分享→点击) ==================== -- ,bn_uv BIGINT COMMENT 'B链全量: 回流去重人数' -- ,bn_pv BIGINT COMMENT 'B链全量: 回流点击次数' -- ,bn_exp BIGINT COMMENT 'B链全量: 回流session曝光数' -- ,bn_ror DOUBLE COMMENT 'bn_uv/exposure_uv' -- ,bn_rov DOUBLE COMMENT 'bn_uv/exposure_cnt' -- ,b1_uv BIGINT COMMENT 'B链depth=1: 回流去重人数' -- ,b1_pv BIGINT COMMENT 'B链depth=1: 回流点击次数' -- ,b1_exp BIGINT COMMENT 'B链depth=1: 回流session曝光数' -- ,b1_ror DOUBLE COMMENT 'b1_uv/exposure_uv' -- ,b1_rov DOUBLE COMMENT 'b1_uv/exposure_cnt' -- ,b2_uv BIGINT COMMENT 'B链depth=2: 回流去重人数' -- ,b2_pv BIGINT COMMENT 'B链depth=2: 回流点击次数' -- ,b2_exp BIGINT COMMENT 'B链depth=2: 回流session曝光数' -- ,b2_ror DOUBLE COMMENT 'b2_uv/b1_uv' -- ,b2_rov DOUBLE COMMENT 'b2_uv/b1_exp' -- ,b3_uv BIGINT COMMENT 'B链depth=3: 回流去重人数' -- ,b3_pv BIGINT COMMENT 'B链depth=3: 回流点击次数' -- ,b3_exp BIGINT COMMENT 'B链depth=3: 回流session曝光数' -- ,b3_ror DOUBLE COMMENT 'b3_uv/b2_uv' -- ,b3_rov DOUBLE COMMENT 'b3_uv/b2_exp' -- -- ==================== C链 (全量depth, 按hop) ==================== -- ,cn_1_uv BIGINT COMMENT 'C链hop1: 回流去重人数' -- ,cn_1_pv BIGINT COMMENT 'C链hop1: 回流点击次数' -- ,cn_1_exp BIGINT COMMENT 'C链hop1: 回流session曝光数' -- ,cn_1_ror DOUBLE COMMENT 'cn_1_uv/bn_uv' -- ,cn_1_rov DOUBLE COMMENT 'cn_1_uv/bn_exp' -- ,cn_2_uv BIGINT COMMENT 'C链hop2: 回流去重人数' -- ,cn_2_pv BIGINT COMMENT 'C链hop2: 回流点击次数' -- ,cn_2_exp BIGINT COMMENT 'C链hop2: 回流session曝光数' -- ,cn_2_ror DOUBLE COMMENT 'cn_2_uv/cn_1_uv' -- ,cn_2_rov DOUBLE COMMENT 'cn_2_uv/cn_1_exp' -- ,cn_3_uv BIGINT COMMENT 'C链hop3: 回流去重人数' -- ,cn_3_pv BIGINT COMMENT 'C链hop3: 回流点击次数' -- ,cn_3_exp BIGINT COMMENT 'C链hop3: 回流session曝光数' -- ,cn_3_ror DOUBLE COMMENT 'cn_3_uv/cn_2_uv' -- ,cn_3_rov DOUBLE COMMENT 'cn_3_uv/cn_2_exp' -- ,cn_total_uv BIGINT COMMENT 'C链合计UV' -- ,cn_total_ror DOUBLE COMMENT 'cn_total_uv/bn_uv' -- ,cn_total_rov DOUBLE COMMENT 'cn_total_uv/bn_exp' -- -- C链 depth拆分 hop1 -- ,c1_1_uv BIGINT COMMENT 'C链d1-hop1: 回流去重人数' -- ,c1_1_pv BIGINT COMMENT 'C链d1-hop1: 回流点击次数' -- ,c1_1_exp BIGINT COMMENT 'C链d1-hop1: 回流session曝光数' -- ,c1_1_ror DOUBLE COMMENT 'c1_1_uv/bn_uv' -- ,c1_1_rov DOUBLE COMMENT 'c1_1_uv/bn_exp' -- ,c2_1_uv BIGINT COMMENT 'C链d2-hop1: 回流去重人数' -- ,c2_1_pv BIGINT COMMENT 'C链d2-hop1: 回流点击次数' -- ,c2_1_exp BIGINT COMMENT 'C链d2-hop1: 回流session曝光数' -- ,c2_1_ror DOUBLE COMMENT 'c2_1_uv/c1_1_uv' -- ,c2_1_rov DOUBLE COMMENT 'c2_1_uv/c1_1_exp' -- ,c3_1_uv BIGINT COMMENT 'C链d3-hop1: 回流去重人数' -- ,c3_1_pv BIGINT COMMENT 'C链d3-hop1: 回流点击次数' -- ,c3_1_exp BIGINT COMMENT 'C链d3-hop1: 回流session曝光数' -- ,c3_1_ror DOUBLE COMMENT 'c3_1_uv/c2_1_uv' -- ,c3_1_rov DOUBLE COMMENT 'c3_1_uv/c2_1_exp' -- -- C链 depth拆分 hop2 -- ,c1_2_uv BIGINT COMMENT 'C链d1-hop2: 回流去重人数' -- ,c1_2_pv BIGINT COMMENT 'C链d1-hop2: 回流点击次数' -- ,c1_2_exp BIGINT COMMENT 'C链d1-hop2: 回流session曝光数' -- ,c1_2_ror DOUBLE COMMENT 'c1_2_uv/cn_1_uv' -- ,c1_2_rov DOUBLE COMMENT 'c1_2_uv/cn_1_exp' -- ,c2_2_uv BIGINT COMMENT 'C链d2-hop2: 回流去重人数' -- ,c2_2_pv BIGINT COMMENT 'C链d2-hop2: 回流点击次数' -- ,c2_2_exp BIGINT COMMENT 'C链d2-hop2: 回流session曝光数' -- ,c2_2_ror DOUBLE COMMENT 'c2_2_uv/c1_2_uv' -- ,c2_2_rov DOUBLE COMMENT 'c2_2_uv/c1_2_exp' -- ,c3_2_uv BIGINT COMMENT 'C链d3-hop2: 回流去重人数' -- ,c3_2_pv BIGINT COMMENT 'C链d3-hop2: 回流点击次数' -- ,c3_2_exp BIGINT COMMENT 'C链d3-hop2: 回流session曝光数' -- ,c3_2_ror DOUBLE COMMENT 'c3_2_uv/c2_2_uv' -- ,c3_2_rov DOUBLE COMMENT 'c3_2_uv/c2_2_exp' -- -- C链 depth拆分 hop3 -- ,c1_3_uv BIGINT COMMENT 'C链d1-hop3: 回流去重人数' -- ,c1_3_pv BIGINT COMMENT 'C链d1-hop3: 回流点击次数' -- ,c1_3_exp BIGINT COMMENT 'C链d1-hop3: 回流session曝光数' -- ,c1_3_ror DOUBLE COMMENT 'c1_3_uv/cn_2_uv' -- ,c1_3_rov DOUBLE COMMENT 'c1_3_uv/cn_2_exp' -- ,c2_3_uv BIGINT COMMENT 'C链d2-hop3: 回流去重人数' -- ,c2_3_pv BIGINT COMMENT 'C链d2-hop3: 回流点击次数' -- ,c2_3_exp BIGINT COMMENT 'C链d2-hop3: 回流session曝光数' -- ,c2_3_ror DOUBLE COMMENT 'c2_3_uv/c1_3_uv' -- ,c2_3_rov DOUBLE COMMENT 'c2_3_uv/c1_3_exp' -- ,c3_3_uv BIGINT COMMENT 'C链d3-hop3: 回流去重人数' -- ,c3_3_pv BIGINT COMMENT 'C链d3-hop3: 回流点击次数' -- ,c3_3_exp BIGINT COMMENT 'C链d3-hop3: 回流session曝光数' -- ,c3_3_ror DOUBLE COMMENT 'c3_3_uv/c2_3_uv' -- ,c3_3_rov DOUBLE COMMENT 'c3_3_uv/c2_3_exp' -- -- ==================== D链 (session内后续曝光传播) ==================== -- ,d0 BIGINT COMMENT 'D链初始成本: session内后续曝光数' -- ,dn_1_uv BIGINT COMMENT 'D链hop1: 回流去重人数' -- ,dn_1_pv BIGINT COMMENT 'D链hop1: 回流点击次数' -- ,dn_1_exp BIGINT COMMENT 'D链hop1: 回流session曝光数' -- ,dn_1_ror DOUBLE COMMENT 'dn_1_uv/exposure_uv' -- ,dn_1_rov DOUBLE COMMENT 'dn_1_uv/d0' -- ,dn_2_uv BIGINT COMMENT 'D链hop2: 回流去重人数' -- ,dn_2_pv BIGINT COMMENT 'D链hop2: 回流点击次数' -- ,dn_2_exp BIGINT COMMENT 'D链hop2: 回流session曝光数' -- ,dn_2_ror DOUBLE COMMENT 'dn_2_uv/dn_1_uv' -- ,dn_2_rov DOUBLE COMMENT 'dn_2_uv/dn_1_exp' -- ,dn_3_uv BIGINT COMMENT 'D链hop3: 回流去重人数' -- ,dn_3_pv BIGINT COMMENT 'D链hop3: 回流点击次数' -- ,dn_3_exp BIGINT COMMENT 'D链hop3: 回流session曝光数' -- ,dn_3_ror DOUBLE COMMENT 'dn_3_uv/dn_2_uv' -- ,dn_3_rov DOUBLE COMMENT 'dn_3_uv/dn_2_exp' -- ,dn_total_uv BIGINT COMMENT 'D链合计UV' -- ,dn_total_ror DOUBLE COMMENT 'dn_total_uv/exposure_uv' -- ,dn_total_rov DOUBLE COMMENT 'dn_total_uv/d0' -- -- D链 depth拆分 hop1 -- ,d1_1_uv BIGINT COMMENT 'D链d1-hop1: 回流去重人数' -- ,d1_1_pv BIGINT COMMENT 'D链d1-hop1: 回流点击次数' -- ,d1_1_exp BIGINT COMMENT 'D链d1-hop1: 回流session曝光数' -- ,d1_1_ror DOUBLE COMMENT 'd1_1_uv/exposure_uv' -- ,d1_1_rov DOUBLE COMMENT 'd1_1_uv/d0' -- ,d2_1_uv BIGINT COMMENT 'D链d2-hop1: 回流去重人数' -- ,d2_1_pv BIGINT COMMENT 'D链d2-hop1: 回流点击次数' -- ,d2_1_exp BIGINT COMMENT 'D链d2-hop1: 回流session曝光数' -- ,d2_1_ror DOUBLE COMMENT 'd2_1_uv/d1_1_uv' -- ,d2_1_rov DOUBLE COMMENT 'd2_1_uv/d1_1_exp' -- ,d3_1_uv BIGINT COMMENT 'D链d3-hop1: 回流去重人数' -- ,d3_1_pv BIGINT COMMENT 'D链d3-hop1: 回流点击次数' -- ,d3_1_exp BIGINT COMMENT 'D链d3-hop1: 回流session曝光数' -- ,d3_1_ror DOUBLE COMMENT 'd3_1_uv/d2_1_uv' -- ,d3_1_rov DOUBLE COMMENT 'd3_1_uv/d2_1_exp' -- -- D链 depth拆分 hop2 -- ,d1_2_uv BIGINT COMMENT 'D链d1-hop2: 回流去重人数' -- ,d1_2_pv BIGINT COMMENT 'D链d1-hop2: 回流点击次数' -- ,d1_2_exp BIGINT COMMENT 'D链d1-hop2: 回流session曝光数' -- ,d1_2_ror DOUBLE COMMENT 'd1_2_uv/dn_1_uv' -- ,d1_2_rov DOUBLE COMMENT 'd1_2_uv/dn_1_exp' -- ,d2_2_uv BIGINT COMMENT 'D链d2-hop2: 回流去重人数' -- ,d2_2_pv BIGINT COMMENT 'D链d2-hop2: 回流点击次数' -- ,d2_2_exp BIGINT COMMENT 'D链d2-hop2: 回流session曝光数' -- ,d2_2_ror DOUBLE COMMENT 'd2_2_uv/d1_2_uv' -- ,d2_2_rov DOUBLE COMMENT 'd2_2_uv/d1_2_exp' -- ,d3_2_uv BIGINT COMMENT 'D链d3-hop2: 回流去重人数' -- ,d3_2_pv BIGINT COMMENT 'D链d3-hop2: 回流点击次数' -- ,d3_2_exp BIGINT COMMENT 'D链d3-hop2: 回流session曝光数' -- ,d3_2_ror DOUBLE COMMENT 'd3_2_uv/d2_2_uv' -- ,d3_2_rov DOUBLE COMMENT 'd3_2_uv/d2_2_exp' -- -- D链 depth拆分 hop3 -- ,d1_3_uv BIGINT COMMENT 'D链d1-hop3: 回流去重人数' -- ,d1_3_pv BIGINT COMMENT 'D链d1-hop3: 回流点击次数' -- ,d1_3_exp BIGINT COMMENT 'D链d1-hop3: 回流session曝光数' -- ,d1_3_ror DOUBLE COMMENT 'd1_3_uv/dn_2_uv' -- ,d1_3_rov DOUBLE COMMENT 'd1_3_uv/dn_2_exp' -- ,d2_3_uv BIGINT COMMENT 'D链d2-hop3: 回流去重人数' -- ,d2_3_pv BIGINT COMMENT 'D链d2-hop3: 回流点击次数' -- ,d2_3_exp BIGINT COMMENT 'D链d2-hop3: 回流session曝光数' -- ,d2_3_ror DOUBLE COMMENT 'd2_3_uv/d1_3_uv' -- ,d2_3_rov DOUBLE COMMENT 'd2_3_uv/d1_3_exp' -- ,d3_3_uv BIGINT COMMENT 'D链d3-hop3: 回流去重人数' -- ,d3_3_pv BIGINT COMMENT 'D链d3-hop3: 回流点击次数' -- ,d3_3_exp BIGINT COMMENT 'D链d3-hop3: 回流session曝光数' -- ,d3_3_ror DOUBLE COMMENT 'd3_3_uv/d2_3_uv' -- ,d3_3_rov DOUBLE COMMENT 'd3_3_uv/d2_3_exp' -- -- ==================== 全链路 ==================== -- ,all_return_n_uv BIGINT COMMENT '全链路拉回UV = B + C + D' -- ,all_rovn DOUBLE COMMENT 'all_return_n_uv/exposure_cnt' -- ,all_ror DOUBLE COMMENT 'all_return_n_uv/exposure_uv' -- ,all_rov DOUBLE COMMENT 'all_return_n_uv/exposure_cnt' -- ) -- COMMENT '曝光回流链路CUBE聚合-宽表版 (5维度: 用户分层/小时段/进入品类TOP1/推荐品类TOP10/内容idTOP1)' -- ; -- -- ===================================================================== -- -- INSERT: 从 wide 宽表聚合, CUBE 全维度组合 -- -- ===================================================================== -- -- SELECT * FROM loghubods.dwd_recsys_alg_exposure_agg_wide_20260209 WHERE dt = '${dt}' ORDER BY exposure_cnt DESC LIMIT 100; -- INSERT OVERWRITE TABLE loghubods.dwd_recsys_alg_exposure_agg_wide_20260209 WITH -- 用户拉活量分层 t_user_type AS ( SELECT DISTINCT type, openid FROM loghubods.mid_share_return_people_1year WHERE dt = TO_CHAR(DATEADD(TO_DATE('${dt}','YYYYMMDD'),-1,'dd'),'YYYYMMDD') AND type IS NOT NULL AND type != 'S_ALL' AND type NOT REGEXP 'R50' ) -- 模型预估分数 ,t_score AS ( SELECT apptype ,videoid ,recommendtraceid ,scoresmap ,sortscore FROM ( SELECT apptype ,videoid ,recommendtraceid ,scoresmap ,sortscore ,ROW_NUMBER() OVER (PARTITION BY apptype,videoid,recommendtraceid) AS rn FROM loghubods.statistics_log_hour WHERE dt LIKE '${dt}%' AND scoresmap IS NOT NULL ) WHERE rn = 1 ) -- TOP1 进入内容品类(场): 按回流人数取 TOP1 ,t_top_head_cate AS ( SELECT vt.merge_leve2 FROM loghubods.dwd_recsys_alg_exposure_base_20260209 base JOIN loghubods.video_merge_tag vt ON base.headvideoid = vt.videoid WHERE base.dt = '${dt}' AND vt.merge_leve2 IS NOT NULL GROUP BY vt.merge_leve2 ORDER BY SUM(CAST(is_return_noself AS BIGINT)) DESC LIMIT 1 ) -- TOP10 推荐内容品类(货): 按曝光次数取 TOP10 ,t_top_vid_cate AS ( SELECT vt.merge_leve2 FROM loghubods.dwd_recsys_alg_exposure_base_20260209 base JOIN loghubods.video_merge_tag vt ON base.vid = vt.videoid WHERE base.dt = '${dt}' AND vt.merge_leve2 IS NOT NULL GROUP BY vt.merge_leve2 ORDER BY COUNT(1) DESC LIMIT 10 ) -- TOP1 内容id(货): 每品类按曝光次数取 TOP1 (曝光>10w) ,t_top_vid AS ( SELECT merge_leve2, vid FROM ( SELECT vt.merge_leve2 ,base.vid ,COUNT(1) AS exp_cnt ,ROW_NUMBER() OVER (PARTITION BY vt.merge_leve2 ORDER BY COUNT(1) DESC) AS rk FROM loghubods.dwd_recsys_alg_exposure_base_20260209 base JOIN loghubods.video_merge_tag vt ON base.vid = vt.videoid WHERE base.dt = '${dt}' AND vt.merge_leve2 IS NOT NULL GROUP BY vt.merge_leve2, base.vid HAVING exp_cnt > 100000 ) WHERE rk <= 1 ) -- 宽表 ,t_wide AS ( SELECT base.* ,CASE WHEN e.type IS NULL OR e.type = 'R_0' THEN 'R0&新用户' WHEN e.type IN ('R_1','R_2_10','R_10_50') THEN 'R1-50' WHEN e.type IN ('R_50_100','R_100_180','R_180_330') THEN 'R_180_330' ELSE e.type END AS user_type ,CASE WHEN th.merge_leve2 IS NOT NULL THEN vt_head.merge_leve2 ELSE '其他' END AS head_merge_leve2 ,CASE WHEN tv.merge_leve2 IS NOT NULL THEN vt_vid.merge_leve2 ELSE '其他' END AS vid_merge_leve2 ,CASE WHEN ti.vid IS NOT NULL THEN base.vid ELSE '其他' END AS vid_id ,CAST(GET_JSON_OBJECT(e1.scoresmap,'$.fmRov') AS DOUBLE) AS str_pred ,1.22*POW(CAST(GET_JSON_OBJECT(e1.scoresmap,'$.NorXGBScore') AS DOUBLE),1.15) AS rosn_pred ,CAST(GET_JSON_OBJECT(e1.scoresmap,'$.hasReturnRovScore') AS DOUBLE) AS rosn_pred_origin ,e1.sortscore ,CASE WHEN CAST(hh AS INT) BETWEEN 0 AND 3 THEN '00-03' WHEN CAST(hh AS INT) BETWEEN 4 AND 7 THEN '04-07' WHEN CAST(hh AS INT) BETWEEN 8 AND 11 THEN '08-11' WHEN CAST(hh AS INT) BETWEEN 12 AND 15 THEN '12-15' WHEN CAST(hh AS INT) BETWEEN 16 AND 19 THEN '16-19' WHEN CAST(hh AS INT) BETWEEN 20 AND 23 THEN '20-23' ELSE '-' END AS hh_bucket FROM loghubods.dwd_recsys_alg_exposure_base_20260209 base LEFT JOIN t_user_type e ON SUBSTRING_INDEX(base.mid,'weixin_openid_',-1) = e.openid LEFT JOIN loghubods.video_merge_tag vt_head ON base.headvideoid = vt_head.videoid LEFT JOIN loghubods.video_merge_tag vt_vid ON base.vid = vt_vid.videoid LEFT JOIN t_score e1 ON base.apptype = e1.apptype AND base.vid = e1.videoid AND base.recomtraceid = e1.recommendtraceid LEFT JOIN t_top_head_cate th ON vt_head.merge_leve2 = th.merge_leve2 LEFT JOIN t_top_vid_cate tv ON vt_vid.merge_leve2 = tv.merge_leve2 LEFT JOIN t_top_vid ti ON base.vid = ti.vid WHERE base.dt = '${dt}' ) SELECT -- ==================== 维度列 ==================== '${dt}' AS dt ,CASE WHEN GROUPING(user_type) = 1 THEN 'SUM' ELSE NVL(user_type, 'SUM') END AS user_type ,CASE WHEN GROUPING(hh_bucket) = 1 THEN 'SUM' ELSE NVL(hh_bucket, 'SUM') END AS hh_bucket ,CASE WHEN GROUPING(head_merge_leve2) = 1 THEN 'SUM' ELSE NVL(head_merge_leve2, 'SUM') END AS head_merge_leve2 ,CASE WHEN GROUPING(vid_merge_leve2) = 1 THEN 'SUM' ELSE NVL(vid_merge_leve2, 'SUM') END AS vid_merge_leve2 ,CASE WHEN GROUPING(vid_id) = 1 THEN 'SUM' ELSE NVL(vid_id, 'SUM') END AS vid_id -- ==================== 基础流量 ==================== ,COUNT(1) AS exposure_cnt ,COUNT(DISTINCT mid) AS exposure_uv ,COUNT(DISTINCT vid) AS vid_cnt ,ROUND(COUNT(1) / COUNT(DISTINCT mid), 4) AS exposure_per_user -- ==================== 分享 & 回流漏斗 ==================== ,SUM(CAST(is_share AS BIGINT)) AS share_exposure_cnt ,SUM(CAST(share_cnt AS BIGINT)) AS share_cnt ,SUM(CAST(is_return_n AS BIGINT)) AS return_exposure_cnt ,SUM(CAST(is_return_noself AS BIGINT)) AS return_exposure_cnt_noself ,SUM(CAST(return_n_uv AS BIGINT)) AS return_uv ,SUM(CAST(return_n_uv_noself AS BIGINT)) AS return_uv_noself ,ROUND(COALESCE(SUM(CAST(is_share AS BIGINT)) / NULLIF(CAST(COUNT(1) AS DOUBLE), 0), 0), 6) AS share_rate ,ROUND(COALESCE(SUM(CAST(is_return_n AS BIGINT)) / NULLIF(CAST(COUNT(1) AS DOUBLE), 0), 0), 6) AS return_rate ,ROUND(COALESCE(SUM(CAST(is_return_noself AS BIGINT)) / NULLIF(CAST(COUNT(1) AS DOUBLE), 0), 0), 6) AS return_rate_noself ,ROUND(COALESCE(SUM(CAST(is_return_noself AS BIGINT)) / NULLIF(CAST(SUM(CAST(is_share AS BIGINT)) AS DOUBLE), 0), 0), 6) AS share_return_rate -- ==================== 模型预估: STR (曝光→非自身回流概率) ==================== ,ROUND(COALESCE(SUM(CAST(is_return_noself AS BIGINT)) / NULLIF(COUNT(1), 0), 0), 6) AS str_real ,ROUND(COALESCE(SUM(str_pred) / NULLIF(COUNT(1), 0), 0), 6) AS str_pred ,ROUND( (SUM(CAST(is_return_noself AS BIGINT)) / NULLIF(COUNT(1), 0)) / NULLIF(SUM(str_pred) / NULLIF(COUNT(1), 0), 0) , 4) AS str_copc ,ROUND(AVG(ABS(str_pred - CAST(is_return_noself AS BIGINT))), 6) AS str_mae ,ROUND(VARIANCE(str_pred - CAST(is_return_noself AS BIGINT)), 6) AS str_var -- ==================== 模型预估: ROSN (条件回流UV, 非自身) ==================== ,ROUND(COALESCE( SUM(CAST(return_n_uv_noself AS BIGINT)) / NULLIF(SUM(CAST(is_return_noself AS BIGINT)), 0) , 0), 6) AS rosn_real ,ROUND(COALESCE(SUM(CASE WHEN CAST(is_return_noself AS BIGINT) = 1 THEN rosn_pred END) / NULLIF(SUM(CAST(is_return_noself AS BIGINT)), 0), 0), 6) AS rosn_pred ,ROUND( (SUM(CAST(return_n_uv_noself AS BIGINT)) / NULLIF(SUM(CAST(is_return_noself AS BIGINT)), 0)) / NULLIF(SUM(CASE WHEN CAST(is_return_noself AS BIGINT) = 1 THEN rosn_pred END) / NULLIF(SUM(CAST(is_return_noself AS BIGINT)), 0), 0) , 4) AS rosn_copc ,ROUND(AVG(rosn_pred_origin), 6) AS rosn_pred_origin ,ROUND(AVG( CASE WHEN CAST(is_return_noself AS BIGINT) = 1 THEN ABS(rosn_pred - CAST(return_n_uv_noself AS BIGINT)) END ), 6) AS rosn_mae ,ROUND(VARIANCE( CASE WHEN CAST(is_return_noself AS BIGINT) = 1 THEN rosn_pred - CAST(return_n_uv_noself AS BIGINT) END ), 6) AS rosn_var -- ==================== 模型预估: ROVN (STR×ROSN) ==================== ,ROUND(COALESCE(SUM(CAST(return_n_uv_noself AS BIGINT)) / NULLIF(COUNT(1), 0), 0), 6) AS rovn_real ,ROUND(AVG(str_pred * rosn_pred), 6) AS rovn_pred ,ROUND( (SUM(CAST(return_n_uv_noself AS BIGINT)) / NULLIF(COUNT(1), 0)) / NULLIF(AVG(str_pred * rosn_pred), 0) , 4) AS rovn_copc ,ROUND(AVG(ABS(str_pred * rosn_pred - CAST(return_n_uv_noself AS BIGINT))), 6) AS rovn_mae ,ROUND(VARIANCE(str_pred * rosn_pred - CAST(return_n_uv_noself AS BIGINT)), 6) AS rovn_var ,ROUND(AVG(CAST(sortscore AS DOUBLE)), 6) AS sortscore_avg -- ==================== B链 ==================== ,SUM(CAST(bn_uv AS BIGINT)) AS bn_uv ,SUM(CAST(bn_pv AS BIGINT)) AS bn_pv ,SUM(CAST(bn_exp AS BIGINT)) AS bn_exp ,ROUND(COALESCE(SUM(CAST(bn_uv AS BIGINT)) / NULLIF(CAST(COUNT(DISTINCT mid) AS DOUBLE), 0), 0), 6) AS bn_ror ,ROUND(COALESCE(SUM(CAST(bn_uv AS BIGINT)) / NULLIF(CAST(COUNT(1) AS DOUBLE), 0), 0), 6) AS bn_rov ,SUM(CAST(b1_uv AS BIGINT)) AS b1_uv ,SUM(CAST(b1_pv AS BIGINT)) AS b1_pv ,SUM(CAST(b1_exp AS BIGINT)) AS b1_exp ,ROUND(COALESCE(SUM(CAST(b1_uv AS BIGINT)) / NULLIF(CAST(COUNT(DISTINCT mid) AS DOUBLE), 0), 0), 6) AS b1_ror ,ROUND(COALESCE(SUM(CAST(b1_uv AS BIGINT)) / NULLIF(CAST(COUNT(1) AS DOUBLE), 0), 0), 6) AS b1_rov ,SUM(CAST(b2_uv AS BIGINT)) AS b2_uv ,SUM(CAST(b2_pv AS BIGINT)) AS b2_pv ,SUM(CAST(b2_exp AS BIGINT)) AS b2_exp ,ROUND(COALESCE(SUM(CAST(b2_uv AS BIGINT)) / NULLIF(CAST(SUM(CAST(b1_uv AS BIGINT)) AS DOUBLE), 0), 0), 6) AS b2_ror ,ROUND(COALESCE(SUM(CAST(b2_uv AS BIGINT)) / NULLIF(CAST(SUM(CAST(b1_exp AS BIGINT)) AS DOUBLE), 0), 0), 6) AS b2_rov ,SUM(CAST(b3_uv AS BIGINT)) AS b3_uv ,SUM(CAST(b3_pv AS BIGINT)) AS b3_pv ,SUM(CAST(b3_exp AS BIGINT)) AS b3_exp ,ROUND(COALESCE(SUM(CAST(b3_uv AS BIGINT)) / NULLIF(CAST(SUM(CAST(b2_uv AS BIGINT)) AS DOUBLE), 0), 0), 6) AS b3_ror ,ROUND(COALESCE(SUM(CAST(b3_uv AS BIGINT)) / NULLIF(CAST(SUM(CAST(b2_exp AS BIGINT)) AS DOUBLE), 0), 0), 6) AS b3_rov -- ==================== C链 (全量depth, 按hop) ==================== ,SUM(CAST(cn_1_uv AS BIGINT)) AS cn_1_uv ,SUM(CAST(cn_1_pv AS BIGINT)) AS cn_1_pv ,SUM(CAST(cn_1_exp AS BIGINT)) AS cn_1_exp ,ROUND(COALESCE(SUM(CAST(cn_1_uv AS BIGINT)) / NULLIF(CAST(SUM(CAST(bn_uv AS BIGINT)) AS DOUBLE), 0), 0), 6) AS cn_1_ror ,ROUND(COALESCE(SUM(CAST(cn_1_uv AS BIGINT)) / NULLIF(CAST(SUM(CAST(bn_exp AS BIGINT)) AS DOUBLE), 0), 0), 6) AS cn_1_rov ,SUM(CAST(cn_2_uv AS BIGINT)) AS cn_2_uv ,SUM(CAST(cn_2_pv AS BIGINT)) AS cn_2_pv ,SUM(CAST(cn_2_exp AS BIGINT)) AS cn_2_exp ,ROUND(COALESCE(SUM(CAST(cn_2_uv AS BIGINT)) / NULLIF(CAST(SUM(CAST(cn_1_uv AS BIGINT)) AS DOUBLE), 0), 0), 6) AS cn_2_ror ,ROUND(COALESCE(SUM(CAST(cn_2_uv AS BIGINT)) / NULLIF(CAST(SUM(CAST(cn_1_exp AS BIGINT)) AS DOUBLE), 0), 0), 6) AS cn_2_rov ,SUM(CAST(cn_3_uv AS BIGINT)) AS cn_3_uv ,SUM(CAST(cn_3_pv AS BIGINT)) AS cn_3_pv ,SUM(CAST(cn_3_exp AS BIGINT)) AS cn_3_exp ,ROUND(COALESCE(SUM(CAST(cn_3_uv AS BIGINT)) / NULLIF(CAST(SUM(CAST(cn_2_uv AS BIGINT)) AS DOUBLE), 0), 0), 6) AS cn_3_ror ,ROUND(COALESCE(SUM(CAST(cn_3_uv AS BIGINT)) / NULLIF(CAST(SUM(CAST(cn_2_exp AS BIGINT)) AS DOUBLE), 0), 0), 6) AS cn_3_rov ,SUM(CAST(cn_1_uv AS BIGINT)) + SUM(CAST(cn_2_uv AS BIGINT)) + SUM(CAST(cn_3_uv AS BIGINT)) AS cn_total_uv ,ROUND(COALESCE( (SUM(CAST(cn_1_uv AS BIGINT)) + SUM(CAST(cn_2_uv AS BIGINT)) + SUM(CAST(cn_3_uv AS BIGINT))) / NULLIF(CAST(SUM(CAST(bn_uv AS BIGINT)) AS DOUBLE), 0) , 0), 6) AS cn_total_ror ,ROUND(COALESCE( (SUM(CAST(cn_1_uv AS BIGINT)) + SUM(CAST(cn_2_uv AS BIGINT)) + SUM(CAST(cn_3_uv AS BIGINT))) / NULLIF(CAST(SUM(CAST(bn_exp AS BIGINT)) AS DOUBLE), 0) , 0), 6) AS cn_total_rov -- ==================== C链 (按depth拆分) ==================== -- hop1 ,SUM(CAST(c1_1_uv AS BIGINT)) AS c1_1_uv ,SUM(CAST(c1_1_pv AS BIGINT)) AS c1_1_pv ,SUM(CAST(c1_1_exp AS BIGINT)) AS c1_1_exp ,ROUND(COALESCE(SUM(CAST(c1_1_uv AS BIGINT)) / NULLIF(CAST(SUM(CAST(bn_uv AS BIGINT)) AS DOUBLE), 0), 0), 6) AS c1_1_ror ,ROUND(COALESCE(SUM(CAST(c1_1_uv AS BIGINT)) / NULLIF(CAST(SUM(CAST(bn_exp AS BIGINT)) AS DOUBLE), 0), 0), 6) AS c1_1_rov ,SUM(CAST(c2_1_uv AS BIGINT)) AS c2_1_uv ,SUM(CAST(c2_1_pv AS BIGINT)) AS c2_1_pv ,SUM(CAST(c2_1_exp AS BIGINT)) AS c2_1_exp ,ROUND(COALESCE(SUM(CAST(c2_1_uv AS BIGINT)) / NULLIF(CAST(SUM(CAST(c1_1_uv AS BIGINT)) AS DOUBLE), 0), 0), 6) AS c2_1_ror ,ROUND(COALESCE(SUM(CAST(c2_1_uv AS BIGINT)) / NULLIF(CAST(SUM(CAST(c1_1_exp AS BIGINT)) AS DOUBLE), 0), 0), 6) AS c2_1_rov ,SUM(CAST(c3_1_uv AS BIGINT)) AS c3_1_uv ,SUM(CAST(c3_1_pv AS BIGINT)) AS c3_1_pv ,SUM(CAST(c3_1_exp AS BIGINT)) AS c3_1_exp ,ROUND(COALESCE(SUM(CAST(c3_1_uv AS BIGINT)) / NULLIF(CAST(SUM(CAST(c2_1_uv AS BIGINT)) AS DOUBLE), 0), 0), 6) AS c3_1_ror ,ROUND(COALESCE(SUM(CAST(c3_1_uv AS BIGINT)) / NULLIF(CAST(SUM(CAST(c2_1_exp AS BIGINT)) AS DOUBLE), 0), 0), 6) AS c3_1_rov -- hop2 ,SUM(CAST(c1_2_uv AS BIGINT)) AS c1_2_uv ,SUM(CAST(c1_2_pv AS BIGINT)) AS c1_2_pv ,SUM(CAST(c1_2_exp AS BIGINT)) AS c1_2_exp ,ROUND(COALESCE(SUM(CAST(c1_2_uv AS BIGINT)) / NULLIF(CAST(SUM(CAST(cn_1_uv AS BIGINT)) AS DOUBLE), 0), 0), 6) AS c1_2_ror ,ROUND(COALESCE(SUM(CAST(c1_2_uv AS BIGINT)) / NULLIF(CAST(SUM(CAST(cn_1_exp AS BIGINT)) AS DOUBLE), 0), 0), 6) AS c1_2_rov ,SUM(CAST(c2_2_uv AS BIGINT)) AS c2_2_uv ,SUM(CAST(c2_2_pv AS BIGINT)) AS c2_2_pv ,SUM(CAST(c2_2_exp AS BIGINT)) AS c2_2_exp ,ROUND(COALESCE(SUM(CAST(c2_2_uv AS BIGINT)) / NULLIF(CAST(SUM(CAST(c1_2_uv AS BIGINT)) AS DOUBLE), 0), 0), 6) AS c2_2_ror ,ROUND(COALESCE(SUM(CAST(c2_2_uv AS BIGINT)) / NULLIF(CAST(SUM(CAST(c1_2_exp AS BIGINT)) AS DOUBLE), 0), 0), 6) AS c2_2_rov ,SUM(CAST(c3_2_uv AS BIGINT)) AS c3_2_uv ,SUM(CAST(c3_2_pv AS BIGINT)) AS c3_2_pv ,SUM(CAST(c3_2_exp AS BIGINT)) AS c3_2_exp ,ROUND(COALESCE(SUM(CAST(c3_2_uv AS BIGINT)) / NULLIF(CAST(SUM(CAST(c2_2_uv AS BIGINT)) AS DOUBLE), 0), 0), 6) AS c3_2_ror ,ROUND(COALESCE(SUM(CAST(c3_2_uv AS BIGINT)) / NULLIF(CAST(SUM(CAST(c2_2_exp AS BIGINT)) AS DOUBLE), 0), 0), 6) AS c3_2_rov -- hop3 ,SUM(CAST(c1_3_uv AS BIGINT)) AS c1_3_uv ,SUM(CAST(c1_3_pv AS BIGINT)) AS c1_3_pv ,SUM(CAST(c1_3_exp AS BIGINT)) AS c1_3_exp ,ROUND(COALESCE(SUM(CAST(c1_3_uv AS BIGINT)) / NULLIF(CAST(SUM(CAST(cn_2_uv AS BIGINT)) AS DOUBLE), 0), 0), 6) AS c1_3_ror ,ROUND(COALESCE(SUM(CAST(c1_3_uv AS BIGINT)) / NULLIF(CAST(SUM(CAST(cn_2_exp AS BIGINT)) AS DOUBLE), 0), 0), 6) AS c1_3_rov ,SUM(CAST(c2_3_uv AS BIGINT)) AS c2_3_uv ,SUM(CAST(c2_3_pv AS BIGINT)) AS c2_3_pv ,SUM(CAST(c2_3_exp AS BIGINT)) AS c2_3_exp ,ROUND(COALESCE(SUM(CAST(c2_3_uv AS BIGINT)) / NULLIF(CAST(SUM(CAST(c1_3_uv AS BIGINT)) AS DOUBLE), 0), 0), 6) AS c2_3_ror ,ROUND(COALESCE(SUM(CAST(c2_3_uv AS BIGINT)) / NULLIF(CAST(SUM(CAST(c1_3_exp AS BIGINT)) AS DOUBLE), 0), 0), 6) AS c2_3_rov ,SUM(CAST(c3_3_uv AS BIGINT)) AS c3_3_uv ,SUM(CAST(c3_3_pv AS BIGINT)) AS c3_3_pv ,SUM(CAST(c3_3_exp AS BIGINT)) AS c3_3_exp ,ROUND(COALESCE(SUM(CAST(c3_3_uv AS BIGINT)) / NULLIF(CAST(SUM(CAST(c2_3_uv AS BIGINT)) AS DOUBLE), 0), 0), 6) AS c3_3_ror ,ROUND(COALESCE(SUM(CAST(c3_3_uv AS BIGINT)) / NULLIF(CAST(SUM(CAST(c2_3_exp AS BIGINT)) AS DOUBLE), 0), 0), 6) AS c3_3_rov -- ==================== D链 (全量depth, 按hop) ==================== ,SUM(CAST(d0 AS BIGINT)) AS d0 ,SUM(CAST(dn_1_uv AS BIGINT)) AS dn_1_uv ,SUM(CAST(dn_1_pv AS BIGINT)) AS dn_1_pv ,SUM(CAST(dn_1_exp AS BIGINT)) AS dn_1_exp ,ROUND(COALESCE(SUM(CAST(dn_1_uv AS BIGINT)) / NULLIF(CAST(COUNT(DISTINCT mid) AS DOUBLE), 0), 0), 6) AS dn_1_ror ,ROUND(COALESCE(SUM(CAST(dn_1_uv AS BIGINT)) / NULLIF(CAST(SUM(CAST(d0 AS BIGINT)) AS DOUBLE), 0), 0), 6) AS dn_1_rov ,SUM(CAST(dn_2_uv AS BIGINT)) AS dn_2_uv ,SUM(CAST(dn_2_pv AS BIGINT)) AS dn_2_pv ,SUM(CAST(dn_2_exp AS BIGINT)) AS dn_2_exp ,ROUND(COALESCE(SUM(CAST(dn_2_uv AS BIGINT)) / NULLIF(CAST(SUM(CAST(dn_1_uv AS BIGINT)) AS DOUBLE), 0), 0), 6) AS dn_2_ror ,ROUND(COALESCE(SUM(CAST(dn_2_uv AS BIGINT)) / NULLIF(CAST(SUM(CAST(dn_1_exp AS BIGINT)) AS DOUBLE), 0), 0), 6) AS dn_2_rov ,SUM(CAST(dn_3_uv AS BIGINT)) AS dn_3_uv ,SUM(CAST(dn_3_pv AS BIGINT)) AS dn_3_pv ,SUM(CAST(dn_3_exp AS BIGINT)) AS dn_3_exp ,ROUND(COALESCE(SUM(CAST(dn_3_uv AS BIGINT)) / NULLIF(CAST(SUM(CAST(dn_2_uv AS BIGINT)) AS DOUBLE), 0), 0), 6) AS dn_3_ror ,ROUND(COALESCE(SUM(CAST(dn_3_uv AS BIGINT)) / NULLIF(CAST(SUM(CAST(dn_2_exp AS BIGINT)) AS DOUBLE), 0), 0), 6) AS dn_3_rov ,SUM(CAST(dn_1_uv AS BIGINT)) + SUM(CAST(dn_2_uv AS BIGINT)) + SUM(CAST(dn_3_uv AS BIGINT)) AS dn_total_uv ,ROUND(COALESCE( (SUM(CAST(dn_1_uv AS BIGINT)) + SUM(CAST(dn_2_uv AS BIGINT)) + SUM(CAST(dn_3_uv AS BIGINT))) / NULLIF(CAST(COUNT(DISTINCT mid) AS DOUBLE), 0) , 0), 6) AS dn_total_ror ,ROUND(COALESCE( (SUM(CAST(dn_1_uv AS BIGINT)) + SUM(CAST(dn_2_uv AS BIGINT)) + SUM(CAST(dn_3_uv AS BIGINT))) / NULLIF(CAST(SUM(CAST(d0 AS BIGINT)) AS DOUBLE), 0) , 0), 6) AS dn_total_rov -- ==================== D链 (按depth拆分) ==================== -- hop1 ,SUM(CAST(d1_1_uv AS BIGINT)) AS d1_1_uv ,SUM(CAST(d1_1_pv AS BIGINT)) AS d1_1_pv ,SUM(CAST(d1_1_exp AS BIGINT)) AS d1_1_exp ,ROUND(COALESCE(SUM(CAST(d1_1_uv AS BIGINT)) / NULLIF(CAST(COUNT(DISTINCT mid) AS DOUBLE), 0), 0), 6) AS d1_1_ror ,ROUND(COALESCE(SUM(CAST(d1_1_uv AS BIGINT)) / NULLIF(CAST(SUM(CAST(d0 AS BIGINT)) AS DOUBLE), 0), 0), 6) AS d1_1_rov ,SUM(CAST(d2_1_uv AS BIGINT)) AS d2_1_uv ,SUM(CAST(d2_1_pv AS BIGINT)) AS d2_1_pv ,SUM(CAST(d2_1_exp AS BIGINT)) AS d2_1_exp ,ROUND(COALESCE(SUM(CAST(d2_1_uv AS BIGINT)) / NULLIF(CAST(SUM(CAST(d1_1_uv AS BIGINT)) AS DOUBLE), 0), 0), 6) AS d2_1_ror ,ROUND(COALESCE(SUM(CAST(d2_1_uv AS BIGINT)) / NULLIF(CAST(SUM(CAST(d1_1_exp AS BIGINT)) AS DOUBLE), 0), 0), 6) AS d2_1_rov ,SUM(CAST(d3_1_uv AS BIGINT)) AS d3_1_uv ,SUM(CAST(d3_1_pv AS BIGINT)) AS d3_1_pv ,SUM(CAST(d3_1_exp AS BIGINT)) AS d3_1_exp ,ROUND(COALESCE(SUM(CAST(d3_1_uv AS BIGINT)) / NULLIF(CAST(SUM(CAST(d2_1_uv AS BIGINT)) AS DOUBLE), 0), 0), 6) AS d3_1_ror ,ROUND(COALESCE(SUM(CAST(d3_1_uv AS BIGINT)) / NULLIF(CAST(SUM(CAST(d2_1_exp AS BIGINT)) AS DOUBLE), 0), 0), 6) AS d3_1_rov -- hop2 ,SUM(CAST(d1_2_uv AS BIGINT)) AS d1_2_uv ,SUM(CAST(d1_2_pv AS BIGINT)) AS d1_2_pv ,SUM(CAST(d1_2_exp AS BIGINT)) AS d1_2_exp ,ROUND(COALESCE(SUM(CAST(d1_2_uv AS BIGINT)) / NULLIF(CAST(SUM(CAST(dn_1_uv AS BIGINT)) AS DOUBLE), 0), 0), 6) AS d1_2_ror ,ROUND(COALESCE(SUM(CAST(d1_2_uv AS BIGINT)) / NULLIF(CAST(SUM(CAST(dn_1_exp AS BIGINT)) AS DOUBLE), 0), 0), 6) AS d1_2_rov ,SUM(CAST(d2_2_uv AS BIGINT)) AS d2_2_uv ,SUM(CAST(d2_2_pv AS BIGINT)) AS d2_2_pv ,SUM(CAST(d2_2_exp AS BIGINT)) AS d2_2_exp ,ROUND(COALESCE(SUM(CAST(d2_2_uv AS BIGINT)) / NULLIF(CAST(SUM(CAST(d1_2_uv AS BIGINT)) AS DOUBLE), 0), 0), 6) AS d2_2_ror ,ROUND(COALESCE(SUM(CAST(d2_2_uv AS BIGINT)) / NULLIF(CAST(SUM(CAST(d1_2_exp AS BIGINT)) AS DOUBLE), 0), 0), 6) AS d2_2_rov ,SUM(CAST(d3_2_uv AS BIGINT)) AS d3_2_uv ,SUM(CAST(d3_2_pv AS BIGINT)) AS d3_2_pv ,SUM(CAST(d3_2_exp AS BIGINT)) AS d3_2_exp ,ROUND(COALESCE(SUM(CAST(d3_2_uv AS BIGINT)) / NULLIF(CAST(SUM(CAST(d2_2_uv AS BIGINT)) AS DOUBLE), 0), 0), 6) AS d3_2_ror ,ROUND(COALESCE(SUM(CAST(d3_2_uv AS BIGINT)) / NULLIF(CAST(SUM(CAST(d2_2_exp AS BIGINT)) AS DOUBLE), 0), 0), 6) AS d3_2_rov -- hop3 ,SUM(CAST(d1_3_uv AS BIGINT)) AS d1_3_uv ,SUM(CAST(d1_3_pv AS BIGINT)) AS d1_3_pv ,SUM(CAST(d1_3_exp AS BIGINT)) AS d1_3_exp ,ROUND(COALESCE(SUM(CAST(d1_3_uv AS BIGINT)) / NULLIF(CAST(SUM(CAST(dn_2_uv AS BIGINT)) AS DOUBLE), 0), 0), 6) AS d1_3_ror ,ROUND(COALESCE(SUM(CAST(d1_3_uv AS BIGINT)) / NULLIF(CAST(SUM(CAST(dn_2_exp AS BIGINT)) AS DOUBLE), 0), 0), 6) AS d1_3_rov ,SUM(CAST(d2_3_uv AS BIGINT)) AS d2_3_uv ,SUM(CAST(d2_3_pv AS BIGINT)) AS d2_3_pv ,SUM(CAST(d2_3_exp AS BIGINT)) AS d2_3_exp ,ROUND(COALESCE(SUM(CAST(d2_3_uv AS BIGINT)) / NULLIF(CAST(SUM(CAST(d1_3_uv AS BIGINT)) AS DOUBLE), 0), 0), 6) AS d2_3_ror ,ROUND(COALESCE(SUM(CAST(d2_3_uv AS BIGINT)) / NULLIF(CAST(SUM(CAST(d1_3_exp AS BIGINT)) AS DOUBLE), 0), 0), 6) AS d2_3_rov ,SUM(CAST(d3_3_uv AS BIGINT)) AS d3_3_uv ,SUM(CAST(d3_3_pv AS BIGINT)) AS d3_3_pv ,SUM(CAST(d3_3_exp AS BIGINT)) AS d3_3_exp ,ROUND(COALESCE(SUM(CAST(d3_3_uv AS BIGINT)) / NULLIF(CAST(SUM(CAST(d2_3_uv AS BIGINT)) AS DOUBLE), 0), 0), 6) AS d3_3_ror ,ROUND(COALESCE(SUM(CAST(d3_3_uv AS BIGINT)) / NULLIF(CAST(SUM(CAST(d2_3_exp AS BIGINT)) AS DOUBLE), 0), 0), 6) AS d3_3_rov -- ==================== 全链路 ==================== ,SUM(CAST(return_n_uv_noself AS BIGINT)) + SUM(CAST(cn_1_uv AS BIGINT)) + SUM(CAST(cn_2_uv AS BIGINT)) + SUM(CAST(cn_3_uv AS BIGINT)) + SUM(CAST(dn_1_uv AS BIGINT)) + SUM(CAST(dn_2_uv AS BIGINT)) + SUM(CAST(dn_3_uv AS BIGINT)) AS all_return_n_uv ,ROUND(COALESCE( ( SUM(CAST(return_n_uv_noself AS BIGINT)) + SUM(CAST(cn_1_uv AS BIGINT)) + SUM(CAST(cn_2_uv AS BIGINT)) + SUM(CAST(cn_3_uv AS BIGINT)) + SUM(CAST(dn_1_uv AS BIGINT)) + SUM(CAST(dn_2_uv AS BIGINT)) + SUM(CAST(dn_3_uv AS BIGINT)) ) / NULLIF(COUNT(1), 0) , 0), 6) AS all_rovn ,ROUND(COALESCE( ( SUM(CAST(return_n_uv_noself AS BIGINT)) + SUM(CAST(cn_1_uv AS BIGINT)) + SUM(CAST(cn_2_uv AS BIGINT)) + SUM(CAST(cn_3_uv AS BIGINT)) + SUM(CAST(dn_1_uv AS BIGINT)) + SUM(CAST(dn_2_uv AS BIGINT)) + SUM(CAST(dn_3_uv AS BIGINT)) ) / NULLIF(CAST(COUNT(1) AS DOUBLE), 0) , 0), 6) AS all_rov ,ROUND(COALESCE( ( SUM(CAST(return_n_uv_noself AS BIGINT)) + SUM(CAST(cn_1_uv AS BIGINT)) + SUM(CAST(cn_2_uv AS BIGINT)) + SUM(CAST(cn_3_uv AS BIGINT)) + SUM(CAST(dn_1_uv AS BIGINT)) + SUM(CAST(dn_2_uv AS BIGINT)) + SUM(CAST(dn_3_uv AS BIGINT)) ) / NULLIF(CAST(COUNT(DISTINCT mid) AS DOUBLE), 0) , 0), 6) AS all_ror -- ===================================================================== -- FROM + GROUP BY CUBE -- ===================================================================== FROM t_wide GROUP BY CUBE( user_type ,hh_bucket ,head_merge_leve2 ,vid_merge_leve2 ,vid_id ) ORDER BY exposure_cnt DESC ;