
-- 活跃表 ods_user_active_log_info_day 基础概况
-- 按 push_type / channel_type 分组统计 UV & CNT，对比 share_log click UV

-- 1. 按 push_type 分组（含合计）
SELECT dt, 'push_type' AS dim, COALESCE(push_type, '合计') AS val,
       COUNT(DISTINCT machinecode) AS uv, COUNT(1) AS cnt
FROM loghubods.ods_user_active_log_info_day
WHERE dt = '${dt}'
GROUP BY dt, push_type
GROUPING SETS ((dt, push_type), (dt))

UNION ALL

-- 2. 按 channel_type 分组
SELECT dt, 'channel_type' AS dim, channel_type AS val,
       COUNT(DISTINCT machinecode) AS uv, COUNT(1) AS cnt
FROM loghubods.ods_user_active_log_info_day
WHERE dt = '${dt}'
GROUP BY dt, channel_type

UNION ALL

-- 3. share_log click UV 做对比
SELECT dt, 'share_log' AS dim, 'click_uv' AS val,
       COUNT(DISTINCT machinecode) AS uv, COUNT(1) AS cnt
FROM loghubods.user_share_log
WHERE dt = '${dt}' AND topic = 'click'
GROUP BY dt
