-- 预处理：解析 scoresmap + page 分类
-- v4: 新增 top20 vid 分组 + GROUPING SETS + 曝光占比
-- v5: 新增相对对照组的变化率字段
-- v6: 新增 GAUC 指标（str/rosn/rov，三种加权方式：曝光/样本对/等权）
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 = '${dt}'
    AND     apptype IN ("0","4")
    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_filtered AS
(
    SELECT  *
    FROM    t_raw
    WHERE   page_type = "推荐"
)
-- 特征提取与维度映射
,t_base AS
(
    SELECT  dt
            ,apptype
            ,CASE   WHEN apptype IN ("4") AND abcode IN ("ab0","ab1") THEN "实验组-先验地域降权"
                    WHEN apptype IN ("4") AND abcode IN ("ab6","ab7") THEN "实验组-str+校准&ros-统计量"
                    WHEN apptype IN ("4") AND abcode IN ("ab8","ab9") THEN "实验组-str+校准"
                    WHEN apptype IN ("4") AND abcode IN ("ab2","ab3") THEN "对照组"
                    WHEN apptype IN ("4") AND abcode IN ("ab4","ab5") THEN "ab4-5"
                    ELSE "其他"
            END AS abcode
            ,page_type AS page
            ,mid
            ,vid
            ,is_share
            ,share_cnt
            ,is_return_1
            ,is_return_n
            ,is_return_noself
            ,return_1_uv
            ,return_n_uv
            ,return_n_uv_noself
            ,new_exposure_cnt
            ,flowpool
            ,scoresmap
            ,CAST(GET_JSON_OBJECT(scoresmap,'$.fmRov') AS DOUBLE) AS str_pred
            ,1.22 * pow(CAST(GET_JSON_OBJECT(scoresmap,'$.NorXGBScore') AS DOUBLE), 1.15) AS rosn_pred
            ,CAST(GET_JSON_OBJECT(scoresmap,'$.hasReturnRovScore') AS DOUBLE) AS rosn_stat
            ,GET_JSON_OBJECT(v1_feature,'$.title') AS vid_title
    FROM    t_filtered
)
,t_valid AS
(
    SELECT  *
    FROM    t_base
    WHERE   str_pred IS NOT NULL
    AND     rosn_pred IS NOT NULL
)
-- 计算每个 abcode 下曝光量 top20 的 vid
,t_vid_rank AS
(
    SELECT  dt
            ,apptype
            ,abcode
            ,vid
            ,COUNT(1) AS vid_exp_cnt
            ,ROW_NUMBER() OVER (PARTITION BY dt, apptype, abcode ORDER BY COUNT(1) DESC) AS vid_rank
    FROM    t_valid
    GROUP BY dt, apptype, abcode, vid
)
,t_top5_vid AS
(
    SELECT  dt, apptype, abcode, vid, vid_rank
    FROM    t_vid_rank
    WHERE   vid_rank <= 20
)
-- 标记 top20 vid
,t_with_top5 AS
(
    SELECT  a.*
            ,CASE WHEN b.vid IS NOT NULL THEN a.vid ELSE NULL END AS top5_vid
            ,CASE WHEN b.vid IS NOT NULL THEN a.vid_title ELSE NULL END AS top5_vid_title
            ,b.vid_rank AS top5_vid_rank
    FROM    t_valid a
    LEFT JOIN t_top5_vid b
    ON      a.dt = b.dt
    AND     a.apptype = b.apptype
    AND     a.abcode = b.abcode
    AND     a.vid = b.vid
)

