data_analysis/table_gen/loghubods.dwd_recsys_alg_exposure_agg_wide_20260209.sql

-- =====================================================================
-- 曝光回流链路 CUBE 聚合表 (宽表版, 含用户/品类维度 + 模型预估 + 全链路漏斗)
-- 维度: user_type × hh_bucket × head_merge_leve2 × vid_merge_leve2 × vid_id (CUBE)
-- 依赖: base_20260209 → (JOIN user_type + video_merge_tag + t_score) → CUBE 聚合
-- 参考: de.sql + dwd_recsys_alg_exposure_agg_20260209
-- =====================================================================
--
-- 指标分区:
--   基础流量       exposure_cnt / exposure_uv / vid_cnt / exposure_per_user
--   分享&回流漏斗  share_exposure_cnt → share_cnt → return_exposure_cnt → return_uv
--                  + 4 个 rate (share_rate / return_rate / return_rate_noself / share_return_rate)
--   模型预估       STR (曝光→非自身回流概率) / ROSN (条件回流UV) / ROVN (STR×ROSN)
--                  每组: _real(label) + _pred(预估) + _copc + _mae + _var
--                  _real 与漏斗字段等价: str_real=return_rate_noself, rovn_real=return_uv_noself/exposure_cnt
--   B/C/D 链       每级: _uv + _pv + _exp + _ror + _rov
--   全链路         all_uv/pv/exp = B + C + D, all_ror/rov
--
-- rov/ror 分母推导 (逐级递推):
--   depth 维度: depth=1 的成本=该hop入口; depth=N+1 的成本=depth=N 的输出
--   hop 维度:   hop1 的成本=链路入口; hop N+1 的成本=hop N 全量depth的输出
--
--   B链:  bn/b1 → rov=uv/COUNT(1), ror=uv/COUNT(DISTINCT mid)
--         b2    → rov=uv/b1_exp,    ror=uv/b1_uv
--         b3    → rov=uv/b2_exp,    ror=uv/b2_uv
--   C链全量: cn_1 → rov=uv/bn_exp,    ror=uv/bn_uv
--            cn_2 → rov=uv/cn_1_exp,  ror=uv/cn_1_uv
--            cn_3 → rov=uv/cn_2_exp,  ror=uv/cn_2_uv
--   C链depth拆分: cX_Y → rov=uv/上级exp, ror=uv/上级uv (X=depth, Y=hop)
--            hop1: c1_1→bn, c2_1→c1_1, c3_1→c2_1
--            hop2: c1_2→cn_1, c2_2→c1_2, c3_2→c2_2
--            hop3: c1_3→cn_2, c2_3→c1_3, c3_3→c2_3
--   D链全量: dn_1 → rov=uv/d0,         ror=uv/COUNT(DISTINCT mid)
--            dn_2 → rov=uv/dn_1_exp,   ror=uv/dn_1_uv
--            dn_3 → rov=uv/dn_2_exp,   ror=uv/dn_2_uv
--   D链depth拆分: dX_Y (同 C 链模式)
--            hop1: d1_1→d0/mid, d2_1→d1_1, d3_1→d2_1
--            hop2: d1_2→dn_1, d2_2→d1_2, d3_2→d2_2
--            hop3: d1_3→dn_2, d2_3→d1_3, d3_3→d2_3
--   全链路: all → rov=uv/COUNT(1), ror=uv/COUNT(DISTINCT mid)
-- =====================================================================

-- 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_1_uv + cn_2_uv + cn_3_uv'
    ,cn_total_pv              BIGINT    COMMENT 'C链合计PV = cn_1_pv + cn_2_pv + cn_3_pv'
    ,cn_total_exp             BIGINT    COMMENT 'C链合计EXP = cn_1_exp + cn_2_exp + cn_3_exp'
    ,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_1_uv + dn_2_uv + dn_3_uv'
    ,dn_total_pv              BIGINT    COMMENT 'D链合计PV = dn_1_pv + dn_2_pv + dn_3_pv'
    ,dn_total_exp             BIGINT    COMMENT 'D链合计EXP = dn_1_exp + dn_2_exp + dn_3_exp'
    ,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_uv                   BIGINT    COMMENT '全链路拉回UV = bn_uv + cn_total_uv + dn_total_uv'
    ,all_pv                   BIGINT    COMMENT '全链路拉回PV = bn_pv + cn_total_pv + dn_total_pv'
    ,all_exp                  BIGINT    COMMENT '全链路拉回EXP = bn_exp + cn_total_exp + dn_total_exp'
    ,all_ror                  DOUBLE    COMMENT 'all_uv/exposure_uv'
    ,all_rov                  DOUBLE    COMMENT 'all_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
        ,SUM(CAST(cn_1_pv AS BIGINT)) + SUM(CAST(cn_2_pv AS BIGINT)) + SUM(CAST(cn_3_pv AS BIGINT))    AS cn_total_pv
        ,SUM(CAST(cn_1_exp AS BIGINT)) + SUM(CAST(cn_2_exp AS BIGINT)) + SUM(CAST(cn_3_exp AS BIGINT))  AS cn_total_exp
        ,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
        ,SUM(CAST(dn_1_pv AS BIGINT)) + SUM(CAST(dn_2_pv AS BIGINT)) + SUM(CAST(dn_3_pv AS BIGINT))    AS dn_total_pv
        ,SUM(CAST(dn_1_exp AS BIGINT)) + SUM(CAST(dn_2_exp AS BIGINT)) + SUM(CAST(dn_3_exp AS BIGINT))  AS dn_total_exp
        ,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(bn_uv 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_uv
        ,SUM(CAST(bn_pv AS BIGINT))
            + SUM(CAST(cn_1_pv AS BIGINT)) + SUM(CAST(cn_2_pv AS BIGINT)) + SUM(CAST(cn_3_pv AS BIGINT))
            + SUM(CAST(dn_1_pv AS BIGINT)) + SUM(CAST(dn_2_pv AS BIGINT)) + SUM(CAST(dn_3_pv AS BIGINT))
                                                                                        AS all_pv
        ,SUM(CAST(bn_exp AS BIGINT))
            + SUM(CAST(cn_1_exp AS BIGINT)) + SUM(CAST(cn_2_exp AS BIGINT)) + SUM(CAST(cn_3_exp AS BIGINT))
            + SUM(CAST(dn_1_exp AS BIGINT)) + SUM(CAST(dn_2_exp AS BIGINT)) + SUM(CAST(dn_3_exp AS BIGINT))
                                                                                        AS all_exp
        ,ROUND(COALESCE(
            (   SUM(CAST(bn_uv 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
        ,ROUND(COALESCE(
            (   SUM(CAST(bn_uv 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


-- =====================================================================
-- 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
;