创建视图的字段不包含原表的日期主键acc_date，调度任务往原表插数据报错

[U_1687836733006_1908]

CREATE TABLE ads.ads_boss_group_index_dt_de (
tenant_id int(11) comment ‘租户号’,
acc_date date comment ‘统计日期’,
dept_code varchar(100) comment ‘门店编码（基础门店）’,
w_type varchar(100) comment ‘维度类型编码’,
category_code_1 varchar(100) comment ‘一级品类编码(采购组编码)’,
busi_unit_code varchar(256) comment ‘业务板块编码’,
busi_subdiv_code varchar(256) comment ‘业务细分编码’,
norm_purch_dept_code varchar(100) comment ‘标准采购部编码’,
manage_purch_dept_code varchar(100) comment ‘管理采购部编码’,
brand_code varchar(100) comment ‘品牌编码’,
channel_code varchar(100) comment ‘渠道编码（销售渠道编码）’,
supplier_type_code varchar(100) comment ‘供应商类型编码’,
comp_state varchar(100) comment ‘是否可比’,
public_dept_code varchar(100) comment ‘门店编码（公共门店）’,
dept_classify_code varchar(100) comment ‘部门分类编码（店型编码）’,
company_code varchar(100) comment ‘公司编码’,
year_w_code varchar(100) comment ‘年周编码’,
year_m_code varchar(100) comment ‘年月编码’,
year_code varchar(100) comment ‘年编码’,
dept_name varchar(256) replace comment ‘门店名称（基础门店）’,
public_dept_name varchar(256) replace comment ‘门店名称（公共门店）’,
dept_classify_name varchar(256) replace comment ‘部门分类名称（店型名称）’,
company_name varchar(256) replace comment ‘公司名称’,
category_name_1 varchar(256) replace comment ‘一级品类名称(采购组编码)’,
busi_unit_name varchar(256) replace comment ‘业务板块名称’,
busi_subdiv_name varchar(256) replace comment ‘业务细分名称’,
norm_purch_dept_name varchar(256) replace comment ‘标准采购部名称’,
manage_purch_dept_name varchar(256) replace comment ‘管理采购部名称’,
brand_name varchar(256) replace comment ‘品牌名称’,
channel_name varchar(256) replace comment ‘渠道名称（销售渠道名称）’,
supplier_type_name varchar(256) replace comment ‘供应商类型名称’,
charge_p_name varchar(256) replace comment ‘负责人名称’,
dept_comment_code varchar(256) replace comment ‘门店评价’,
dept_comment_name varchar(256) replace comment ‘门店评价内容’,
etl_time datetime replace comment ‘etl时间’,
sale_income_tax_d decimal(22,4) SUM DEFAULT “0” comment ‘销售金额（含税）’,
sale_income_d decimal(22,4) SUM DEFAULT “0” comment ‘销售金额（无税）=销售收入’,
front_sale_grossprofit_tax_d decimal(22,4) SUM DEFAULT “0” comment ‘前台毛利金额（含税）’,
front_sale_grossprofit_d decimal(22,4) SUM DEFAULT “0” comment ‘前台毛利金额（无税）’,
back_sale_grossprofit_tax_d decimal(22,4) SUM DEFAULT “0” comment ‘后台毛利金额（含税）’,
back_sale_grossprofit_d decimal(22,4) SUM DEFAULT “0” comment ‘后台毛利金额（无税）’,
sale_grossprofit_tax_d decimal(22,4) SUM DEFAULT “0” comment ‘综合毛利(含税)’,
sale_grossprofit_d decimal(22,4) SUM DEFAULT “0” comment ‘综合毛利(无税)’,
bill_num_d int(11) SUM DEFAULT “0” comment ‘客流量’,
target_amount decimal(22,4) max DEFAULT “0” comment ‘目标金额’
)
AGGREGATE KEY(tenant_id,acc_date,dept_code,w_type,category_code_1,busi_unit_code,busi_subdiv_code,norm_purch_dept_code,manage_purch_dept_code,brand_code,channel_code,supplier_type_code,comp_state,public_dept_code,dept_classify_code,company_code,year_w_code,year_m_code,year_code)
COMMENT “维度指标日汇总表”
PARTITION BY RANGE (acc_date) (
START (“2021-01-01”) END (“2023-06-30”) EVERY (INTERVAL 1 DAY)
)
DISTRIBUTED BY HASH(tenant_id,acc_date,dept_code) BUCKETS 5
PROPERTIES (
“dynamic_partition.enable” = “true”,
“dynamic_partition.time_unit” = “DAY”,
“dynamic_partition.start” = “-1000”,
“dynamic_partition.end” = “2”,
“dynamic_partition.prefix” = “p”,
“dynamic_partition.buckets” = “5”
)
;

