create table tb_promotion_advertiser_report ( app_code String default '' comment '应用编码', company String default '' comment '广告平台', advertiser_id 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, time_granularity, time_number) SETTINGS index_granularity = 8192; create table tb_promotion_advertiser_report_rolling ( app_code String default '' comment '应用编码', company String default '' comment '广告平台', advertiser_id 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, time_granularity, time_number) TTL toDateTime(add_timestamp / 1000) + toIntervalDay(3) SETTINGS index_granularity = 8192;