-- 测试放宽条件：mid + subsessionid 匹配，取最近头部
WITH t_rec AS (
    SELECT  dt, mid, subsessionid, headvideoid, vid, ts
    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
            ,`merge二级品类` AS head_cate2
    FROM    loghubods.opengid_base_data
    WHERE   dt = "${start}"
)
-- 方案1：当前（mid+subsession+headvideoid+时间）
,t_v1 AS (
    SELECT  r.mid, r.subsessionid, r.vid, r.ts
            ,ROW_NUMBER() OVER (
                PARTITION BY r.mid, r.subsessionid, r.vid, r.ts
                ORDER BY h.click_ts DESC
            ) AS rn
            ,h.videoid AS head_vid
    FROM    t_rec 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)
)
,t_cnt_v1 AS (
    SELECT  '1_当前(+headvideoid)' AS step
            ,COUNT(1) AS exp
            ,SUM(CASE WHEN head_vid IS NOT NULL THEN 1 ELSE 0 END) AS matched
    FROM    t_v1 WHERE rn = 1
)
-- 方案2：放宽（mid+subsession+时间，不要headvideoid）
,t_v2 AS (
    SELECT  r.mid, r.subsessionid, r.vid, r.ts
            ,ROW_NUMBER() OVER (
                PARTITION BY r.mid, r.subsessionid, r.vid, r.ts
                ORDER BY h.click_ts DESC
            ) AS rn
            ,h.videoid AS head_vid
    FROM    t_rec r
    LEFT JOIN t_head h
    ON      r.dt = h.dt AND r.mid = h.mid AND r.subsessionid = h.subsessionid
    AND     h.click_ts <= CAST(r.ts AS BIGINT)
)
,t_cnt_v2 AS (
    SELECT  '2_放宽(无headvideoid)' AS step
            ,COUNT(1) AS exp
            ,SUM(CASE WHEN head_vid IS NOT NULL THEN 1 ELSE 0 END) AS matched
    FROM    t_v2 WHERE rn = 1
)

SELECT * FROM t_cnt_v1
UNION ALL SELECT * FROM t_cnt_v2
;
