-- 对比 v1（严格）和 v2（放宽）的关联率
WITH t_head AS (
    SELECT  dt, mid, subsessionid, videoid AS head_vid
            ,UNIX_TIMESTAMP(`点击时间`) AS click_ts
    FROM    loghubods.opengid_base_data
    WHERE   dt BETWEEN "${start}" AND "${end}"
)
,t_rec AS (
    SELECT  dt, mid, subsessionid, headvideoid, vid, ts
            ,rootsourceid
            ,CASE WHEN rootsourceid = '' OR rootsourceid IS NULL THEN '内部' ELSE '外部' END AS in_out
            ,GET_JSON_OBJECT(extend,"$.extParams.userShareDepth") AS layer
    FROM    loghubods.dwd_recsys_alg_exposure_base_20250108
    WHERE   dt BETWEEN "${start}" AND "${end}"
    AND     apptype IN ('4','0')
    AND     page IN ("回流后沉浸页&内页feed","详情后沉浸页","首页feed","详情页")
)
,t_rec_with_crowd AS (
    SELECT  r.*
            ,CASE   WHEN in_out = '内部' THEN '内部'
                    WHEN layer = '0' THEN '外部0层'
                    WHEN CAST(layer AS INT) > 0 THEN '外部裂变'
                    ELSE '其他'
            END AS crowd
    FROM    t_rec r
)
-- v1: 严格条件（mid + subsessionid + headvideoid + 时间）
,t_v1 AS (
    SELECT  r.crowd, 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.head_vid
    FROM    t_rec_with_crowd 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.head_vid
    AND     h.click_ts <= CAST(r.ts AS BIGINT)
    WHERE   r.crowd <> '其他'
)
,t_v1_stats AS (
    SELECT  crowd
            ,'v1_严格' AS version
            ,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
    GROUP BY crowd
)
-- v2: 放宽条件（mid + subsessionid + 时间，无headvideoid）
,t_v2 AS (
    SELECT  r.crowd, 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.head_vid
    FROM    t_rec_with_crowd 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)
    WHERE   r.crowd <> '其他'
)
,t_v2_stats AS (
    SELECT  crowd
            ,'v2_放宽' AS version
            ,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
    GROUP BY crowd
)

SELECT  crowd, version, exp, matched
        ,ROUND(matched / exp, 4) AS match_rate
FROM    t_v1_stats
UNION ALL
SELECT  crowd, version, exp, matched
        ,ROUND(matched / exp, 4) AS match_rate
FROM    t_v2_stats
ORDER BY crowd, version
;
