-- 品类命中分析 - Debug版本
-- 输出中间结果，方便检查命中逻辑

-- Step 1: 解析用户一级品类历史
WITH user_cate1_exploded AS (
    SELECT
        mid,
        get_json_object(json_obj, '$.na') AS history_cat1
    FROM (
        SELECT
            mid,
            json_piece AS json_obj
        FROM loghubods.alg_recsys_feature_user_share_return_stat
        LATERAL VIEW explode(
            split(
                regexp_replace(
                    regexp_replace(
                        regexp_replace(
                            get_json_object(feature, '$.c1_s'),
                            '\\\\\"', '\"'
                        ),
                        '^\\[|\\]$', ''
                    ),
                    '\\},\\{', '}|{'
                ),
                '\\|'
            )
        ) t AS json_piece
        WHERE dt = '${end}'
        AND get_json_object(feature, '$.c1_s') IS NOT NULL
        AND get_json_object(feature, '$.c1_s') != '[]'
    ) exploded
    WHERE json_obj IS NOT NULL AND json_obj != ''
)

-- Step 2: 用户一级品类去重列表（转为字符串方便查看）
,user_cate1_list AS (
    SELECT
        mid,
        collect_set(history_cat1) AS history_cat1_array,
        concat_ws(',', collect_set(history_cat1)) AS history_cat1_str
    FROM user_cate1_exploded
    WHERE history_cat1 IS NOT NULL
    GROUP BY mid
)

-- Step 3: 基础数据（只取样本）
,base_data AS (
    SELECT
        dt,
        channel,
        mid,
        再分享merge一级品类 AS 再分享一级品类,
        再分享merge二级品类 AS 再分享二级品类,
        再分享群聊回流uv,
        再分享单聊回流uv
    FROM loghubods.opengid_base_data
    WHERE dt = ${end}
    AND usersharedepth = 0
    AND videoid IS NOT NULL
    AND 再分享merge一级品类 IS NOT NULL
)

-- Step 4: Join 并输出详细信息
SELECT
    a.dt,
    a.channel,
    a.mid,
    a.再分享一级品类,
    b.history_cat1_str AS 用户历史一级品类,
    CASE
        WHEN b.history_cat1_array IS NULL THEN '无历史'
        WHEN array_contains(b.history_cat1_array, a.再分享一级品类) THEN '命中'
        ELSE '未命中'
    END AS 一级品类命中,
    -- 调试信息
    CASE WHEN b.history_cat1_array IS NULL THEN 0 ELSE size(b.history_cat1_array) END AS 历史品类数量,
    a.再分享群聊回流uv + a.再分享单聊回流uv AS 裂变uv
FROM base_data a
LEFT JOIN user_cate1_list b ON a.mid = b.mid
ORDER BY a.channel, a.mid
LIMIT 1000
;
