2.0.14版本，同步物化视图不命中

Viewed 28
2.0

doris版本 2.0.14
查询语句与建物化视图语句一样，但没有命中物化视图 ，请教下可能是什么原因呢。表里有数据

explain SELECT ads_rev_vip_abtest_order_unbind_di.dt AS d0, ads_rev_vip_abtest_order_unbind_di.exp_name AS d1, ads_rev_vip_abtest_order_unbind_di.bucket_name AS d2, SUM(ads_rev_vip_abtest_order_unbind_di.order_cnt) AS m0, SUM((CASE WHEN (ads_rev_vip_abtest_order_unbind_di.unbind_0d_cnt > 0) THEN ads_rev_vip_abtest_order_unbind_di.unbind_0d_cnt ELSE 0 END)) AS m1, SUM((CASE WHEN (ads_rev_vip_abtest_order_unbind_di.order_cnt > 0) THEN ads_rev_vip_abtest_order_unbind_di.order_cnt ELSE 0 END)) AS m2, SUM((CASE WHEN (ads_rev_vip_abtest_order_unbind_di.unbind_1d_cnt > 0) THEN ads_rev_vip_abtest_order_unbind_di.unbind_1d_cnt ELSE 0 END)) AS m3, SUM(ads_rev_vip_abtest_order_unbind_di.unbind_0d_cnt) AS m4, SUM(ads_rev_vip_abtest_order_unbind_di.unbind_1d_cnt) AS m5, SUM(ads_rev_vip_abtest_order_unbind_di.unbind_7d_cnt) AS m6 FROM music_new_dm.ads_rev_vip_abtest_order_unbind_di WHERE (((CASE WHEN (ads_rev_vip_abtest_order_unbind_di.last_12_month_order_unit_price > 0) THEN FLOOR(ads_rev_vip_abtest_order_unbind_di.last_12_month_order_unit_price) ELSE CEIL(ads_rev_vip_abtest_order_unbind_di.last_12_month_order_unit_price) END) IS NOT NULL) AND ((CASE WHEN (ads_rev_vip_abtest_order_unbind_di.last_12_month_order_month_cnt > 0) THEN FLOOR(ads_rev_vip_abtest_order_unbind_di.last_12_month_order_month_cnt) ELSE CEIL(ads_rev_vip_abtest_order_unbind_di.last_12_month_order_month_cnt) END) IS NOT NULL)) GROUP BY ads_rev_vip_abtest_order_unbind_di.dt, ads_rev_vip_abtest_order_unbind_di.exp_name, ads_rev_vip_abtest_order_unbind_di.bucket_name;

image.png
image.png

表结构
image.png

z40428998119
2 Answers

方便提供一下sql中涉及到表结构吗，更新在提问内容中即可，doris具体是什么版本呢。

黄海军@SelectDB2666

CREATE TABLE music_new_dm.ads_rev_vip_abtest_order_unbind_di (
os varchar() COMMENT '用户os',
exp_id varchar() COMMENT '实验id',
exp_name varchar() COMMENT '实验名称',
bucket_id BIGINT COMMENT '实验桶id',
bucket_name varchar() COMMENT '实验桶名称',
user_level varchar() COMMENT '用户等级',
city_level varchar() COMMENT '城市等级',
gender varchar() COMMENT '用户性别',
black_lost_level varchar() COMMENT '黑胶流失天数标签',
device_price_range varchar() COMMENT '设备价格',
integrate_age_group varchar() COMMENT '年龄区间',
duration_type varchar() COMMENT '卡种类型',
vip_type varchar() COMMENT '会员包类型',
sub_vtype varchar() COMMENT '会员包子类型',
order_life_cycle varchar() COMMENT '订单生命周期',
marketing_id varchar() COMMENT '营销id',
market_source varchar() COMMENT '订单来源',
user_cnt BIGINT COMMENT '用户数',
order_cnt BIGINT COMMENT '订单数',
trade_amt DOUBLE COMMENT '订单金额',
unbind_0d_cnt BIGINT COMMENT '当日连包订单解绑数',
unbind_1d_cnt BIGINT COMMENT '次日内连包订单解绑数',
unbind_7d_cnt BIGINT COMMENT '7日内连包订单解绑数',
unbind_31d_cnt BIGINT COMMENT '31日内连包订单解绑数',
unbind_62d_cnt BIGINT COMMENT '62日内连包订单解绑数',
unbind_93d_cnt BIGINT COMMENT '93日内连包订单解绑数',
unbind_124d_cnt BIGINT COMMENT '124日内连包订单解绑数',
unbind_155d_cnt BIGINT COMMENT '155日内连包订单解绑数',
last_vip_duration_type varchar() COMMENT '最近一次购买会员周期类型',
last_12_month_order_cnt varchar() COMMENT '最近一次付费前12个月会员订购单数',
last_12_month_order_month_cnt double COMMENT '最近一次付费前12个月会员订单月数',
last_12_month_order_unit_price double COMMENT '用户最近一次付费前12个月会员订购单价',
model1_level BIGINT COMMENT '模型1(近30日+区隔&收敛)-档位',
model2_level BIGINT COMMENT '模型2(近7日+区隔&收敛)-档位',
model3_level BIGINT COMMENT '模型3(近30日+区隔)-档位',
model4_level BIGINT COMMENT '模型4(近7日+区隔)-档位',
pay_method varchar() COMMENT '支付方式',
active_layer varchar() COMMENT '月度有效播放活跃度',
pingtai_active_layer varchar() COMMENT '平台活跃度',
rock_income varchar() COMMENT '收入水平（后面补充）',
model_price_range varchar(*) COMMENT '价格区间',
dt date
) ENGINE=OLAP
COMMENT '会员价格实验-用户解绑ads表'
PARTITION BY RANGE(dt)
(PARTITION p20241006 VALUES [('2024-10-06'), ('2024-10-07')))
DISTRIBUTED BY RANDOM BUCKETS 3
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"is_being_synced" = "false",
"dynamic_partition.enable" = "true",
"dynamic_partition.create_history_partition" = "true",
"dynamic_partition.time_unit" = "DAY",
"dynamic_partition.time_zone" = "Asia/Shanghai",
"dynamic_partition.start" = "-500",
"dynamic_partition.end" = "3",
"dynamic_partition.prefix" = "p",
"dynamic_partition.replication_allocation" = "tag.location.default: 3",
"dynamic_partition.buckets" = "3",
"dynamic_partition.create_history_partition" = "true",
"dynamic_partition.history_partition_num" = "-1",
"dynamic_partition.hot_partition_num" = "0",
"dynamic_partition.reserved_history_periods" = "NULL",
"dynamic_partition.storage_policy" = "",
"storage_format" = "V2",
"compression" = "ZSTD",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"enable_mow_light_delete" = "false"
);

z40428998119