-- 用 subsessionid join，按内外部分组统计 UV 情况
WITH t_head AS (
    SELECT  dt
            ,mid
            ,subsessionid
            ,CASE WHEN rootsourceid = '' OR rootsourceid IS NULL THEN '内部' ELSE '外部' END AS in_out
            ,COUNT(1) AS head_cnt
    FROM    loghubods.opengid_base_data
    WHERE   dt BETWEEN "${start}" AND "${end}"
    GROUP BY dt, mid, subsessionid
            ,CASE WHEN rootsourceid = '' OR rootsourceid IS NULL THEN '内部' ELSE '外部' END
)
,t_rec AS (
    SELECT  dt
            ,mid
            ,subsessionid
            ,CASE WHEN rootsourceid = '' OR rootsourceid IS NULL THEN '内部' ELSE '外部' END AS in_out
            ,COUNT(1) AS rec_cnt
    FROM    loghubods.dwd_recsys_alg_exposure_base_20250108
    WHERE   dt BETWEEN "${start}" AND "${end}"
    GROUP BY dt, mid, subsessionid
            ,CASE WHEN rootsourceid = '' OR rootsourceid IS NULL THEN '内部' ELSE '外部' END
)
SELECT  COALESCE(a.dt, b.dt) AS dt
        ,COALESCE(a.in_out, b.in_out) AS in_out
        -- 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
AND     a.in_out = b.in_out
GROUP BY COALESCE(a.dt, b.dt)
        ,COALESCE(a.in_out, b.in_out)
ORDER BY dt, in_out
;
