-- 排查不同 key 组合的重复情况
WITH t_head AS (
    SELECT  dt, mid, sessionid, subsessionid, videoid
    FROM    loghubods.opengid_base_data
    WHERE   dt = "${start}"
)
-- 1. (mid, subsessionid, videoid) 重复
SELECT  '1_(mid,subsessionid,videoid)' AS key_combo
        ,COUNT(1) AS dup_key_cnt
        ,SUM(cnt) AS total_rows
        ,SUM(cnt - 1) AS extra_rows
FROM    (
            SELECT  mid, subsessionid, videoid, COUNT(1) AS cnt
            FROM    t_head
            GROUP BY mid, subsessionid, videoid
            HAVING  COUNT(1) > 1
        )

UNION ALL

-- 2. (mid, sessionid, subsessionid, videoid) 重复
SELECT  '2_(mid,sessionid,subsessionid,videoid)' AS key_combo
        ,COUNT(1) AS dup_key_cnt
        ,SUM(cnt) AS total_rows
        ,SUM(cnt - 1) AS extra_rows
FROM    (
            SELECT  mid, sessionid, subsessionid, videoid, COUNT(1) AS cnt
            FROM    t_head
            GROUP BY mid, sessionid, subsessionid, videoid
            HAVING  COUNT(1) > 1
        )

UNION ALL

-- 3. 头部表总行数 vs 去重后行数
SELECT  '3_头部表总量' AS key_combo
        ,COUNT(1) AS dup_key_cnt
        ,CAST(NULL AS BIGINT) AS total_rows
        ,CAST(NULL AS BIGINT) AS extra_rows
FROM    t_head

UNION ALL

SELECT  '4_头部表去重(mid,subsessionid,videoid)' AS key_combo
        ,COUNT(1) AS dup_key_cnt
        ,CAST(NULL AS BIGINT) AS total_rows
        ,CAST(NULL AS BIGINT) AS extra_rows
FROM    (
            SELECT  mid, subsessionid, videoid
            FROM    t_head
            GROUP BY mid, subsessionid, videoid
        )
;
