-- 验证：scoresmap 各关键字段在每个 (apptype, suffix_group) 的写入次数
-- 目的：直接证伪/证实"01/67 尾号有 newPLeave"是不是 565/566 改动溢出
--   - 看每个尾号桶 has_new_pleave / total 比例
--   - 横向对比：哪些字段在哪些桶被写入，模式如何
WITH t_suffix_group AS
(
    SELECT 'a' AS suffix, 'ab' AS suffix_group
    UNION ALL SELECT 'b', 'ab'
    UNION ALL SELECT '0', '01'
    UNION ALL SELECT '1', '01'
    UNION ALL SELECT '2', '2c'
    UNION ALL SELECT 'c', '2c'
    UNION ALL SELECT '3', '34'
    UNION ALL SELECT '4', '34'
    UNION ALL SELECT '5', '5d'
    UNION ALL SELECT 'd', '5d'
    UNION ALL SELECT '6', '67'
    UNION ALL SELECT '7', '67'
    UNION ALL SELECT '8', '89'
    UNION ALL SELECT '9', '89'
    UNION ALL SELECT 'e', 'ef'
    UNION ALL SELECT 'f', 'ef'
)
,t_base AS
(
    SELECT  apptype
            ,SUBSTR(GET_JSON_OBJECT(extend,'$.rootsessionid'),
                    LENGTH(GET_JSON_OBJECT(extend,'$.rootsessionid')),1) AS suffix
            ,REPLACE(GET_JSON_OBJECT(extend_alg,'$.scoresMap'),"\\","") AS scoresmap
    FROM    loghubods.dwd_recsys_alg_sample_all_20250212
    WHERE   dt = '${dt}'
    AND     apptype IN ('0','4')
    AND     extend_alg IS NOT NULL
    AND     page IN ("回流后沉浸页&内页feed","详情后沉浸页","首页feed","详情页")
)
SELECT  a.apptype
        ,sg.suffix_group
        ,COUNT(1) AS exp
        ,SUM(CASE WHEN GET_JSON_OBJECT(scoresmap,'$.fmRov')        IS NOT NULL THEN 1 ELSE 0 END) AS has_fmrov
        ,SUM(CASE WHEN GET_JSON_OBJECT(scoresmap,'$.NorXGBScore')  IS NOT NULL THEN 1 ELSE 0 END) AS has_xgb
        ,SUM(CASE WHEN GET_JSON_OBJECT(scoresmap,'$.NorDNNScore')  IS NOT NULL THEN 1 ELSE 0 END) AS has_dnn
        ,SUM(CASE WHEN GET_JSON_OBJECT(scoresmap,'$.pLeave')       IS NOT NULL THEN 1 ELSE 0 END) AS has_pleave
        ,SUM(CASE WHEN GET_JSON_OBJECT(scoresmap,'$.newPLeave')    IS NOT NULL THEN 1 ELSE 0 END) AS has_new_pleave
        ,SUM(CASE WHEN GET_JSON_OBJECT(scoresmap,'$.cnRovnScore')  IS NOT NULL THEN 1 ELSE 0 END) AS has_cn
        ,SUM(CASE WHEN GET_JSON_OBJECT(scoresmap,'$.dnRovnScore')  IS NOT NULL THEN 1 ELSE 0 END) AS has_dn
FROM    t_base a
INNER JOIN t_suffix_group sg
ON      a.suffix = sg.suffix
GROUP BY a.apptype, sg.suffix_group
ORDER BY a.apptype, sg.suffix_group
;
