🔎 查询 SQL
SQL ① 各服务大盘曝光/点击 & CTR 基线
-- 数据源:数仓 dws_log_domain_guid_extension_impress_kpis_d / _click_kpis_d
-- 时间窗口:近30天 2026-05-13 ~ 2026-06-11
SELECT impress_type AS svc, scene, SUM(impress_cnt) AS impr
FROM dws_log_domain_guid_extension_impress_kpis_d
WHERE impress_date BETWEEN '2026-05-13' AND '2026-06-11'
AND (scene LIKE '%Inject%' OR scene LIKE 'FloatWin%' OR scene LIKE '%Ball%')
GROUP BY impress_type, scene;
-- 点击同理:_click_kpis_d / SUM(click_cnt) / click_date / click_type
-- 大盘CTR = SUM(click_cnt) / SUM(impress_cnt) (按服务聚合)
SQL ② 每日曝光/点击时间序列(本看板)
SELECT 'impr' AS k, CAST(impress_date AS STRING) AS d, impress_type AS svc, scene, SUM(impress_cnt) AS cnt
FROM dws_log_domain_guid_extension_impress_kpis_d
WHERE impress_date BETWEEN '2026-05-13' AND '2026-06-11'
AND (scene LIKE '%Inject%' OR scene LIKE 'FloatWin%' OR scene LIKE '%Ball%')
GROUP BY impress_date, impress_type, scene
UNION ALL
SELECT 'click', CAST(click_date AS STRING), click_type, scene, SUM(click_cnt)
FROM dws_log_domain_guid_extension_click_kpis_d
WHERE click_date BETWEEN '2026-05-13' AND '2026-06-11'
AND (scene LIKE '%Inject%' OR scene LIKE 'FloatWin%' OR scene LIKE '%Ball%')
GROUP BY click_date, click_type, scene;
-- 之后按 (impress_type, scene) 归类到:inject_CB/AT/PCP/Vio、ball_self(XF)、ballsvc_*(CC/AT/CB/PCP/UGC)