–周查询视图
–问题1：给日汇总表建了个基于周编码查询的物化视图，视图的字段不包含日表的日期主键acc_date，调度任务往日表插数据报错unknown column acc_date in index week_aggr
–问题2：name相关的字段放开建视图也报错
CREATE MATERIALIZED VIEW week_aggr AS
SELECT year_w_code,
tenant_id,
dept_code ,
company_code ,
w_type ,
category_code_1 ,
busi_unit_code ,
busi_subdiv_code ,
norm_purch_dept_code ,
manage_purch_dept_code,
brand_code ,
channel_code ,
supplier_type_code ,
comp_state ,
public_dept_code ,
dept_classify_code ,
– max(dept_name ) as dept_name ,
– max(public_dept_name ) as public_dept_name ,
– max(dept_classify_name ) as dept_classify_name ,
– max(company_name ) as company_name ,
– max(category_name_1 ) as category_name_1 ,
– max(busi_unit_name ) as busi_unit_name ,
– max(busi_subdiv_name ) as busi_subdiv_name ,
– max(norm_purch_dept_name ) as norm_purch_dept_name ,
– max(manage_purch_dept_name) as manage_purch_dept_name ,
– max(brand_name ) as brand_name ,
– max(channel_name ) as channel_name ,
– max(supplier_type_name ) as supplier_type_name ,
– max(charge_p_name ) as charge_p_name ,
sum(sale_income_tax_d ) as sale_income_tax_d ,
sum(sale_income_d ) as sale_income_d ,
sum(front_sale_grossprofit_tax_d) as front_sale_grossprofit_tax_d ,
sum(front_sale_grossprofit_d ) as front_sale_grossprofit_d ,
sum(back_sale_grossprofit_tax_d ) as back_sale_grossprofit_tax_d ,
sum(back_sale_grossprofit_d ) as back_sale_grossprofit_d ,
sum(sale_grossprofit_tax_d ) as sale_grossprofit_tax_d ,
sum(sale_grossprofit_d ) as sale_grossprofit_d ,
sum(bill_num_d ) as bill_num_d ,
max(target_amount) as target_amount
FROM ads.ads_boss_group_index_dt_de
GROUP BY year_w_code,
tenant_id,
dept_code ,
w_type ,
category_code_1 ,
busi_unit_code ,
busi_subdiv_code ,
norm_purch_dept_code ,
manage_purch_dept_code,
brand_code ,
channel_code ,
supplier_type_code ,
comp_state ,
public_dept_code ,
dept_classify_code ,
company_code
;

版本号2.5.3，有人知道是什么情况，怎么解决吗

[U_1687836733006_1908]

版本号2.5.3

[yuchen1019]

@U_1687836733006_1908

CREATE MATERIALIZED VIEW week_aggr AS
SELECT year_w_code,
tenant_id,
dept_code ,
company_code ,
w_type ,
category_code_1 ,
busi_unit_code ,
busi_subdiv_code ,
norm_purch_dept_code ,
manage_purch_dept_code,
brand_code ,
channel_code ,
supplier_type_code ,
comp_state ,
public_dept_code ,
dept_classify_code ,
– max(dept_name ) as dept_name ,
– max(public_dept_name ) as public_dept_name ,
– max(dept_classify_name ) as dept_classify_name ,
– max(company_name ) as company_name ,
– max(category_name_1 ) as category_name_1 ,
– max(busi_unit_name ) as busi_unit_name ,
– max(busi_subdiv_name ) as busi_subdiv_name ,
– max(norm_purch_dept_name ) as norm_purch_dept_name ,
– max(manage_purch_dept_name) as manage_purch_dept_name ,
– max(brand_name ) as brand_name ,
– max(channel_name ) as channel_name ,
– max(supplier_type_name ) as supplier_type_name ,
– max(charge_p_name ) as charge_p_name ,
sum(sale_income_tax_d ) as sale_income_tax_d ,
sum(sale_income_d ) as sale_income_d ,
sum(front_sale_grossprofit_tax_d) as front_sale_grossprofit_tax_d ,
sum(front_sale_grossprofit_d ) as front_sale_grossprofit_d ,
sum(back_sale_grossprofit_tax_d ) as back_sale_grossprofit_tax_d ,
sum(back_sale_grossprofit_d ) as back_sale_grossprofit_d ,
sum(sale_grossprofit_tax_d ) as sale_grossprofit_tax_d ,
sum(sale_grossprofit_d ) as sale_grossprofit_d ,
sum(bill_num_d ) as bill_num_d ,
max(target_amount) as target_amount
FROM ads.ads_boss_group_index_dt_de
GROUP BY year_w_code,
tenant_id,
dept_code ,
w_type ,
category_code_1 ,
busi_unit_code ,
busi_subdiv_code ,
norm_purch_dept_code ,
manage_purch_dept_code,
brand_code ,
channel_code ,
supplier_type_code ,
comp_state ,
public_dept_code ,
dept_classify_code ,
company_code
;

