-- 排查：各实验组 scoresMap 中字段缺失情况
-- 检查 fmRov 和 NorXGBScore 是否存在及有效

SELECT  dt
        ,abcode
        ,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
        -- 总量
        ,COUNT(1) AS total_cnt
        ,COUNT(DISTINCT mid) AS total_dau
        -- scoresMap 存在
        ,SUM(CASE WHEN scoresmap IS NOT NULL THEN 1 ELSE 0 END) AS has_scoresmap
        -- fmRov 字段检查
        ,SUM(CASE WHEN GET_JSON_OBJECT(scoresmap,'$.fmRov') IS NOT NULL THEN 1 ELSE 0 END) AS has_fmRov
        ,SUM(CASE WHEN CAST(GET_JSON_OBJECT(scoresmap,'$.fmRov') AS DOUBLE) IS NOT NULL THEN 1 ELSE 0 END) AS valid_fmRov
        -- NorXGBScore 字段检查
        ,SUM(CASE WHEN GET_JSON_OBJECT(scoresmap,'$.NorXGBScore') IS NOT NULL THEN 1 ELSE 0 END) AS has_NorXGBScore
        ,SUM(CASE WHEN CAST(GET_JSON_OBJECT(scoresmap,'$.NorXGBScore') AS DOUBLE) IS NOT NULL THEN 1 ELSE 0 END) AS valid_NorXGBScore
        -- 两个字段都有效
        ,SUM(CASE WHEN CAST(GET_JSON_OBJECT(scoresmap,'$.fmRov') AS DOUBLE) IS NOT NULL
                  AND CAST(GET_JSON_OBJECT(scoresmap,'$.NorXGBScore') AS DOUBLE) IS NOT NULL
             THEN 1 ELSE 0 END) AS both_valid
        -- 两个字段都有效 + 推荐页面（最终有效数据）
        ,SUM(CASE WHEN CAST(GET_JSON_OBJECT(scoresmap,'$.fmRov') AS DOUBLE) IS NOT NULL
                  AND CAST(GET_JSON_OBJECT(scoresmap,'$.NorXGBScore') AS DOUBLE) IS NOT NULL
                  AND page IN ('回流后沉浸页&内页feed','详情后沉浸页','首页feed','详情页')
             THEN 1 ELSE 0 END) AS final_valid
        ,COUNT(DISTINCT CASE WHEN CAST(GET_JSON_OBJECT(scoresmap,'$.fmRov') AS DOUBLE) IS NOT NULL
                  AND CAST(GET_JSON_OBJECT(scoresmap,'$.NorXGBScore') AS DOUBLE) IS NOT NULL
                  AND page IN ('回流后沉浸页&内页feed','详情后沉浸页','首页feed','详情页')
             THEN mid END) AS final_dau
FROM    (
    SELECT  *
            ,REPLACE(GET_JSON_OBJECT(extend_alg,'$.scoresMap'),"\\","") AS scoresmap
    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')
) t
GROUP BY dt, abcode
ORDER BY abcode
;
