-- click 分享者(from_mid) top1000，按带回的 target_mid 人数降序
-- from_mid = split(shareid, '-')[0]，target_mid = machinecode（排除自点）
-- 使用: python fetch_daily.py .../05_click_top_from_mid.sql --date 20260210

SELECT dt, from_mid,
       target_uv,
       ROUND(target_uv * 100.0 / SUM(target_uv) OVER(PARTITION BY dt), 2) as target_uv_pct,
       cnt,
       vid,
       max_depth, avg_depth
FROM (
    SELECT dt,
           split(shareid, '-')[0] as from_mid,
           COUNT(DISTINCT machinecode) as target_uv,
           COUNT(1) as cnt,
           COUNT(DISTINCT clickobjectid) as vid,
           MAX(CAST(usersharedepth AS BIGINT)) as max_depth,
           ROUND(AVG(CAST(usersharedepth AS BIGINT)), 2) as avg_depth,
           ROW_NUMBER() OVER(PARTITION BY dt ORDER BY COUNT(DISTINCT machinecode) DESC) as rn
    FROM loghubods.user_share_log
    WHERE dt = '${dt}' AND topic = 'click'
      AND split(shareid, '-')[0] <> machinecode  -- 排除自点
      And rootsourceid = '' OR rootsourceid IS NULL  -- 排除转发带回的
      And  
    GROUP BY dt, split(shareid, '-')[0]
) t
WHERE rn <= 1000
ORDER BY target_uv DESC
