-- 验证 opengid_base_data 和 dwd_recsys_alg_exposure_base 的关联情况（用 subsessionid join）
WITH t_head AS (
    -- 头部点击表
    SELECT  dt
            ,mid
            ,subsessionid
            ,COUNT(1) AS head_cnt
    FROM    loghubods.opengid_base_data
    WHERE   dt BETWEEN "${start}" AND "${end}"
    GROUP BY dt, mid, subsessionid
)
,t_rec AS (
    -- 推荐曝光表
    SELECT  dt
            ,mid
            ,subsessionid
            ,COUNT(1) AS rec_cnt
    FROM    loghubods.dwd_recsys_alg_exposure_base_20250108
    WHERE   dt BETWEEN "${start}" AND "${end}"
    GROUP BY dt, mid, subsessionid
)
SELECT  COALESCE(a.dt, b.dt) AS dt
        -- UV
        ,COUNT(DISTINCT COALESCE(a.mid, b.mid)) AS total_uv
        ,COUNT(DISTINCT CASE WHEN a.mid IS NOT NULL THEN a.mid END) AS head_uv
        ,COUNT(DISTINCT CASE WHEN b.mid IS NOT NULL THEN b.mid END) AS rec_uv
        ,COUNT(DISTINCT CASE WHEN a.mid IS NOT NULL AND b.mid IS NOT NULL THEN a.mid END) AS matched_uv
        -- Subsession
        ,COUNT(DISTINCT COALESCE(CONCAT(a.mid, a.subsessionid), CONCAT(b.mid, b.subsessionid))) AS total_subsessions
        ,COUNT(DISTINCT CASE WHEN a.subsessionid IS NOT NULL THEN CONCAT(a.mid, a.subsessionid) END) AS head_subsessions
        ,COUNT(DISTINCT CASE WHEN b.subsessionid IS NOT NULL THEN CONCAT(b.mid, b.subsessionid) END) AS rec_subsessions
        ,COUNT(DISTINCT CASE WHEN a.subsessionid IS NOT NULL AND b.subsessionid IS NOT NULL THEN CONCAT(a.mid, a.subsessionid) END) AS matched_subsessions
        -- 行数
        ,SUM(COALESCE(a.head_cnt, 0)) AS head_rows
        ,SUM(COALESCE(b.rec_cnt, 0)) AS rec_rows
FROM    t_head a
FULL OUTER JOIN t_rec b
ON      a.dt = b.dt
AND     a.mid = b.mid
AND     a.subsessionid = b.subsessionid
GROUP BY COALESCE(a.dt, b.dt)
ORDER BY dt
;