-- ========== GAUC 计算（仅 vid='all' 粒度）==========
-- 1. str/rov gauc（秩和公式）
,t_with_rank AS
(
    SELECT  dt, apptype, abcode, mid, is_return_noself, return_n_uv_noself
            ,str_pred, rosn_pred, rosn_stat
            ,str_pred * rosn_pred AS rov_pred
            ,str_pred * rosn_stat AS rov_stat_pred
            ,ROW_NUMBER() OVER (PARTITION BY dt, apptype, abcode, mid ORDER BY str_pred) AS str_rank_user
            ,ROW_NUMBER() OVER (PARTITION BY dt, apptype, abcode, mid ORDER BY str_pred * rosn_pred) AS rov_rank_user
            ,ROW_NUMBER() OVER (PARTITION BY dt, apptype, abcode, mid ORDER BY str_pred * rosn_stat) AS rov_stat_rank_user
    FROM    t_valid
)
,t_user_auc AS
(
    SELECT  dt, apptype, abcode, mid
            ,COUNT(1) AS user_exp
            ,SUM(CASE WHEN is_return_noself > 0 THEN 1 ELSE 0 END) AS user_n_pos
            ,SUM(CASE WHEN is_return_noself > 0 THEN str_rank_user ELSE 0 END) AS user_sum_str_pos_rank
            ,SUM(CASE WHEN is_return_noself > 0 THEN rov_rank_user ELSE 0 END) AS user_sum_rov_pos_rank
            ,SUM(CASE WHEN is_return_noself > 0 THEN rov_stat_rank_user ELSE 0 END) AS user_sum_rov_stat_pos_rank
    FROM    t_with_rank
    GROUP BY dt, apptype, abcode, mid
)
,t_user_auc_valid AS
(
    SELECT  *
            ,user_n_pos * (user_exp - user_n_pos) AS user_pairs
            ,CASE
                WHEN user_n_pos * (user_exp - user_n_pos) < 3 THEN NULL
                ELSE GREATEST(0.0, LEAST(1.0,
                    (user_sum_str_pos_rank * 1.0 / user_n_pos - (user_n_pos + 1) / 2.0) / (user_exp - user_n_pos)
                ))
            END AS user_str_auc
            ,CASE
                WHEN user_n_pos * (user_exp - user_n_pos) < 3 THEN NULL
                ELSE GREATEST(0.0, LEAST(1.0,
                    (user_sum_rov_pos_rank * 1.0 / user_n_pos - (user_n_pos + 1) / 2.0) / (user_exp - user_n_pos)
                ))
            END AS user_rov_auc
            ,CASE
                WHEN user_n_pos * (user_exp - user_n_pos) < 3 THEN NULL
                ELSE GREATEST(0.0, LEAST(1.0,
                    (user_sum_rov_stat_pos_rank * 1.0 / user_n_pos - (user_n_pos + 1) / 2.0) / (user_exp - user_n_pos)
                ))
            END AS user_rov_stat_auc
    FROM    t_user_auc
)
,t_gauc AS
(
    SELECT  dt, apptype, abcode
            -- str: 三种加权方式
            ,round(SUM(user_exp * user_str_auc) / NULLIF(SUM(CASE WHEN user_str_auc IS NOT NULL THEN user_exp END), 0), 6) AS str_gauc_曝光
            ,round(SUM(user_pairs * user_str_auc) / NULLIF(SUM(CASE WHEN user_str_auc IS NOT NULL THEN user_pairs END), 0), 6) AS str_gauc_样本对
            ,round(AVG(user_str_auc), 6) AS str_gauc_等权
            -- rov: 三种加权方式
            ,round(SUM(user_exp * user_rov_auc) / NULLIF(SUM(CASE WHEN user_rov_auc IS NOT NULL THEN user_exp END), 0), 6) AS rov_gauc_曝光
            ,round(SUM(user_pairs * user_rov_auc) / NULLIF(SUM(CASE WHEN user_rov_auc IS NOT NULL THEN user_pairs END), 0), 6) AS rov_gauc_样本对
            ,round(AVG(user_rov_auc), 6) AS rov_gauc_等权
            -- rov_stat: 三种加权方式
            ,round(SUM(user_exp * user_rov_stat_auc) / NULLIF(SUM(CASE WHEN user_rov_stat_auc IS NOT NULL THEN user_exp END), 0), 6) AS rov_stat_gauc_曝光
            ,round(SUM(user_pairs * user_rov_stat_auc) / NULLIF(SUM(CASE WHEN user_rov_stat_auc IS NOT NULL THEN user_pairs END), 0), 6) AS rov_stat_gauc_样本对
            ,round(AVG(user_rov_stat_auc), 6) AS rov_stat_gauc_等权
    FROM    t_user_auc_valid
    GROUP BY dt, apptype, abcode
)

