sql求优化建议

建表语句，完整sql，explain结果都可以贴一下，大概率耗在磁盘扫描读取

这种没有吗

CREATE TABLE ads_iad_adx_stat_core_di (
flight_id bigint NULL COMMENT '广告位ID',
dsp_id bigint NULL COMMENT 'DSP ID',
ad_type int NULL COMMENT '1：合约，2：竞价',
ad_deal bigint NULL COMMENT 'deal id',
schedule_uid bigint NULL COMMENT '排期ID',
api_type int NULL COMMENT '1:实时,2:预取,3:批量',
template_id bigint NULL COMMENT '模板ID',
vip_type int NULL COMMENT '会员类型：0非会员，其他会员',
ad_id bigint NULL COMMENT '广告ID',
maisui_sponsor_id bigint NULL COMMENT '麦穗广告主ID',
maisui_sponsor_group_id bigint NULL COMMENT '麦穗广告主分组ID',
incentive_source int NULL COMMENT '激励拉活入口来源',
fetch_type int NULL COMMENT '素材获取类型：0：客户端曝光点击的素材是实时素材,1：客户端曝光点击的素材是客户端预取的RTB缓存素材,2：客户端曝光点击的素材是客户端实时请求到的RTB缓存素材,3：日志对应的素材是DSP缓存素材',
gray_release int NULL COMMENT '是否灰度流量标',
device_type int NULL COMMENT '0:all,1:phone,2:pad,3:pc,4:tv,5:unknown',
mix_rank_type int NULL COMMENT '混排实验类型：1进入混排实验',
mix_rank_result int NULL COMMENT '混排结果：1混排抢量成功',
invocation_style int NULL COMMENT '交互类型',
actual_inv_style int NULL COMMENT '下发的交互类型',
final_inv_style int NULL COMMENT '客户端实际交互类型',
media_sponsor_id bigint NULL COMMENT '媒体上的广告主账号ID',
schedule_traffic_type int NULL COMMENT '排期流量类型。1：PDB, 2:抄底, 3:PD',
dsp_resp_code bigint NULL COMMENT 'dsp响应码',
boss_account_type int NULL COMMENT '老板账号类型',
app_version varchar(65533) NULL COMMENT 'APP 版本',
rank_priority varchar(65533) NULL COMMENT '流量分配类型',
material_id varchar(65533) NULL COMMENT '素材ID',
hit_vip_crowd_ids varchar(65533) NULL COMMENT '命中人群包',
os varchar(65533) NULL COMMENT '系统。android/ios',
abtestids varchar(65533) NULL COMMENT 'ab实验ID集合',
media_id varchar(65533) NULL COMMENT '媒体编码',
dt date NULL COMMENT '日志日期',
log_time_minute datetime NULL COMMENT '分钟粒度的日志时间',
log_time_hour datetime NULL COMMENT '小时粒度的日志时间',
media_flight_request_ads bigint SUM NULL DEFAULT "0",
exposure bigint SUM NULL DEFAULT "0" COMMENT '曝光',
click bigint SUM NULL DEFAULT "0",
dsp_resp_ads_win bigint SUM NULL DEFAULT "0",
dsp_bid_participate bigint SUM NULL DEFAULT "0",
dsp_request bigint SUM NULL DEFAULT "0",
dsp_resp bigint SUM NULL DEFAULT "0",
cpm_bid_by_fen bigint SUM NULL DEFAULT "0",
cpm_price_by_fen bigint SUM NULL DEFAULT "0",
deal_request bigint SUM NULL DEFAULT "0",
deal_resp bigint SUM NULL DEFAULT "0",
preempt bigint SUM NULL DEFAULT "0",
cpm_preempt bigint SUM NULL DEFAULT "0",
rank_bid_ads_cpm_bid_price_by_fen bigint SUM NULL DEFAULT "0",
rank_bid_ads_cpm_win_price_by_fen bigint SUM NULL DEFAULT "0",
dsp_resp_timeout bigint SUM NULL DEFAULT "0",
dsp_resp_exception bigint SUM NULL DEFAULT "0",
dsp_resp_parse_exception bigint SUM NULL DEFAULT "0",
cache_dsp_resp_win bigint SUM NULL DEFAULT "0",
cache_dsp_resp_ads_win bigint SUM NULL DEFAULT "0",
count bigint SUM NULL DEFAULT "0"
) ENGINE=OLAP
AGGREGATE KEY(flight_id, dsp_id, ad_type, ad_deal, schedule_uid, api_type, template_id, vip_type, ad_id, maisui_sponsor_id, maisui_sponsor_group_id, incentive_source, fetch_type, gray_release, device_type, mix_rank_type, mix_rank_result, invocation_style, actual_inv_style, final_inv_style, media_sponsor_id, schedule_traffic_type, dsp_resp_code, boss_account_type, app_version, rank_priority, material_id, hit_vip_crowd_ids, os, abtestids, media_id, dt, log_time_minute, log_time_hour)
PARTITION BY RANGE(dt)
(PARTITION p20241023 VALUES [('2024-10-23'), ('2024-10-24')),
PARTITION p20241024 VALUES [('2024-10-24'), ('2024-10-25')),
PARTITION p20241025 VALUES [('2024-10-25'), ('2024-10-26')),
PARTITION p20241026 VALUES [('2024-10-26'), ('2024-10-27')),
PARTITION p20241027 VALUES [('2024-10-27'), ('2024-10-28')),
PARTITION p20241028 VALUES [('2024-10-28'), ('2024-10-29')),
PARTITION p20241029 VALUES [('2024-10-29'), ('2024-10-30')),
PARTITION p20241030 VALUES [('2024-10-30'), ('2024-10-31')),
PARTITION p20241031 VALUES [('2024-10-31'), ('2024-11-01')))
DISTRIBUTED BY HASH(flight_id, dsp_id, ad_type, ad_deal, schedule_uid, api_type, template_id, vip_type, ad_id, maisui_sponsor_id, maisui_sponsor_group_id, incentive_source, fetch_type, gray_release, device_type, mix_rank_type, mix_rank_result, invocation_style, actual_inv_style, final_inv_style, media_sponsor_id, schedule_traffic_type, dsp_resp_code, boss_account_type, app_version, rank_priority, material_id, hit_vip_crowd_ids, os, abtestids, media_id, dt, log_time_minute, log_time_hour) BUCKETS 320
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "DAY",
"dynamic_partition.time_zone" = "Asia/Shanghai",
"dynamic_partition.start" = "-100",
"dynamic_partition.end" = "2",
"dynamic_partition.prefix" = "p",
"dynamic_partition.replicati

