问题：非常简单的SQL，物化视图都命中不了？是哪里有问题还是说starrocks对物化视图支持很弱？
建表SQL：CREATE TABLE ads_guanxing_day_org_pro_sptm_sal_ds (
tm_product_no varchar(65533) NULL COMMENT “天猫商品编码”,
tm_shop_no varchar(65533) NULL COMMENT “天猫店铺编码”,
category2_no varchar(65533) NULL COMMENT “二级分类编码”,
category3_no varchar(65533) NULL COMMENT “三级分类编码”,
category4_no varchar(65533) NULL COMMENT “四级分类编码”,
period_sdate varchar(65533) NULL COMMENT “销售时间”,
tm_shop_name varchar(65533) NULL COMMENT “天猫店铺名称”,
tm_brand_name varchar(65533) NULL COMMENT “天猫商品品牌”,
title varchar(65533) NULL COMMENT “标题”,
category2_name varchar(65533) NULL COMMENT “二级分类名称”,
category3_name varchar(65533) NULL COMMENT “三级分类名称”,
category4_name varchar(65533) NULL COMMENT “四级分类名称”,
estimated_amt decimal64(18, 4) NULL COMMENT “预估到手价”,
view_amt decimal64(18, 4) NULL COMMENT “页面价”,
sal_qty int(11) NULL COMMENT “日销量”,
sal_amt decimal64(18, 4) NULL COMMENT “日销额”,
collect_num int(11) NULL COMMENT “日收藏数据”,
spider_inv_qty int(11) NULL COMMENT “当日库存”,
etl_time varchar(65533) NULL COMMENT “etl时间”,
partition_day date NULL COMMENT “分区时间(YYYY-MM-DD)”
) ENGINE=OLAP DISTRIBUTED BY HASH
DUPLICATE KEY(tm_product_no, tm_shop_no, category2_no, category3_no, category4_no)
PARTITION BY RANGE(partition_day)
(
PARTITION p20231231 VALUES [(“2023-12-31”), (“2024-01-01”)),
PARTITION p20240101 VALUES [(“2024-01-01”), (“2024-01-02”)),
PARTITION p20240102 VALUES [(“2024-01-02”), (“2024-01-03”)),
PARTITION p20240103 VALUES [(“2024-01-03”), (“2024-01-04”)))
DISTRIBUTED BY HASH(partition_day) BUCKETS 3
PROPERTIES (
“replication_num” = “3”,
“dynamic_partition.enable” = “true”,
“dynamic_partition.time_unit” = “DAY”,
“dynamic_partition.time_zone” = “Asia/Shanghai”,
“dynamic_partition.start” = “-2147483648”,
“dynamic_partition.end” = “1”,
“dynamic_partition.prefix” = “p”,
“dynamic_partition.buckets” = “3”,
“dynamic_partition.history_partition_num” = “0”,
“in_memory” = “false”,
“storage_format” = “DEFAULT”,
“enable_persistent_index” = “false”,
“compression” = “LZ4”
);
物化视图：
CREATE MATERIALIZED VIEW ads_guanxing_day_org_pro_sptm_sal_ds_view_02
DISTRIBUTED BY HASH(tm_product_no)
REFRESH ASYNC START(‘2024-01-04 17:39:00’) EVERY (interval 1 day)
AS
select
tm_product_no ,
partition_day as period_sdate ,
sum(sal_qty) as current_sal_qty ,
sum(sal_amt) as current_sal_amt ,
sum(collect_num) as current_mockdata_num
from
ads_hy_guanxing.ads_guanxing_day_org_pro_sptm_sal_ds
group by
tm_product_no ,
partition_day ;
查看是否命中：
explain
select
tm_product_no ,
partition_day as period_sdate ,
sum(sal_qty) as current_sal_qty ,
sum(sal_amt) as current_sal_amt ,
sum(collect_num) as current_mockdata_num
from
ads_hy_guanxing.ads_guanxing_day_org_pro_sptm_sal_ds
group by
tm_product_no ,
partition_day ;