-- 2. rosn gauc（仅有回流样本，连续值标签）
,t_has_return AS
(
    SELECT  dt, apptype, abcode, mid, return_n_uv_noself, rosn_pred, rosn_stat
    FROM    t_valid
    WHERE   is_return_noself > 0
)
,t_rosn_pairs AS
(
    SELECT  a.dt, a.apptype, a.abcode, a.mid
            ,CASE WHEN a.rosn_pred > b.rosn_pred THEN 1.0
                  WHEN a.rosn_pred = b.rosn_pred THEN 0.5
                  ELSE 0.0 END AS concordant_pred
            ,CASE WHEN a.rosn_stat > b.rosn_stat THEN 1.0
                  WHEN a.rosn_stat = b.rosn_stat THEN 0.5
                  ELSE 0.0 END AS concordant_stat
    FROM    t_has_return a
    JOIN    t_has_return b
    ON      a.dt = b.dt AND a.apptype = b.apptype AND a.abcode = b.abcode
    AND     a.mid = b.mid
    AND     a.return_n_uv_noself > b.return_n_uv_noself
)
,t_user_rosn_exp AS
(
    SELECT  dt, apptype, abcode, mid
            ,COUNT(1) AS user_exp
    FROM    t_has_return
    GROUP BY dt, apptype, abcode, mid
)
,t_user_rosn_cindex AS
(
    SELECT  a.dt, a.apptype, a.abcode, a.mid
            ,b.user_exp
            ,COUNT(1) AS user_pairs
            ,AVG(concordant_pred) AS user_cindex_pred
            ,AVG(concordant_stat) AS user_cindex_stat
    FROM    t_rosn_pairs a
    JOIN    t_user_rosn_exp b
    ON      a.dt = b.dt AND a.apptype = b.apptype AND a.abcode = b.abcode AND a.mid = b.mid
    GROUP BY a.dt, a.apptype, a.abcode, a.mid, b.user_exp
    HAVING  COUNT(1) >= 3
)
,t_rosn_gauc AS
(
    SELECT  dt, apptype, abcode
            -- rosn: 三种加权方式
            ,round(SUM(user_exp * user_cindex_pred) / NULLIF(SUM(CASE WHEN user_cindex_pred IS NOT NULL THEN user_exp END), 0), 6) AS rosn_gauc_曝光
            ,round(SUM(user_pairs * user_cindex_pred) / SUM(user_pairs), 6) AS rosn_gauc_样本对
            ,round(AVG(user_cindex_pred), 6) AS rosn_gauc_等权
            -- rosn_stat: 三种加权方式
            ,round(SUM(user_exp * user_cindex_stat) / NULLIF(SUM(CASE WHEN user_cindex_stat IS NOT NULL THEN user_exp END), 0), 6) AS rosn_stat_gauc_曝光
            ,round(SUM(user_pairs * user_cindex_stat) / SUM(user_pairs), 6) AS rosn_stat_gauc_样本对
            ,round(AVG(user_cindex_stat), 6) AS rosn_stat_gauc_等权
    FROM    t_user_rosn_cindex
    GROUP BY dt, apptype, abcode
)

