-- 排查头部表 (subsessionid, videoid) 是否有重复
WITH t_head AS (
    SELECT  dt, mid, subsessionid, videoid
    FROM    loghubods.opengid_base_data
    WHERE   dt = "${start}"
)
SELECT  '头部表(subsessionid,videoid)重复' AS check_type
        ,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
        )
;