CREATE TABLE dim_iad_flight_base_dd (
flight_id varchar(65533) NOT NULL COMMENT '广告位ID',
flight_name varchar(65533) NULL COMMENT '广告位名称',
media_code varchar(65533) NULL COMMENT '媒体编码',
platform varchar(65533) NULL COMMENT '客户端的平台(ios/android)',
status int NULL COMMENT '模板ID',
is_adaptable int NULL COMMENT '是否支持广告自适应',
version bigint NULL COMMENT '数据版本号',
deletion_flag int NULL COMMENT '删除标识。1：未删除，0：已删除'
) ENGINE=OLAP
UNIQUE KEY(flight_id)
DISTRIBUTED BY HASH(flight_id) BUCKETS 1
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V1",
"enable_unique_key_merge_on_write" = "true",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728",
"enable_mow_light_delete" = "false"
);

CREATE VIEW v_ads_iad_adx_stat_core_di AS SELECT
internal.music_iad.a.flight_id,
internal.music_iad.a.dsp_id,
internal.music_iad.c.ad_type,
internal.music_iad.a.ad_deal,
internal.music_iad.e.schedule_uid,
internal.music_iad.a.api_type,
internal.music_iad.a.template_id,
internal.music_iad.a.vip_type,
internal.music_iad.b.ad_id,
internal.music_iad.c.advertiser_id as maisui_sponsor_id,
d.sponsor_group_id as maisui_sponsor_group_id,
internal.music_iad.a.incentive_source,
internal.music_iad.a.fetch_type,
internal.music_iad.a.gray_release,
internal.music_iad.f.platform as device_type,
internal.music_iad.a.mix_rank_type,
internal.music_iad.a.mix_rank_result,
internal.music_iad.a.invocation_style,
internal.music_iad.a.actual_inv_style,
internal.music_iad.a.final_inv_style,
internal.music_iad.a.media_sponsor_id,
internal.music_iad.e.schedule_traffic_type,
internal.music_iad.a.dsp_resp_code,
internal.music_iad.a.boss_account_type,
internal.music_iad.a.app_version,
internal.music_iad.a.rank_priority,
internal.music_iad.a.material_id,
internal.music_iad.a.hit_vip_crowd_ids,
internal.music_iad.a.os,
internal.music_iad.a.abtestids,
internal.music_iad.f.media_code as media_id,
internal.music_iad.a.dt,
internal.music_iad.a.log_time_minute,
internal.music_iad.a.log_time_hour,
internal.music_iad.a.media_flight_request_ads,
internal.music_iad.a.exposure,
internal.music_iad.a.click,
internal.music_iad.a.dsp_resp_ads_win,
case when internal.music_iad.e.schedule_traffic_type in (1,2) and internal.music_iad.a.dsp_resp_ads_win > 0 then internal.music_iad.a.dsp_resp_ads_win else internal.music_iad.a.dsp_bid_participate end as dsp_bid_participate,
internal.music_iad.a.dsp_request,
internal.music_iad.a.dsp_resp,
internal.music_iad.a.cpm_bid_by_fen,
internal.music_iad.a.cpm_price_by_fen,
internal.music_iad.a.deal_request,
internal.music_iad.a.deal_resp,
internal.music_iad.a.preempt,
internal.music_iad.a.cpm_preempt,
internal.music_iad.a.rank_bid_ads_cpm_bid_price_by_fen,
internal.music_iad.a.rank_bid_ads_cpm_win_price_by_fen,
internal.music_iad.a.dsp_resp_timeout,
internal.music_iad.a.dsp_resp_exception,
internal.music_iad.a.dsp_resp_parse_exception,
internal.music_iad.a.cache_dsp_resp_win,
internal.music_iad.a.cache_dsp_resp_ads_win,
internal.music_iad.a.count
from internal.music_iad.ads_iad_adx_stat_core_di a
left join [broadcast](
select internal.music_iad.dim_iad_material_base_dd.material_id, internal.music_iad.dim_iad_material_base_dd.ad_id from internal.music_iad.dim_iad_material_base_dd
) b on internal.music_iad.a.material_id=internal.music_iad.b.material_id
left join [broadcast](
select internal.music_iad.dim_iad_adform_base_dd.ad_id, internal.music_iad.dim_iad_adform_base_dd.ad_type, internal.music_iad.dim_iad_adform_base_dd.advertiser_id from internal.music_iad.dim_iad_adform_base_dd
) c on internal.music_iad.b.ad_id=cast(internal.music_iad.c.ad_id as string)
left join [broadcast](
select internal.music_iad.dim_iad_sponsor_base_dd.sponsor_id, internal.music_iad.dim_iad_sponsor_base_dd.related_uid as sponsor_group_id from internal.music_iad.dim_iad_sponsor_base_dd
) d on internal.music_iad.c.advertiser_id=internal.music_iad.d.sponsor_id
left join [broadcast](
select internal.music_iad.dim_iad_deal_base_dd.deal_id, internal.music_iad.dim_iad_deal_base_dd.schedule_uid, internal.music_iad.dim_iad_deal_base_dd.schedule_traffic_type from internal.music_iad.dim_iad_deal_base_dd
) e on internal.music_iad.a.ad_deal=internal.music_iad.e.deal_id
left join [broadcast](
select internal.music_iad.dim_iad_flight_base_dd.flight_id, internal.music_iad.dim_iad_flight_base_dd.media_code, internal.music_iad.dim_iad_flight_base_dd.platform from internal.music_iad.dim_iad_flight_base_dd
) f on internal.music_iad.a.flight_id=internal.music_iad.f.flight_id;

select
flight_id , api_type , dsp_id, os , vip_type,
sum(media_flight_request_ads) as media_flight_request_ads ,
sum(dsp_request) as dsp_request ,
sum(dsp_resp) as dsp_resp,
sum(deal_request) as deal_request ,
sum(deal_resp) as deal_resp,
sum(dsp_bid_participate) as dsp_bid_participate ,
sum(dsp_resp_ads_win) dsp_resp_ads_win,
sum(exposure) as exposure ,
sum(click) as click
from v_ads_iad_adx_stat_core_di
where dt between '2024-10-25' and '2024-10-28'
and log_time_minute between '2024-10-25 00:00:00' and '2024-10-28 08:00:00'
and flight_id in ('90000001')
group by flight_id, api_type, dsp_id, os ,vip_type
;