-- 先聚合
,t_agg AS
(
    SELECT  dt
            ,COALESCE(apptype, 'sum') AS apptype
            ,COALESCE(abcode, 'sum') AS abcode
            ,COALESCE(CAST(top5_vid AS STRING), 'all') AS vid
            ,CASE WHEN GROUPING(top5_vid) = 1 THEN NULL ELSE MAX(top5_vid_title) END AS vid_title
            ,CASE WHEN GROUPING(top5_vid) = 1 THEN NULL ELSE MAX(top5_vid_rank) END AS vid_rank
            -- COPC
            ,round((SUM(is_return_noself) / COUNT(1)) / NULLIF(SUM(str_pred) / COUNT(1), 0), 4) AS str_copc
            ,round((SUM(return_n_uv_noself) / NULLIF(SUM(is_return_noself), 0)) / NULLIF(SUM(rosn_pred) / COUNT(1), 0), 4) AS rosn_copc
            ,round((SUM(return_n_uv_noself) / NULLIF(SUM(is_return_noself), 0)) / NULLIF(SUM(rosn_stat) / COUNT(1), 0), 4) AS rosn_stat_copc
            ,round((SUM(return_n_uv_noself) / COUNT(1)) / NULLIF(AVG(str_pred * rosn_pred), 0), 4) AS rovn_copc
            ,round((SUM(return_n_uv_noself) / COUNT(1)) / NULLIF(AVG(str_pred * rosn_stat), 0), 4) AS rovn_stat_copc
            -- 模型预测与真实值
            ,round(COALESCE(SUM(is_return_noself) / COUNT(1),0),6) AS str_real
            ,round(COALESCE(SUM(str_pred) / COUNT(1),0),6) AS str_pred
            ,round(COALESCE(SUM(return_n_uv_noself) / NULLIF(SUM(is_return_noself), 0),0),6) AS rosn_real
            ,round(COALESCE(SUM(rosn_pred) / COUNT(1),0),6) AS rosn_pred
            ,round(COALESCE(SUM(rosn_stat) / COUNT(1),0),6) AS rosn_stat
            ,round(SUM(return_n_uv_noself) / COUNT(1), 6) AS rovn_real
            ,round(AVG(str_pred * rosn_pred), 6) AS rovn_pred
            ,round(AVG(str_pred * rosn_stat), 6) AS rovn_stat
            -- 误差
            ,round(AVG(ABS(rosn_pred - return_n_uv_noself)),6) AS rosn_pred_mae
            ,round(AVG(ABS(rosn_stat - return_n_uv_noself)),6) AS rosn_stat_mae
            -- 业务指标
            ,round(COALESCE(COUNT(1) / COUNT(DISTINCT mid),0),2) AS exp_per_dau
            ,round(COALESCE(SUM(is_share) / COUNT(1),0),6) AS str_one
            ,round(COALESCE(SUM(return_n_uv) / SUM(is_share),0),6) AS ros_one
            ,round(COALESCE(SUM(share_cnt) / COUNT(1),0),6) AS str
            ,round(COALESCE(SUM(return_n_uv) / SUM(share_cnt),0),6) AS ros
            ,round(COALESCE(SUM(is_return_1) / COUNT(1),0),6) AS str_plus
            ,round(COALESCE(SUM(return_n_uv) / SUM(is_return_1),0),6) AS ros_minus
            ,round(COALESCE(SUM(return_n_uv) / COUNT(1),0),6) AS rovn
            ,round(COALESCE(SUM(new_exposure_cnt) / COUNT(1),0),6) AS vovh24
            ,COUNT(DISTINCT mid) AS dau
            ,COUNT(1) AS exp
            ,COALESCE(SUM(is_share),0) AS is_share
            ,COALESCE(SUM(share_cnt),0) AS share_cnt
            ,COALESCE(SUM(is_return_1),0) AS is_return_1
            ,COALESCE(SUM(return_n_uv),0) AS return_n_uv
            ,COALESCE(SUM(new_exposure_cnt),0) AS viewh24
            ,COALESCE(SUM(return_n_uv_noself),0) AS return_n_uv_noself
    FROM    t_with_top5
    GROUP BY dt, apptype, abcode, top5_vid
    GROUPING SETS (
        (dt, apptype, abcode),
        (dt, apptype, abcode, top5_vid)
    )
    HAVING  top5_vid IS NOT NULL OR GROUPING(top5_vid) = 1
)
-- 新增：获取对照组基准值并计算变化率
,t_with_baseline AS
(
    SELECT  *
            -- 计算曝光占比
            ,round(exp * 1.0 / MAX(CASE WHEN vid = 'all' THEN exp END) OVER (PARTITION BY dt, apptype, abcode), 4) AS exp_pct
            -- 对照组基准值（业务指标）
            ,MAX(CASE WHEN abcode = '对照组' THEN exp_per_dau END) OVER (PARTITION BY dt, apptype, vid) AS exp_per_dau_base
            ,MAX(CASE WHEN abcode = '对照组' THEN str_one END) OVER (PARTITION BY dt, apptype, vid) AS str_one_base
            ,MAX(CASE WHEN abcode = '对照组' THEN ros_one END) OVER (PARTITION BY dt, apptype, vid) AS ros_one_base
            ,MAX(CASE WHEN abcode = '对照组' THEN str END) OVER (PARTITION BY dt, apptype, vid) AS str_base
            ,MAX(CASE WHEN abcode = '对照组' THEN ros END) OVER (PARTITION BY dt, apptype, vid) AS ros_base
            ,MAX(CASE WHEN abcode = '对照组' THEN str_plus END) OVER (PARTITION BY dt, apptype, vid) AS str_plus_base
            ,MAX(CASE WHEN abcode = '对照组' THEN ros_minus END) OVER (PARTITION BY dt, apptype, vid) AS ros_minus_base
            ,MAX(CASE WHEN abcode = '对照组' THEN rovn END) OVER (PARTITION BY dt, apptype, vid) AS rovn_base
            ,MAX(CASE WHEN abcode = '对照组' THEN vovh24 END) OVER (PARTITION BY dt, apptype, vid) AS vovh24_base
            -- 对照组基准值（COPC 指标）
            ,MAX(CASE WHEN abcode = '对照组' THEN str_copc END) OVER (PARTITION BY dt, apptype, vid) AS str_copc_base
            ,MAX(CASE WHEN abcode = '对照组' THEN rosn_copc END) OVER (PARTITION BY dt, apptype, vid) AS rosn_copc_base
            ,MAX(CASE WHEN abcode = '对照组' THEN rosn_stat_copc END) OVER (PARTITION BY dt, apptype, vid) AS rosn_stat_copc_base
            ,MAX(CASE WHEN abcode = '对照组' THEN rovn_copc END) OVER (PARTITION BY dt, apptype, vid) AS rovn_copc_base
            ,MAX(CASE WHEN abcode = '对照组' THEN rovn_stat_copc END) OVER (PARTITION BY dt, apptype, vid) AS rovn_stat_copc_base
            -- 对照组基准值（真实值）
            ,MAX(CASE WHEN abcode = '对照组' THEN str_real END) OVER (PARTITION BY dt, apptype, vid) AS str_real_base
            ,MAX(CASE WHEN abcode = '对照组' THEN rosn_real END) OVER (PARTITION BY dt, apptype, vid) AS rosn_real_base
            ,MAX(CASE WHEN abcode = '对照组' THEN rovn_real END) OVER (PARTITION BY dt, apptype, vid) AS rovn_real_base
            -- 对照组基准值（计数指标）
            ,MAX(CASE WHEN abcode = '对照组' THEN dau END) OVER (PARTITION BY dt, apptype, vid) AS dau_base
            ,MAX(CASE WHEN abcode = '对照组' THEN exp END) OVER (PARTITION BY dt, apptype, vid) AS exp_base
            ,MAX(CASE WHEN abcode = '对照组' THEN is_share END) OVER (PARTITION BY dt, apptype, vid) AS is_share_base
            ,MAX(CASE WHEN abcode = '对照组' THEN share_cnt END) OVER (PARTITION BY dt, apptype, vid) AS share_cnt_base
            ,MAX(CASE WHEN abcode = '对照组' THEN is_return_1 END) OVER (PARTITION BY dt, apptype, vid) AS is_return_1_base
            ,MAX(CASE WHEN abcode = '对照组' THEN return_n_uv END) OVER (PARTITION BY dt, apptype, vid) AS return_n_uv_base
            ,MAX(CASE WHEN abcode = '对照组' THEN viewh24 END) OVER (PARTITION BY dt, apptype, vid) AS viewh24_base
            ,MAX(CASE WHEN abcode = '对照组' THEN return_n_uv_noself END) OVER (PARTITION BY dt, apptype, vid) AS return_n_uv_noself_base
    FROM    t_agg
)
-- 最终输出：原有字段 + GAUC + 变化率
SELECT  a.dt
        ,a.apptype
        ,a.abcode
        ,a.vid
        ,a.vid_title
        ,a.vid_rank
        ,a.exp_pct
        ,round((a.dau - a.dau_base) / NULLIF(a.dau_base, 0), 4) AS dau_chg
        ,round((a.exp - a.exp_base) / NULLIF(a.exp_base, 0), 4) AS exp_chg
        -- COPC
        ,a.str_copc, a.rosn_copc, a.rosn_stat_copc, a.rovn_copc, a.rovn_stat_copc
        -- 模型预测与真实值
        ,a.str_real, a.str_pred, a.rosn_real, a.rosn_pred, a.rosn_stat
        ,a.rovn_real, a.rovn_pred, a.rovn_stat
        ,a.rosn_pred_mae, a.rosn_stat_mae
        -- ========== GAUC 指标（仅 vid='all' 有值）==========
        -- str gauc
        ,CASE WHEN a.vid = 'all' THEN b.str_gauc_曝光 END AS str_gauc_曝光
        ,CASE WHEN a.vid = 'all' THEN b.str_gauc_样本对 END AS str_gauc_样本对
        ,CASE WHEN a.vid = 'all' THEN b.str_gauc_等权 END AS str_gauc_等权
        -- rosn gauc
        ,CASE WHEN a.vid = 'all' THEN c.rosn_gauc_曝光 END AS rosn_gauc_曝光
        ,CASE WHEN a.vid = 'all' THEN c.rosn_gauc_样本对 END AS rosn_gauc_样本对
        ,CASE WHEN a.vid = 'all' THEN c.rosn_gauc_等权 END AS rosn_gauc_等权
        ,CASE WHEN a.vid = 'all' THEN c.rosn_stat_gauc_曝光 END AS rosn_stat_gauc_曝光
        ,CASE WHEN a.vid = 'all' THEN c.rosn_stat_gauc_样本对 END AS rosn_stat_gauc_样本对
        ,CASE WHEN a.vid = 'all' THEN c.rosn_stat_gauc_等权 END AS rosn_stat_gauc_等权
        -- rov gauc
        ,CASE WHEN a.vid = 'all' THEN b.rov_gauc_曝光 END AS rov_gauc_曝光
        ,CASE WHEN a.vid = 'all' THEN b.rov_gauc_样本对 END AS rov_gauc_样本对
        ,CASE WHEN a.vid = 'all' THEN b.rov_gauc_等权 END AS rov_gauc_等权
        ,CASE WHEN a.vid = 'all' THEN b.rov_stat_gauc_曝光 END AS rov_stat_gauc_曝光
        ,CASE WHEN a.vid = 'all' THEN b.rov_stat_gauc_样本对 END AS rov_stat_gauc_样本对
        ,CASE WHEN a.vid = 'all' THEN b.rov_stat_gauc_等权 END AS rov_stat_gauc_等权
        -- 业务指标
        ,a.exp_per_dau, a.str_one, a.ros_one, a.str, a.ros, a.str_plus, a.ros_minus, a.rovn, a.vovh24
        -- 计数
        ,a.dau, a.exp, a.is_share, a.share_cnt, a.is_return_1, a.return_n_uv, a.viewh24, a.return_n_uv_noself
        -- ========== 变化率字段 ==========
        -- 业务指标变化率
        ,round((a.exp_per_dau - a.exp_per_dau_base) / NULLIF(a.exp_per_dau_base, 0), 4) AS exp_per_dau_chg
        ,round((a.str_one - a.str_one_base) / NULLIF(a.str_one_base, 0), 4) AS str_one_chg
        ,round((a.ros_one - a.ros_one_base) / NULLIF(a.ros_one_base, 0), 4) AS ros_one_chg
        ,round((a.str - a.str_base) / NULLIF(a.str_base, 0), 4) AS str_chg
        ,round((a.ros - a.ros_base) / NULLIF(a.ros_base, 0), 4) AS ros_chg
        ,round((a.str_plus - a.str_plus_base) / NULLIF(a.str_plus_base, 0), 4) AS str_plus_chg
        ,round((a.ros_minus - a.ros_minus_base) / NULLIF(a.ros_minus_base, 0), 4) AS ros_minus_chg
        ,round((a.rovn - a.rovn_base) / NULLIF(a.rovn_base, 0), 4) AS rovn_chg
        ,round((a.vovh24 - a.vovh24_base) / NULLIF(a.vovh24_base, 0), 4) AS vovh24_chg
        -- COPC 变化率
        ,round((a.str_copc - a.str_copc_base) / NULLIF(a.str_copc_base, 0), 4) AS str_copc_chg
        ,round((a.rosn_copc - a.rosn_copc_base) / NULLIF(a.rosn_copc_base, 0), 4) AS rosn_copc_chg
        ,round((a.rosn_stat_copc - a.rosn_stat_copc_base) / NULLIF(a.rosn_stat_copc_base, 0), 4) AS rosn_stat_copc_chg
        ,round((a.rovn_copc - a.rovn_copc_base) / NULLIF(a.rovn_copc_base, 0), 4) AS rovn_copc_chg
        ,round((a.rovn_stat_copc - a.rovn_stat_copc_base) / NULLIF(a.rovn_stat_copc_base, 0), 4) AS rovn_stat_copc_chg
        -- 真实值变化率
        ,round((a.str_real - a.str_real_base) / NULLIF(a.str_real_base, 0), 4) AS str_real_chg
        ,round((a.rosn_real - a.rosn_real_base) / NULLIF(a.rosn_real_base, 0), 4) AS rosn_real_chg
        ,round((a.rovn_real - a.rovn_real_base) / NULLIF(a.rovn_real_base, 0), 4) AS rovn_real_chg
        -- 计数指标变化率
        ,round((a.is_share - a.is_share_base) / NULLIF(a.is_share_base, 0), 4) AS is_share_chg
        ,round((a.share_cnt - a.share_cnt_base) / NULLIF(a.share_cnt_base, 0), 4) AS share_cnt_chg
        ,round((a.is_return_1 - a.is_return_1_base) / NULLIF(a.is_return_1_base, 0), 4) AS is_return_1_chg
        ,round((a.return_n_uv - a.return_n_uv_base) / NULLIF(a.return_n_uv_base, 0), 4) AS return_n_uv_chg
        ,round((a.viewh24 - a.viewh24_base) / NULLIF(a.viewh24_base, 0), 4) AS viewh24_chg
        ,round((a.return_n_uv_noself - a.return_n_uv_noself_base) / NULLIF(a.return_n_uv_noself_base, 0), 4) AS return_n_uv_noself_chg
FROM    t_with_baseline a
LEFT JOIN t_gauc b
ON      a.dt = b.dt AND a.apptype = b.apptype AND a.abcode = b.abcode
LEFT JOIN t_rosn_gauc c
ON      a.dt = c.dt AND a.apptype = c.apptype AND a.abcode = c.abcode
ORDER BY a.dt DESC, a.apptype, a.abcode, a.exp DESC
;
