-- 品类命中分析
-- 分析推荐品类是否命中用户历史品类
-- 横行：命中、未命中、无历史品类
-- 纵列：再分享品类

-- 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_list,
        concat_ws(',', collect_set(history_cat1)) AS history_cat1_str
    FROM user_cate1_exploded
    WHERE history_cat1 IS NOT NULL
    GROUP BY mid
)

-- Step 2.1: 用户一级品类频次
,user_cate1_freq AS (
    SELECT
        mid,
        concat_ws('|', collect_list(concat(history_cat1, ':', cast(cnt as string)))) AS history_cat1_freq
    FROM (
        SELECT mid, history_cat1, count(*) AS cnt
        FROM user_cate1_exploded
        WHERE history_cat1 IS NOT NULL
        GROUP BY mid, history_cat1
    ) t
    GROUP BY mid
)

-- Step 3: 解析用户二级品类历史
,user_cate2_exploded AS (
    SELECT
        mid,
        get_json_object(json_obj, '$.na') AS history_cat2
    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, '$.c2_s'),
                            '\\\\\"', '\"'
                        ),
                        '^\\[|\\]$', ''
                    ),
                    '\\},\\{', '}|{'
                ),
                '\\|'
            )
        ) t AS json_piece
        WHERE dt = '${end}'
        AND get_json_object(feature, '$.c2_s') IS NOT NULL
        AND get_json_object(feature, '$.c2_s') != '[]'
    ) exploded
    WHERE json_obj IS NOT NULL AND json_obj != ''
)

-- Step 4: 用户二级品类去重列表（含频次）
,user_cate2_list AS (
    SELECT
        mid,
        collect_set(history_cat2) AS history_cat2_list,
        concat_ws(',', collect_set(history_cat2)) AS history_cat2_str
    FROM user_cate2_exploded
    WHERE history_cat2 IS NOT NULL
    GROUP BY mid
)

-- Step 4.1: 用户二级品类频次
,user_cate2_freq AS (
    SELECT
        mid,
        concat_ws('|', collect_list(concat(history_cat2, ':', cast(cnt as string)))) AS history_cat2_freq
    FROM (
        SELECT mid, history_cat2, count(*) AS cnt
        FROM user_cate2_exploded
        WHERE history_cat2 IS NOT NULL
        GROUP BY mid, history_cat2
    ) t
    GROUP BY mid
)

-- Step 5: 基础数据
,base_data AS (
    SELECT
        dt,
        channel,
        mid,
        再分享merge一级品类 AS 再分享一级品类,
        再分享merge二级品类 AS 再分享二级品类,
        再分享群聊回流uv,
        再分享单聊回流uv,
        是否原视频
    FROM loghubods.opengid_base_data
    WHERE dt >= ${start}
    AND dt <= ${end}
    AND usersharedepth = 0
    AND videoid IS NOT NULL
)

-- Step 6: Join 并判断命中情况
,joined_data AS (
    SELECT
        a.dt,
        a.channel,
        a.mid,
        a.再分享一级品类,
        a.再分享二级品类,
        a.再分享群聊回流uv,
        a.再分享单聊回流uv,
        a.是否原视频,
        b.history_cat1_list,
        b.history_cat1_str,
        c.history_cat2_list,
        c.history_cat2_str,
        d.history_cat1_freq,
        e.history_cat2_freq,
        -- 一级品类命中情况
        CASE
            WHEN b.history_cat1_list IS NULL THEN '无历史'
            WHEN array_contains(b.history_cat1_list, a.再分享一级品类) THEN '命中'
            ELSE '未命中'
        END AS cat1_match,
        -- 二级品类命中情况
        CASE
            WHEN c.history_cat2_list IS NULL THEN '无历史'
            WHEN array_contains(c.history_cat2_list, a.再分享二级品类) THEN '命中'
            ELSE '未命中'
        END AS cat2_match
    FROM base_data a
    LEFT JOIN user_cate1_list b ON a.mid = b.mid
    LEFT JOIN user_cate2_list c ON a.mid = c.mid
    LEFT JOIN user_cate1_freq d ON a.mid = d.mid
    LEFT JOIN user_cate2_freq e ON a.mid = e.mid
)

-- Step 7: 输出明细（保留中间结果）
SELECT
    dt,
    channel,
    再分享一级品类,
    再分享二级品类,
    cat1_match AS 一级品类命中,
    cat2_match AS 二级品类命中,
    COUNT(DISTINCT mid) AS 点击uv,
    SUM(CASE WHEN 再分享群聊回流uv > 0 THEN 再分享群聊回流uv ELSE 0 END)
     + SUM(CASE WHEN 再分享单聊回流uv > 0 THEN 再分享单聊回流uv ELSE 0 END) AS 裂变uv,
    (SUM(CASE WHEN 再分享群聊回流uv > 0 THEN 再分享群聊回流uv ELSE 0 END)
     + SUM(CASE WHEN 再分享单聊回流uv > 0 THEN 再分享单聊回流uv ELSE 0 END)
    ) / (COUNT(DISTINCT mid) + 10) AS 整体裂变率,
    (SUM(CASE WHEN 是否原视频 = '是' THEN 再分享群聊回流uv ELSE 0 END)
     + SUM(CASE WHEN 是否原视频 = '是' THEN 再分享单聊回流uv ELSE 0 END)
    ) / (COUNT(DISTINCT mid) + 10) AS 头部裂变率,
    (SUM(CASE WHEN 是否原视频 = '否' THEN 再分享群聊回流uv ELSE 0 END)
     + SUM(CASE WHEN 是否原视频 = '否' THEN 再分享单聊回流uv ELSE 0 END)
    ) / (COUNT(DISTINCT mid) + 10) AS 推荐裂变率
FROM joined_data
GROUP BY dt, channel, 再分享一级品类, 再分享二级品类, cat1_match, cat2_match
ORDER BY dt, channel, 点击uv DESC
;
