-- 排查 join 后数据膨胀问题
-- 对比原始曝光表和 join 后的数量

-- 1. 原始曝光表：内部+人生忠告的 exp
WITH t_rec_origin AS (
    SELECT  dt
            ,mid
            ,subsessionid
            ,vid
            ,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","详情页")
)
,t_vid_info AS (
    SELECT  vid
            ,COALESCE(GET_JSON_OBJECT(feature,"$.merge_second_level_cate"),"unknown") AS rec_cate2
    FROM    (
                SELECT  vid, feature
                        ,ROW_NUMBER() OVER (PARTITION BY vid ORDER BY dt DESC,hh DESC ) AS rn
                FROM    loghubods.alg_vid_feature_basic_info
                WHERE   CONCAT(dt,hh) BETWEEN CONCAT("${start}","00") AND CONCAT("${start}","23")
            )
    WHERE   rn = 1
)
,t_rec_with_cate AS (
    SELECT  r.*, v.rec_cate2
    FROM    t_rec_origin r
    LEFT JOIN t_vid_info v ON r.vid = v.vid
)
-- 原始曝光数
,t_origin_cnt AS (
    SELECT  '1_原始曝光' AS step
            ,in_out
            ,rec_cate2
            ,COUNT(1) AS exp
            ,COUNT(DISTINCT CONCAT(mid, subsessionid)) AS subsession_cnt
    FROM    t_rec_with_cate
    WHERE   in_out = '内部'
    AND     rec_cate2 = '人生忠告'
    GROUP BY in_out, rec_cate2
)

-- 2. 头部表：每个 subsessionid 有多少条记录？
,t_head AS (
    SELECT  dt, mid, subsessionid, videoid, rootsourceid
            ,CASE WHEN rootsourceid = '' OR rootsourceid IS NULL THEN '内部' ELSE '外部' END AS head_in_out
    FROM    loghubods.opengid_base_data
    WHERE   dt = "${start}"
)
,t_head_dup AS (
    SELECT  '2_头部表重复' AS step
            ,mid
            ,subsessionid
            ,COUNT(1) AS head_cnt
    FROM    t_head
    GROUP BY mid, subsessionid
    HAVING  COUNT(1) > 1
)
,t_head_dup_stat AS (
    SELECT  '2_头部表重复统计' AS step
            ,CAST(NULL AS STRING) AS in_out
            ,CAST(NULL AS STRING) AS rec_cate2
            ,COUNT(1) AS exp  -- 有重复的 subsession 数
            ,SUM(head_cnt) AS subsession_cnt  -- 总重复行数
    FROM    t_head_dup
)

-- 3. Join 后的数量
,t_joined AS (
    SELECT  r.mid
            ,r.subsessionid
            ,r.vid
            ,r.in_out
            ,r.rec_cate2
            ,h.videoid AS head_vid
    FROM    t_rec_with_cate r
    LEFT JOIN t_head h
    ON      r.dt = h.dt
    AND     r.mid = h.mid
    AND     r.subsessionid = h.subsessionid
    WHERE   r.in_out = '内部'
    AND     r.rec_cate2 = '人生忠告'
)
,t_joined_cnt AS (
    SELECT  '3_Join后' AS step
            ,in_out
            ,rec_cate2
            ,COUNT(1) AS exp
            ,COUNT(DISTINCT CONCAT(mid, subsessionid)) AS subsession_cnt
    FROM    t_joined
    GROUP BY in_out, rec_cate2
)

-- 汇总输出
SELECT * FROM t_origin_cnt
UNION ALL
SELECT * FROM t_head_dup_stat
UNION ALL
SELECT * FROM t_joined_cnt
;
