playlet/sql/central/clickhouse_central_update_20250120.sql

create table tb_promotion_creative_report
(
    app_code                        String  default '' comment '应用编码',
    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',
    time_granularity                String  default '' comment '时间维度DAILY或HOURLY',
    time_number                     Int64   default 0 comment '数据时间yyyyMMdd或yyyyMMddHH',
    add_timestamp                   Int64   default 0 comment '数据插入时间',
    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 '平均点击单价',
    dau                             Int32   default 0 comment '日活跃用户数',
    reg                             Int32   default 0 comment '新增用户数',
    st_activate                     Int32   default 0 comment '标准_激活数',
    st_first_pay_count              Int32   default 0 comment '标准_首次付费数',
    st_first_pay_amount             Float64 default 0 comment '标准_首次付费金额',
    st_first_day_pay_count          Int32   default 0 comment '标准_首日付费次数',
    st_first_day_pay_user           Int32   default 0 comment '标准_首日付费人数',
    st_first_day_pay_amount         Float64 default 0 comment '标准_首日付费金额',
    st_total_pay_count              Int32   default 0 comment '标准_首日付费金额',
    st_total_pay_user               Int32   default 0 comment '标准_总付费人数',
    st_total_pay_amount             Float64 default 0 comment '标准_总付费金额',
    transfer_count                  Int32   default 0 comment '提现次数',
    transfer_user                   Int32   default 0 comment '提现人数',
    transfer_total_amount           Float64 default 0 comment '提现总金额',
    rewarded_video_ad_count         Int32   default 0 comment '激励视频_广告总数',
    rewarded_video_ad_income        Float64 default 0 comment '激励视频_广告收入',
    rewarded_video_ad_monetize_user Int32   default 0 comment '激励视频_广告变现人数',
    rewarded_video_ad_ecpm          Float64 default 0 comment '激励视频_广告ECPM',
    banner_ad_count                 Int32   default 0 comment '横幅_广告总数',
    banner_ad_income                Float64 default 0 comment '横幅_广告收入',
    banner_ad_monetize_user         Int32   default 0 comment '横幅_广告变现人数',
    banner_ad_ecpm                  Float64 default 0 comment '横幅_广告ECPM',
    feeds_ad_count                  Int32   default 0 comment '信息流_广告总数',
    feeds_ad_income                 Float64 default 0 comment '信息流_广告收入',
    feeds_ad_monetize_user          Int32   default 0 comment '信息流_广告变现人数',
    feeds_ad_ecpm                   Float64 default 0 comment '信息流_广告ECPM',
    interstitial_ad_count           Int32   default 0 comment '插屏_广告总数',
    interstitial_ad_income          Float64 default 0 comment '插屏_广告收入',
    interstitial_ad_monetize_user   Int32   default 0 comment '插屏_广告变现人数',
    interstitial_ad_ecpm            Float64 default 0 comment '插屏_广告ECPM',
    splash_ad_count                 Int32   default 0 comment '开屏_广告总数',
    splash_ad_income                Float64 default 0 comment '开屏_广告收入',
    splash_ad_monetize_user         Int32   default 0 comment '开屏_广告变现人数',
    splash_ad_ecpm                  Float64 default 0 comment '开屏_广告ECPM',
    first_day_ad_income             Float64 default 0 comment '首日广告收入',
    total_ad_income                 Float64 default 0 comment '总广告收入'
) engine = ReplacingMergeTree PARTITION BY time_number
    ORDER BY (app_code, company, advertiser_id,gid,aid,cid, time_granularity, time_number)
    SETTINGS index_granularity = 8192;

create table tb_promotion_creative_report_rolling
(
    app_code                        String  default '' comment '应用编码',
    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',
    time_granularity                String  default '' comment '时间维度DAILY或HOURLY',
    time_number                     Int64   default 0 comment '数据时间yyyyMMdd或yyyyMMddHH',
    add_timestamp                   Int64   default 0 comment '数据插入时间',
    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 '平均点击单价',
    dau                             Int32   default 0 comment '日活跃用户数',
    reg                             Int32   default 0 comment '新增用户数',
    st_activate                     Int32   default 0 comment '标准_激活数',
    st_first_pay_count              Int32   default 0 comment '标准_首次付费数',
    st_first_pay_amount             Float64 default 0 comment '标准_首次付费金额',
    st_first_day_pay_count          Int32   default 0 comment '标准_首日付费次数',
    st_first_day_pay_user           Int32   default 0 comment '标准_首日付费人数',
    st_first_day_pay_amount         Float64 default 0 comment '标准_首日付费金额',
    st_total_pay_count              Int32   default 0 comment '标准_首日付费金额',
    st_total_pay_user               Int32   default 0 comment '标准_总付费人数',
    st_total_pay_amount             Float64 default 0 comment '标准_总付费金额',
    transfer_count                  Int32   default 0 comment '提现次数',
    transfer_user                   Int32   default 0 comment '提现人数',
    transfer_total_amount           Float64 default 0 comment '提现总金额',
    rewarded_video_ad_count         Int32   default 0 comment '激励视频_广告总数',
    rewarded_video_ad_income        Float64 default 0 comment '激励视频_广告收入',
    rewarded_video_ad_monetize_user Int32   default 0 comment '激励视频_广告变现人数',
    rewarded_video_ad_ecpm          Float64 default 0 comment '激励视频_广告ECPM',
    banner_ad_count                 Int32   default 0 comment '横幅_广告总数',
    banner_ad_income                Float64 default 0 comment '横幅_广告收入',
    banner_ad_monetize_user         Int32   default 0 comment '横幅_广告变现人数',
    banner_ad_ecpm                  Float64 default 0 comment '横幅_广告ECPM',
    feeds_ad_count                  Int32   default 0 comment '信息流_广告总数',
    feeds_ad_income                 Float64 default 0 comment '信息流_广告收入',
    feeds_ad_monetize_user          Int32   default 0 comment '信息流_广告变现人数',
    feeds_ad_ecpm                   Float64 default 0 comment '信息流_广告ECPM',
    interstitial_ad_count           Int32   default 0 comment '插屏_广告总数',
    interstitial_ad_income          Float64 default 0 comment '插屏_广告收入',
    interstitial_ad_monetize_user   Int32   default 0 comment '插屏_广告变现人数',
    interstitial_ad_ecpm            Float64 default 0 comment '插屏_广告ECPM',
    splash_ad_count                 Int32   default 0 comment '开屏_广告总数',
    splash_ad_income                Float64 default 0 comment '开屏_广告收入',
    splash_ad_monetize_user         Int32   default 0 comment '开屏_广告变现人数',
    splash_ad_ecpm                  Float64 default 0 comment '开屏_广告ECPM',
    first_day_ad_income             Float64 default 0 comment '首日广告收入',
    total_ad_income                 Float64 default 0 comment '总广告收入'
) engine = ReplacingMergeTree PARTITION BY time_number
    ORDER BY (app_code, company, advertiser_id,gid,aid,cid, time_granularity, time_number)
    TTL toDateTime(add_timestamp / 1000) + toIntervalDay(3)
    SETTINGS index_granularity = 8192;


create table tb_promotion_creative_report_temp
(
    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_creative_report_rolling_temp
(
    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;