playlet/sql/central/clickhouse_central_update_20241010.sql

create table tb_promotion_report
(
    company          String  default '' comment '广告平台',
    advertiser_id    String  default '' comment '广告主id',
    gid              String  default '' comment '广告组id',
    aid              String  default '' comment '广告id',
    cid              String  default '' comment '广告创意id',
    cost             Float64 default 0 comment '消耗（元）',
    show             Int32   default 0 comment '展现（次）',
    click            Int32   default 0 comment '点击（次）',
    convert          Int32   default 0 comment '转化（个）',
    convert_cost     Float64 default 0 comment '每个转化所付出的平均成本（元）',
    avg_show_cost    Float64 default 0 comment '平均千次展现费用（元）',
    avg_click_cost   Float64 default 0 comment '平均点击单价',
    time_granularity String  default '' comment '时间维度DAILY或HOURLY',
    time_number      Int64   default 0 comment '数据时间yyyyMMdd或yyyyMMddHH',
    add_timestamp    Int64   default 0 comment '数据插入时间'
)
    engine = ReplacingMergeTree
        PARTITION BY time_number
        ORDER BY (company, advertiser_id, gid, aid, cid, time_granularity, time_number)
        SETTINGS index_granularity = 8192;

create table tb_promotion_report_rolling
(
    company          String  default '' comment '广告平台',
    advertiser_id    String  default '' comment '广告主id',
    gid              String  default '' comment '广告组id',
    aid              String  default '' comment '广告id',
    cid              String  default '' comment '广告创意id',
    cost             Float64 default 0 comment '消耗（元）',
    show             Int32   default 0 comment '展现（次）',
    click            Int32   default 0 comment '点击（次）',
    convert          Int32   default 0 comment '转化（个）',
    convert_cost     Float64 default 0 comment '每个转化所付出的平均成本（元）',
    avg_show_cost    Float64 default 0 comment '平均千次展现费用（元）',
    avg_click_cost   Float64 default 0 comment '平均点击单价',
    time_granularity String  default '' comment '时间维度DAILY或HOURLY',
    time_number      Int64   default 0 comment '数据时间yyyyMMdd或yyyyMMddHH',
    add_timestamp    Int64   default 0 comment '数据插入时间'
)
    engine = ReplacingMergeTree
        PARTITION BY time_number
        ORDER BY (company, advertiser_id, gid, aid, cid, time_granularity, time_number)
        TTL toDateTime(add_timestamp / 1000) + interval 3 day
        SETTINGS index_granularity = 8192;