-- 按 topic 和 usersharedepth 分组统计
-- 使用: python fetch_daily.py tasks/00_表的洞察/loghubods.user_share_log/03_topic_depth_group.sql --date 20260107

SELECT
    dt,
    coalesce(topic, '_SUM_') as topic,
    coalesce(usersharedepth, '_SUM_') as usersharedepth,
    count(1) as cnt,
    round(count(1) * 1.0 / sum(count(1)) over(), 6) as pct_total,
    round(count(1) * 1.0 / sum(count(1)) over(partition by topic), 6) as pct_in_topic,
    count(distinct machinecode) as uv,
    round(count(distinct machinecode) * 1.0 / sum(count(distinct machinecode)) over(), 6) as uv_pct_total,
    round(count(distinct machinecode) * 1.0 / sum(count(distinct machinecode)) over(partition by topic), 6) as uv_pct_in_topic
FROM loghubods.user_share_log
WHERE dt = '${dt}'
GROUP BY dt, topic, usersharedepth
GROUPING SETS (
    (dt, topic, usersharedepth),
    (dt, topic),
    (dt)
)
ORDER BY topic, usersharedepth
LIMIT 2000
