-- 新旧规则准确率对比
SELECT  shareid_type
       ,total
       ,old_match
       ,new_match
       ,old_mismatch
       ,new_mismatch
       ,ROUND(old_match * 100.0 / total, 4) AS old_rate
       ,ROUND(new_match * 100.0 / total, 4) AS new_rate
FROM (
    SELECT  CASE WHEN c.shareid LIKE 'weixin_openid%' THEN 'weixin_openid'
                 WHEN c.shareid LIKE 'frontend%'      THEN 'frontend'
                 WHEN c.shareid LIKE 'app_share_id%'  THEN 'app_share_id'
                 ELSE 'other'
            END AS shareid_type
           ,COUNT(1) AS total
           ,SUM(CASE WHEN REGEXP_REPLACE(c.shareid, '-[^-]+-[^-]+$', '') = s.mid THEN 1 ELSE 0 END) AS old_match
           ,SUM(CASE WHEN REGEXP_REPLACE(c.shareid, '(-[0-9a-f]{4})?-[0-9]{13}[0-9]*$', '') = s.mid THEN 1 ELSE 0 END) AS new_match
           ,SUM(CASE WHEN REGEXP_REPLACE(c.shareid, '-[^-]+-[^-]+$', '') <> s.mid THEN 1 ELSE 0 END) AS old_mismatch
           ,SUM(CASE WHEN REGEXP_REPLACE(c.shareid, '(-[0-9a-f]{4})?-[0-9]{13}[0-9]*$', '') <> s.mid THEN 1 ELSE 0 END) AS new_mismatch
    FROM    loghubods.user_share_log c
    JOIN (
        SELECT  shareid, MAX(machinecode) AS mid
        FROM    loghubods.user_share_log
        WHERE   dt = '${dt}' AND topic = 'share'
        GROUP BY shareid
    ) s ON c.shareid = s.shareid
    WHERE   c.dt = '${dt}'
    AND     c.topic = 'click'
    GROUP BY
            CASE WHEN c.shareid LIKE 'weixin_openid%' THEN 'weixin_openid'
                 WHEN c.shareid LIKE 'frontend%'      THEN 'frontend'
                 WHEN c.shareid LIKE 'app_share_id%'  THEN 'app_share_id'
                 ELSE 'other'
            END
) t
ORDER BY total DESC
;
