-- 用时间条件关联：头部点击时间 < 曝光ts，取最接近的一条
-- 验证是否解决膨胀问题

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","详情页")
)
,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 r
    LEFT JOIN t_vid_info v ON r.vid = v.vid
)
,t_head AS (
    SELECT  dt, mid, subsessionid, videoid
            ,UNIX_TIMESTAMP(`点击时间`) AS click_ts
            ,`merge二级品类` AS head_cate2
    FROM    loghubods.opengid_base_data
    WHERE   dt = "${start}"
)
-- 原始曝光数
,t_origin_cnt AS (
    SELECT  '1_原始曝光' AS step, COUNT(1) AS exp
    FROM    t_rec_with_cate
    WHERE   in_out = '内部' AND rec_cate2 = '人生忠告'
)
-- 关联：点击时间 < 曝光ts，取最接近的一条
,t_joined_with_time AS (
    SELECT  r.*
            ,h.videoid AS head_vid
            ,h.head_cate2
            ,h.click_ts
            ,ROW_NUMBER() OVER (
                PARTITION BY r.mid, r.subsessionid, r.headvideoid, r.vid, r.ts
                ORDER BY h.click_ts DESC  -- 取最接近的（最大的小于ts的）
            ) AS rn
    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
    AND     r.headvideoid = h.videoid
    AND     h.click_ts < CAST(r.ts AS BIGINT)  -- 点击时间 < 曝光时间
    WHERE   r.in_out = '内部' AND r.rec_cate2 = '人生忠告'
)
,t_joined_dedup AS (
    SELECT  * FROM t_joined_with_time WHERE rn = 1
)
,t_joined_cnt AS (
    SELECT  '2_Join(点击时间<ts,取最近)' AS step, COUNT(1) AS exp
    FROM    t_joined_dedup
)

SELECT * FROM t_origin_cnt
UNION ALL
SELECT * FROM t_joined_cnt
;
