-- 排查：所有实验组在指定日期的 dau 异常
-- 逐层检查数据量变化，定位问题环节

-- 1. 原始数据层：检查所有实验组的基础数据量
SELECT  '1_原始数据' AS step
        ,dt
        ,CASE   WHEN abcode IN ('ab0','ab1') THEN '实验组-先验地域降权'
                WHEN abcode IN ('ab6','ab7') THEN '实验组-str+校准&ros-统计量'
                WHEN abcode IN ('ab8','ab9') THEN '实验组-str+校准'
                WHEN abcode IN ('ab2','ab3') THEN '对照组'
                WHEN abcode IN ('ab4','ab5') THEN 'ab4-5'
        END AS abcode_group
        ,abcode
        ,COUNT(1) AS row_cnt
        ,COUNT(DISTINCT mid) AS dau
        ,COUNT(DISTINCT vid) AS vid_cnt
FROM    loghubods.dwd_recsys_alg_sample_all_20250212
WHERE   dt = '${dt}'
AND     apptype = '4'
AND     abcode IN ('ab0','ab1','ab2','ab3','ab4','ab5','ab6','ab7','ab8','ab9')
GROUP BY dt, abcode
ORDER BY abcode_group, abcode

UNION ALL

-- 2. 过滤 extend_alg 后
SELECT  '2_有extend_alg' AS step
        ,dt
        ,CASE   WHEN abcode IN ('ab0','ab1') THEN '实验组-先验地域降权'
                WHEN abcode IN ('ab6','ab7') THEN '实验组-str+校准&ros-统计量'
                WHEN abcode IN ('ab8','ab9') THEN '实验组-str+校准'
                WHEN abcode IN ('ab2','ab3') THEN '对照组'
                WHEN abcode IN ('ab4','ab5') THEN 'ab4-5'
        END AS abcode_group
        ,abcode
        ,COUNT(1) AS row_cnt
        ,COUNT(DISTINCT mid) AS dau
        ,COUNT(DISTINCT vid) AS vid_cnt
FROM    loghubods.dwd_recsys_alg_sample_all_20250212
WHERE   dt = '${dt}'
AND     apptype = '4'
AND     abcode IN ('ab0','ab1','ab2','ab3','ab4','ab5','ab6','ab7','ab8','ab9')
AND     extend_alg IS NOT NULL
GROUP BY dt, abcode
ORDER BY abcode_group, abcode

UNION ALL

-- 3. 过滤 scoresMap 后
SELECT  '3_有scoresMap' AS step
        ,dt
        ,CASE   WHEN abcode IN ('ab0','ab1') THEN '实验组-先验地域降权'
                WHEN abcode IN ('ab6','ab7') THEN '实验组-str+校准&ros-统计量'
                WHEN abcode IN ('ab8','ab9') THEN '实验组-str+校准'
                WHEN abcode IN ('ab2','ab3') THEN '对照组'
                WHEN abcode IN ('ab4','ab5') THEN 'ab4-5'
        END AS abcode_group
        ,abcode
        ,COUNT(1) AS row_cnt
        ,COUNT(DISTINCT mid) AS dau
        ,COUNT(DISTINCT vid) AS vid_cnt
FROM    loghubods.dwd_recsys_alg_sample_all_20250212
WHERE   dt = '${dt}'
AND     apptype = '4'
AND     abcode IN ('ab0','ab1','ab2','ab3','ab4','ab5','ab6','ab7','ab8','ab9')
AND     extend_alg IS NOT NULL
AND     GET_JSON_OBJECT(extend_alg,'$.scoresMap') IS NOT NULL
GROUP BY dt, abcode
ORDER BY abcode_group, abcode

UNION ALL

-- 4. 过滤推荐页面后
SELECT  '4_推荐页面' AS step
        ,dt
        ,CASE   WHEN abcode IN ('ab0','ab1') THEN '实验组-先验地域降权'
                WHEN abcode IN ('ab6','ab7') THEN '实验组-str+校准&ros-统计量'
                WHEN abcode IN ('ab8','ab9') THEN '实验组-str+校准'
                WHEN abcode IN ('ab2','ab3') THEN '对照组'
                WHEN abcode IN ('ab4','ab5') THEN 'ab4-5'
        END AS abcode_group
        ,abcode
        ,COUNT(1) AS row_cnt
        ,COUNT(DISTINCT mid) AS dau
        ,COUNT(DISTINCT vid) AS vid_cnt
FROM    loghubods.dwd_recsys_alg_sample_all_20250212
WHERE   dt = '${dt}'
AND     apptype = '4'
AND     abcode IN ('ab0','ab1','ab2','ab3','ab4','ab5','ab6','ab7','ab8','ab9')
AND     extend_alg IS NOT NULL
AND     GET_JSON_OBJECT(extend_alg,'$.scoresMap') IS NOT NULL
AND     page IN ('回流后沉浸页&内页feed', '详情后沉浸页', '首页feed', '详情页')
GROUP BY dt, abcode
ORDER BY abcode_group, abcode

UNION ALL

-- 5. 过滤 str_pred/rosn_pred 非空后
SELECT  '5_模型分数有效' AS step
        ,dt
        ,CASE   WHEN abcode IN ('ab0','ab1') THEN '实验组-先验地域降权'
                WHEN abcode IN ('ab6','ab7') THEN '实验组-str+校准&ros-统计量'
                WHEN abcode IN ('ab8','ab9') THEN '实验组-str+校准'
                WHEN abcode IN ('ab2','ab3') THEN '对照组'
                WHEN abcode IN ('ab4','ab5') THEN 'ab4-5'
        END AS abcode_group
        ,abcode
        ,COUNT(1) AS row_cnt
        ,COUNT(DISTINCT mid) AS dau
        ,COUNT(DISTINCT vid) AS vid_cnt
FROM    (
    SELECT  dt, abcode, mid, vid
            ,CAST(GET_JSON_OBJECT(
                REPLACE(GET_JSON_OBJECT(extend_alg,'$.scoresMap'),"\\",""),
                '$.fmRov'
            ) AS DOUBLE) AS str_pred
            ,CAST(GET_JSON_OBJECT(
                REPLACE(GET_JSON_OBJECT(extend_alg,'$.scoresMap'),"\\",""),
                '$.NorXGBScore'
            ) AS DOUBLE) AS rosn_pred_raw
    FROM    loghubods.dwd_recsys_alg_sample_all_20250212
    WHERE   dt = '${dt}'
    AND     apptype = '4'
    AND     abcode IN ('ab0','ab1','ab2','ab3','ab4','ab5','ab6','ab7','ab8','ab9')
    AND     extend_alg IS NOT NULL
    AND     GET_JSON_OBJECT(extend_alg,'$.scoresMap') IS NOT NULL
    AND     page IN ('回流后沉浸页&内页feed', '详情后沉浸页', '首页feed', '详情页')
) t
WHERE   str_pred IS NOT NULL
AND     rosn_pred_raw IS NOT NULL
GROUP BY dt, abcode
ORDER BY abcode_group, abcode
;