麻烦第一个问题给出下创建语句 我尝试复现下 ，第二个报错是不是：Unexpected exception: The aggregation type of column[dept_name] must be same as the aggregate type of base column in aggregate table 这个是指dept_name列的聚合类型要跟聚合表中对应列的聚合类型保持一致的

[U_1687836733006_1908]

ads_boss_group_index_dt_de这个是日汇总表：
CREATE TABLE ads.ads_boss_group_index_dt_de (
tenant_id int(11) comment ‘租户号’,
acc_date date comment ‘统计日期’,
dept_code varchar(100) comment ‘门店编码（基础门店）’,
w_type varchar(100) comment ‘维度类型编码’,
category_code_1 varchar(100) comment ‘一级品类编码(采购组编码)’,
busi_unit_code varchar(256) comment ‘业务板块编码’,
busi_subdiv_code varchar(256) comment ‘业务细分编码’,
norm_purch_dept_code varchar(100) comment ‘标准采购部编码’,
manage_purch_dept_code varchar(100) comment ‘管理采购部编码’,
brand_code varchar(100) comment ‘品牌编码’,
channel_code varchar(100) comment ‘渠道编码（销售渠道编码）’,
supplier_type_code varchar(100) comment ‘供应商类型编码’,
comp_state varchar(100) comment ‘是否可比’,
public_dept_code varchar(100) comment ‘门店编码（公共门店）’,
dept_classify_code varchar(100) comment ‘部门分类编码（店型编码）’,
company_code varchar(100) comment ‘公司编码’,
year_w_code varchar(100) comment ‘年周编码’,
year_m_code varchar(100) comment ‘年月编码’,
year_code varchar(100) comment ‘年编码’,
dept_name varchar(256) replace comment ‘门店名称（基础门店）’,
public_dept_name varchar(256) replace comment ‘门店名称（公共门店）’,
dept_classify_name varchar(256) replace comment ‘部门分类名称（店型名称）’,
company_name varchar(256) replace comment ‘公司名称’,
category_name_1 varchar(256) replace comment ‘一级品类名称(采购组编码)’,
busi_unit_name varchar(256) replace comment ‘业务板块名称’,
busi_subdiv_name varchar(256) replace comment ‘业务细分名称’,
norm_purch_dept_name varchar(256) replace comment ‘标准采购部名称’,
manage_purch_dept_name varchar(256) replace comment ‘管理采购部名称’,
brand_name varchar(256) replace comment ‘品牌名称’,
channel_name varchar(256) replace comment ‘渠道名称（销售渠道名称）’,
supplier_type_name varchar(256) replace comment ‘供应商类型名称’,
charge_p_name varchar(256) replace comment ‘负责人名称’,
dept_comment_code varchar(256) replace comment ‘门店评价’,
dept_comment_name varchar(256) replace comment ‘门店评价内容’,
etl_time datetime replace comment ‘etl时间’,
sale_income_tax_d decimal(22,4) SUM DEFAULT “0” comment ‘销售金额（含税）’,
sale_income_d decimal(22,4) SUM DEFAULT “0” comment ‘销售金额（无税）=销售收入’,
front_sale_grossprofit_tax_d decimal(22,4) SUM DEFAULT “0” comment ‘前台毛利金额（含税）’,
front_sale_grossprofit_d decimal(22,4) SUM DEFAULT “0” comment ‘前台毛利金额（无税）’,
back_sale_grossprofit_tax_d decimal(22,4) SUM DEFAULT “0” comment ‘后台毛利金额（含税）’,
back_sale_grossprofit_d decimal(22,4) SUM DEFAULT “0” comment ‘后台毛利金额（无税）’,
sale_grossprofit_tax_d decimal(22,4) SUM DEFAULT “0” comment ‘综合毛利(含税)’,
sale_grossprofit_d decimal(22,4) SUM DEFAULT “0” comment ‘综合毛利(无税)’,
bill_num_d int(11) SUM DEFAULT “0” comment ‘客流量’,
target_amount decimal(22,4) max DEFAULT “0” comment ‘目标金额’
)
AGGREGATE KEY(tenant_id,acc_date,dept_code,w_type,category_code_1,busi_unit_code,busi_subdiv_code,norm_purch_dept_code,manage_purch_dept_code,brand_code,channel_code,supplier_type_code,comp_state,public_dept_code,dept_classify_code,company_code,year_w_code,year_m_code,year_code)
COMMENT “维度指标日汇总表”
PARTITION BY RANGE (acc_date) (
START (“2021-01-01”) END (“2023-06-30”) EVERY (INTERVAL 1 DAY)
)
DISTRIBUTED BY HASH(tenant_id,acc_date,dept_code) BUCKETS 5
PROPERTIES (
“dynamic_partition.enable” = “true”,
“dynamic_partition.time_unit” = “DAY”,
“dynamic_partition.start” = “-1000”,
“dynamic_partition.end” = “2”,
“dynamic_partition.prefix” = “p”,
“dynamic_partition.buckets” = “5”
)
;

