-- 排查加上 shareid 后的重复情况
WITH t_head AS (
    SELECT  dt, mid, sessionid, subsessionid, videoid, shareid, `点击时间`
    FROM    loghubods.opengid_base_data
    WHERE   dt = "${start}"
)
-- 1. (mid, subsessionid, videoid, shareid) 重复
SELECT  '1_(mid,subsessionid,videoid,shareid)' AS key_combo
        ,COUNT(1) AS dup_key_cnt
        ,SUM(cnt) AS total_rows
FROM    (
            SELECT  mid, subsessionid, videoid, shareid, COUNT(1) AS cnt
            FROM    t_head
            GROUP BY mid, subsessionid, videoid, shareid
            HAVING  COUNT(1) > 1
        )

UNION ALL

-- 2. shareid 是否唯一
SELECT  '2_shareid单独' AS key_combo
        ,COUNT(1) AS dup_key_cnt
        ,SUM(cnt) AS total_rows
FROM    (
            SELECT  shareid, COUNT(1) AS cnt
            FROM    t_head
            WHERE   shareid IS NOT NULL AND shareid <> ''
            GROUP BY shareid
            HAVING  COUNT(1) > 1
        )

UNION ALL

-- 3. shareid 为空的比例
SELECT  '3_shareid为空' AS key_combo
        ,COUNT(1) AS dup_key_cnt
        ,CAST(NULL AS BIGINT) AS total_rows
FROM    t_head
WHERE   shareid IS NULL OR shareid = ''

UNION ALL

-- 4. 头部表总行数
SELECT  '4_头部表总量' AS key_combo
        ,COUNT(1) AS dup_key_cnt
        ,CAST(NULL AS BIGINT) AS total_rows
FROM    t_head
;
