eCPM （effictive cost per miile）=出价*CTR*CVR*1000

CREATE TABLE fact_ad_events (
    event_time     DATETIME NOT NULL,
    ad_id          BIGINT,
    creative_id    BIGINT,
    campaign_id    BIGINT,
    creative_version STRING,
    channel        VARCHAR(64),
    region         VARCHAR(32),
    user_tag       VARCHAR(128),     -- 人群标签（兴趣/年龄/性别/LTV分层等）
    event_type     VARCHAR(20),      -- 'show'  'click'  'convert'
    cost           DECIMAL(18,6),
    user_id        BIGINT
)
DUPLICATE KEY(event_time, ad_id, creative_id)
PARTITION BY RANGE (event_time) ()
DISTRIBUTED BY HASH(ad_id) BUCKETS 64
PROPERTIES ("replication_num" = "3");

CREATE MATERIALIZED VIEW agg_ad_realtime
PARTITION BY date_trunc('minute', event_time)
DISTRIBUTED BY HASH(creative_id)
REFRESH ASYNC EVERY (INTERVAL 1 MINUTE)
AS
SELECT
    date_trunc('minute', event_time)          AS event_minute,
    event_time,                               -- 必须保留原始列，让分区能找到
    ad_id,
    creative_id,
    creative_version,
    channel,
    region,
    user_tag,
    COUNT_IF(event_type = 'show')             AS impression_count,
    COUNT_IF(event_type = 'click')            AS click_count,
    COUNT_IF(event_type = 'convert')          AS convert_count,
    SUM(cost)                                 AS cost
FROM fact_ad_events
GROUP BY
    date_trunc('minute', event_time),
    event_time,                               -- 对应上面保留的列
    ad_id,
    creative_id,
    creative_version,
    channel,
    region,
    user_tag;

WITH ranked_new_material AS (
    SELECT
        creative_id,
        creative_version,
        channel,
        user_tag,
        SUM(click_count) AS click_counts,
        SUM(impression_count) AS impression_count,
        ROUND(SUM(click_count) * 100.0 / NULLIF(SUM(impression_count), 0), 2) AS ctr_percent,
        RANK() OVER (PARTITION BY channel ORDER BY
            ROUND(SUM(click_count) * 100.0 / NULLIF(SUM(impression_count), 0), 2) DESC
        ) AS ctr_rank
    FROM agg_ad_realtime
    WHERE  event_minute >= NOW() - INTERVAL 1 HOUR
    AND creative_version LIKE '%new%'   -- 新素材标识
    GROUP BY creative_id, creative_version, channel, user_tag
    HAVING SUM(impression_count) >= 10   -- 先过滤低曝光素材，提升性能
)
SELECT
    creative_id,
    creative_version,
    channel,
    user_tag,
    impression_count,
    ctr_percent,
    ctr_rank
FROM ranked_new_material
WHERE ctr_rank <= 5
  AND ctr_percent >= 4.0 AND impression_count >= 20;
ORDER BY channel, ctr_rank; -- Top5 且 CTR≥4%（行业可调）, 且曝光至少300才参与排名

SELECT 
    event_minute,
    creative_id,
    creative_version,
    channel,
    region,
    user_tag,
    impression_count,
    click_count,
    ROUND(click_count * 100.0 / NULLIF(impression_count, 0), 2) AS ctr_percent,
    ROUND(convert_count * 100.0 / NULLIF(click_count, 0), 2)     AS cvr_percent
FROM agg_ad_realtime
WHERE event_minute >= NOW() - INTERVAL 60 MINUTE
ORDER BY event_minute DESC, ctr_percent DESC;

DEMO 执行示意