VIEW week_aggr 这个基于上面建立的是视图：
CREATE MATERIALIZED VIEW week_aggr AS
SELECT year_w_code,
tenant_id,
dept_code ,
company_code ,
w_type ,
category_code_1 ,
busi_unit_code ,
busi_subdiv_code ,
norm_purch_dept_code ,
manage_purch_dept_code,
brand_code ,
channel_code ,
supplier_type_code ,
comp_state ,
public_dept_code ,
dept_classify_code ,
– max(dept_name ) as dept_name ,
– max(public_dept_name ) as public_dept_name ,
– max(dept_classify_name ) as dept_classify_name ,
– max(company_name ) as company_name ,
– max(category_name_1 ) as category_name_1 ,
– max(busi_unit_name ) as busi_unit_name ,
– max(busi_subdiv_name ) as busi_subdiv_name ,
– max(norm_purch_dept_name ) as norm_purch_dept_name ,
– max(manage_purch_dept_name) as manage_purch_dept_name ,
– max(brand_name ) as brand_name ,
– max(channel_name ) as channel_name ,
– max(supplier_type_name ) as supplier_type_name ,
– max(charge_p_name ) as charge_p_name ,
sum(sale_income_tax_d ) as sale_income_tax_d ,
sum(sale_income_d ) as sale_income_d ,
sum(front_sale_grossprofit_tax_d) as front_sale_grossprofit_tax_d ,
sum(front_sale_grossprofit_d ) as front_sale_grossprofit_d ,
sum(back_sale_grossprofit_tax_d ) as back_sale_grossprofit_tax_d ,
sum(back_sale_grossprofit_d ) as back_sale_grossprofit_d ,
sum(sale_grossprofit_tax_d ) as sale_grossprofit_tax_d ,
sum(sale_grossprofit_d ) as sale_grossprofit_d ,
sum(bill_num_d ) as bill_num_d ,
max(target_amount) as target_amount
FROM ads.ads_boss_group_index_dt_de
GROUP BY year_w_code,
tenant_id,
dept_code ,
w_type ,
category_code_1 ,
busi_unit_code ,
busi_subdiv_code ,
norm_purch_dept_code ,
manage_purch_dept_code,
brand_code ,
channel_code ,
supplier_type_code ,
comp_state ,
public_dept_code ,
dept_classify_code ,
company_code
;

今天建完视图之后再插入第一个表数据就报unknown column acc_date in index week_aggr

[U_1687836733006_1908]

排查了一下，主要是由于delete from导致调度任务异常，delete from ads.ads_boss_group_index_dt_de where acc_date=‘2023-06-01’;
报错Unknown column ‘acc_date’ in ‘index[week_aggr]’，这应该怎么解决呢