【详述】
创建非分区物化视图后,出现select * from where book_date <= ‘2025-02-27’ and book_date >= ‘2025-01-01’ 出现 查询结果为null 情况.
尝试重新强制刷新分期，但是还是出现以上查询为空的情况,
物化视图底表SQL：
CREATE MATERIALIZED VIEW dim_ctk_smartretail_tenant_customer_inc_ed_fix (org_code COMMENT “机构编码”, book_date COMMENT “日结日期”, new_cnt COMMENT “新增会员数”, new_offline_cnt COMMENT “线下新增会员数”, new_online_cnt COMMENT “线上新增会员数”,
INDEX book_date (book_date) USING BITMAP,
INDEX org_code (org_code) USING BITMAP)
COMMENT “DIM-机构新增会员数-天-补零”
DISTRIBUTED BY HASH(org_code)
ORDER BY (org_code,book_date)
REFRESH ASYNC START(“2019-10-20 01:40:00”) EVERY(INTERVAL 1 DAY)
PROPERTIES (
“storage_medium” = “SSD”,
“storage_cooldown_time” = “9999-12-31 23:59:59”,
“replicated_storage” = “true”,
“replication_num” = “3”
)
AS SELECT complete_data.book_date, complete_data.org_code, max(complete_data.new_cnt) AS new_cnt, max(complete_data.new_offline_cnt) AS new_offline_cnt, max(complete_data.new_online_cnt) AS new_online_cnt
FROM (SELECT d.org_code, d.book_date, coalesce(t.new_cnt, 0) AS new_cnt, coalesce(t.new_offline_cnt, 0) AS new_offline_cnt, coalesce(t.new_online_cnt, 0) AS new_online_cnt
FROM (SELECT o.org_code, e.book_date
FROM (SELECT DISTINCT dim_ctk_smartretail_tenant_customer_inc_ed_new_offline_fix.org_code
FROM smartretail_tenant.dim_ctk_smartretail_tenant_customer_inc_ed_new_offline_fix) o CROSS JOIN (SELECT dim_time_calendar.book_date
FROM smartretail_tenant.dim_time_calendar
WHERE dim_time_calendar.book_date <= (current_date() - INTERVAL 1 DAY)) e ) d LEFT OUTER JOIN smartretail_tenant.dim_ctk_smartretail_tenant_customer_inc_ed_new_offline_fix AS t ON (d.org_code = t.org_code) AND (d.book_date = t.book_date)) complete_data
WHERE (complete_data.book_date <= (current_date() - INTERVAL 1 DAY)) AND (complete_data.book_date >= ‘2019-10-01’)
GROUP BY complete_data.org_code, complete_data.book_date ORDER BY complete_data.org_code ASC, complete_data.book_date ASC ;
【背景】做过哪些操作？（尝试强制刷新物化视图.）
refresh materialized view dim_ctk_smartretail_tenant_customer_inc_ed_fix force;
【业务影响】
【StarRocks版本】3.2.10
【集群规模】例如：3fe+3be（fe与be混部）
【联系方式】18862610591@163.com