WITH ctr_trend AS (
    SELECT creative_id, creative_version,
           ROUND(click_count * 100.0 / NULLIF(impression_count, 0), 2) AS ctr_percent,
           AVG(ROUND(click_count * 100.0 / NULLIF(impression_count, 0), 2))
               OVER (PARTITION BY creative_id ORDER BY event_minute ROWS BETWEEN 10 PRECEDING AND CURRENT ROW) AS moving_avg
    FROM agg_ad_realtime
    WHERE event_minute >= NOW() - INTERVAL 30 MINUTE
)
SELECT * FROM ctr_trend
WHERE (moving_avg - ctr_percent) / moving_avg > 0.20
ORDER BY (moving_avg - ctr_percent) DESC;

SELECT creative_id,
       creative_version,
       impression_count,
       click_count,
       ROUND(click_count * 100.0 / NULLIF(impression_count, 0), 2) AS ctr_percent
FROM agg_ad_realtime
WHERE impression_count > 10000
  AND click_count < 50
  AND event_minute >= NOW() - INTERVAL 10 MINUTE;

CREATE MATERIALIZED VIEW agg_ad_abtest
PARTITION BY event_minute                      -- 直接用别名分区（3.1+ 支持）
DISTRIBUTED BY HASH(creative_id, creative_version)
REFRESH ASYNC EVERY (INTERVAL 1 MINUTE)
AS
SELECT
    date_trunc('minute', event_time)          AS event_minute,
    campaign_id,
    creative_id,
    creative_version,
    channel,
    region,
    user_tag,
    COUNT_IF(event_type = 'show')             AS impression_count,
    COUNT_IF(event_type = 'click')            AS click_count,
    COUNT_IF(event_type = 'convert')          AS convert_count
FROM fact_ad_events
GROUP BY
    date_trunc('minute', event_time),         -- 对应 event_minute
    creative_id,
    creative_version,
    campaign_id,
    channel,
    region,
    user_tag;

SELECT 
    creative_version,
    channel,
    region,
    user_tag,
    SUM(impression_count) AS impressions,
    SUM(click_count) AS clicks,
    ROUND(SUM(click_count) * 100.0 / NULLIF(SUM(impression_count), 0), 2) AS ctr_percent,
    ROUND(SUM(convert_count) * 100.0 / NULLIF(SUM(click_count), 0), 2) AS cvr_percent
FROM agg_ad_abtest
WHERE event_minute >= NOW() - INTERVAL 24 HOUR
GROUP BY creative_version, channel, region, user_tag
ORDER BY ctr_percent DESC;

WITH stats AS (
    SELECT creative_version,
           SUM(impression_count) AS imp,
           SUM(click_count) AS clk
    FROM agg_ad_abtest
    WHERE campaign_id = 1234
      AND creative_version IN  ('V1_old','V1_new','V2_old','V2_new')
      AND event_minute >= NOW() - INTERVAL 12 HOUR
    GROUP BY creative_version
)
SELECT a.creative_version AS winner, b.creative_version AS loser,
       ROUND(a.clk*100.0/a.imp,2) AS ctr_a,
       ROUND(b.clk*100.0/b.imp,2) AS ctr_b,
       POWER(a.clk - a.imp*p,2)/(a.imp*p) + POWER(b.clk - b.imp*p,2)/(b.imp*p) AS chi_square
FROM stats a CROSS JOIN stats b
CROSS JOIN (SELECT SUM(clk)*1.0/SUM(imp) AS p FROM stats) total
WHERE a.creative_version != b.creative_version
HAVING chi_square > 0.003 AND ctr_a > ctr_b;

SELECT 
  HOUR(event_time) as hour_of_day,
  creative_version,
  COUNT(CASE WHEN event_type='impression' THEN 1 END) as impressions,
  COUNT(CASE WHEN event_type='click' THEN 1 END) as clicks,
  ROUND(clicks * 100.0 / NULLIF(impressions, 0), 2) as ctr_percent
FROM fact_ad_events
WHERE campaign_id = 1234
  AND event_time >= date_sub(NOW(), INTERVAL 7 DAY)
GROUP BY hour_of_day, creative_version
ORDER BY hour_of_day, creative_version;

Demo执行示意