-- 排查内部流量关联不上头部的原因
-- 分析 headvideoid 是否在头部表中存在
WITH t_rec AS (
    SELECT  dt, mid, subsessionid, headvideoid, vid, ts
            ,rootsourceid
            ,CASE WHEN rootsourceid = '' OR rootsourceid IS NULL THEN '内部' ELSE '外部' END AS in_out
    FROM    loghubods.dwd_recsys_alg_exposure_base_20250108
    WHERE   dt = "${start}"
    AND     apptype IN ('4','0')
    AND     page IN ("回流后沉浸页&内页feed","详情后沉浸页","首页feed","详情页")
    AND     (rootsourceid = '' OR rootsourceid IS NULL)  -- 只看内部
)
,t_head AS (
    SELECT  dt, mid, subsessionid, videoid
            ,UNIX_TIMESTAMP(`点击时间`) AS click_ts
    FROM    loghubods.opengid_base_data
    WHERE   dt = "${start}"
)
-- 分析 headvideoid 情况
,t_headvid_analysis AS (
    SELECT  CASE
                WHEN headvideoid IS NULL OR headvideoid = '' THEN 'headvideoid为空'
                ELSE 'headvideoid有值'
            END AS headvid_status
            ,COUNT(1) AS exp_cnt
            ,COUNT(DISTINCT mid) AS uv
    FROM    t_rec
    GROUP BY CASE
                WHEN headvideoid IS NULL OR headvideoid = '' THEN 'headvideoid为空'
                ELSE 'headvideoid有值'
            END
)
-- headvideoid 有值的情况下，分析关联情况
,t_rec_with_headvid AS (
    SELECT  r.*
    FROM    t_rec r
    WHERE   r.headvideoid IS NOT NULL AND r.headvideoid <> ''
)
,t_join_analysis AS (
    SELECT  r.mid, r.subsessionid, r.headvideoid, r.vid, r.ts
            ,CASE
                WHEN h1.videoid IS NOT NULL THEN '1_mid+subsession+vid匹配'
                WHEN h2.videoid IS NOT NULL THEN '2_仅mid+vid匹配'
                WHEN h3.videoid IS NOT NULL THEN '3_仅vid匹配'
                ELSE '4_完全不匹配'
            END AS match_status
            ,h1.click_ts AS ts1
    FROM    t_rec_with_headvid r
    LEFT JOIN t_head h1
    ON      r.dt = h1.dt AND r.mid = h1.mid AND r.subsessionid = h1.subsessionid
    AND     r.headvideoid = h1.videoid
    AND     h1.click_ts <= CAST(r.ts AS BIGINT)
    LEFT JOIN (SELECT DISTINCT dt, mid, videoid FROM t_head) h2
    ON      r.dt = h2.dt AND r.mid = h2.mid AND r.headvideoid = h2.videoid
    LEFT JOIN (SELECT DISTINCT dt, videoid FROM t_head) h3
    ON      r.dt = h3.dt AND r.headvideoid = h3.videoid
)
,t_match_summary AS (
    SELECT  match_status, COUNT(1) AS exp_cnt
    FROM    t_join_analysis
    GROUP BY match_status
)

SELECT '0_headvideoid分布' AS analysis, headvid_status AS status, exp_cnt, uv
FROM t_headvid_analysis
UNION ALL
SELECT '1_关联情况分析' AS analysis, match_status AS status, exp_cnt, NULL AS uv
FROM t_match_summary
ORDER BY analysis, status
;
