为了更快的定位您的问题，请提供以下信息，谢谢
【详述】问题详细描述
【背景】做过哪些操作？
【业务影响】
【是否存算分离】是
【StarRocks版本】例如：3.1.4
【集群规模】例如：3fe（1 leader+2 follower）+3be（fe与be混部）
【机器信息】CPU虚拟核/内存/网卡，例如：8C/32G/万兆
【联系方式】xiaxiong2010@163.com
-
base表
1、jdbc_catalog v_lease_battery_deliver
2、default_catalog dwd_starrocks_exchange_order
3、default_catalog v_dwd_starrocks_split_instruct_combine
-
物化视图创建sql
CREATE MATERIALIZED VIEW IF NOT EXISTS battery.mv_split_calculate
DISTRIBUTED BY HASH(platform_company_id,project_id,bill_date)
REFRESH ASYNC EVERY (interval 30 minute)
AS
select
a.platform_company_id,
a.project_id,
a.bill_date,
a.vin_count,
a.change_mile,
a.project_amount,
a.third_project_amount,
a.instruction_amount,
a.third_instruction_amount,
a.cur_exchange_vin_count,
a.cur_exchange_mile,
ceil(a.cur_exchange_mile / a.cur_exchange_vin_count) as cur_avg_exchange_mile,
a.payment_amount,
b.payment_amount as all_third_instruction_amount,
b.payment_amount as all_payment_amount
from
(
select
a.platform_company_id,
a.lease_project_id as project_id,
ifnull(b.bill_date, a1.bill_date) as bill_date,
sum(if(b.bill_date >= DATE_FORMAT(a.start_split_date, ‘%Y-%m-%d’), 1, 0)) as vin_count,
sum(b.exchange_odo) as change_mile,
sum(b.project_amount) as project_amount,
sum(b.third_project_amount) as third_project_amount,
sum(b.instruction_amount) as instruction_amount,
sum(b.payment_amount) as third_instruction_amount,
sum(c.payment_amount) as payment_amount,
sum(if(b.order_date > a.start_split_date and b.source_exchange_odo > 0, 1, 0)) as cur_exchange_vin_count,
sum(if(b.order_date > a.start_split_date and b.source_exchange_odo > 0, b.source_exchange_odo, 0)) as cur_exchange_mile
from
mysql_catelog.ion_biz_rental.v_lease_battery_deliver a
left join (
select
a.vin,
DATE_FORMAT(a.order_date, ‘%Y-%m-%d’) as bill_date
from
battery.dwd_starrocks_exchange_order a
left join mysql_catelog.ion_biz_rental.v_lease_battery_deliver b on
a.vin = b.vin_code
where
1 = 1
and a.order_status = ‘0’
and a.order_date >= b.start_split_date
and a.order_date >= b.project_split_date
group by
1,
2
union
select
a.vin,
DATE_FORMAT(a.order_date, ‘%Y-%m-%d’) as bill_date
from
(
select
vin,
DATE_FORMAT(a.trans_complete_time, ‘%Y-%m-%d’) as order_date
from
battery.v_dwd_starrocks_split_instruct_combine a
where
payment_status = ‘1’
and checked_flag = ‘1’
group by
1,
2 ) a
left join mysql_catelog.ion_biz_rental.v_lease_battery_deliver b on
a.vin = b.vin_code
where
1 = 1
and a.order_date >= b.start_split_date
and a.order_date >= b.project_split_date
group by
1,
2 ) a1 on
a.vin_code = a1.vin
left join (
select
a.vin,
DATE_FORMAT(a.order_date, ‘%Y-%m-%d’) as bill_date,
a.order_date,
sum(a.current_car_odo - a.last_car_odo) as source_exchange_odo,
sum(a.exchange_odo) as exchange_odo,
sum( ROUND(a.exchange_odo * cast(b.split_expr AS DECIMAL64 (15, 4)), 2) ) as project_amount,
sum(ROUND(a.battery_use_amount, 2)) as third_project_amount,
sum(if(a.cash_amount >= ROUND(a.exchange_odo * cast(b.split_expr AS DECIMAL64 (15, 4)), 2), ROUND(a.exchange_odo * cast(b.split_expr AS DECIMAL64 (15, 4)), 2), a.cash_amount)) as instruction_amount,
sum(c.payment_amount) as payment_amount
from
battery.dwd_starrocks_exchange_order a
left join mysql_catelog.ion_biz_rental.v_lease_battery_deliver b on
a.vin = b.vin_code
left join (
select
consumer_order_no,
sum(payment_amount) as payment_amount
from
battery.v_dwd_starrocks_split_instruct_combine
where
payment_status = ‘1’
and checked_flag = ‘1’
group by
consumer_order_no ) c on
a.order_id = c.consumer_order_no
where
1 = 1
and a.order_status = ‘0’
and a.order_date >= b.start_split_date
and a.order_date >= b.project_split_date
group by
1,
2 ,
3 ) b on
a.vin_code = b.vin
and a1.bill_date = b.bill_date
and a1.vin = b.vin
left join (
select
a.vin,
DATE_FORMAT(a.trans_complete_time, ‘%Y-%m-%d’) as bill_date,
sum(a.payment_amount) as payment_amount
from
battery.v_dwd_starrocks_split_instruct_combine a
where
a.payment_status = ‘1’
and a.checked_flag = ‘1’
group by
1,
2 ) c on
a.vin_code = c.vin
and c.bill_date = a1.bill_date
and a1.vin = c.vin
where
1 = 1
and a.platform_company_id = ‘C_0027’
group by
a.lease_project_id,
a.platform_company_id,
ifnull(b.bill_date, a1.bill_date) ) a
left join (
select
platform_company_id,
DATE_FORMAT(trans_complete_time, ‘%Y-%m-%d’) as bill_date,
sum(payment_amount) as payment_amount
from
battery.v_dwd_starrocks_split_instruct_combine
where
payment_status = ‘1’
and checked_flag = ‘1’
– and trans_complete_month = ‘2024-04’
group by
1,
2 ) b on
a.platform_company_id = b.platform_company_id
and a.bill_date = b.bill_date
where a.bill_date is not null
order by
a.bill_date desc; -
查询报错：
Getting analyzing error. Detail message: The data of ‘mv_split_calculate’ cannot be inserted because ‘mv_split_calculate’ is a materialized view,and the data of materialized view must be consistent with the base table…