版本2.5.7，创建物化视图时提示物化视图分区列必须在基表的分区列，但是基表的分区列和物化视图的分区列是一样的，详情请见附件
物化视图提示分区必须在基表分区列错误.sql (70.7 KB)
你好，这个是因为我们在相同的别名上处理存在bug，将表中的另一个a改成aa可以暂时规避这个问题。
CREATE MATERIALIZED VIEW dwd_view_processtest_detail_test
PARTITION BY (day_id)
DISTRIBUTED BY hash(workuser_barcode) BUCKETS 10
PROPERTIES (“storage_medium” = “SSD”
)
REFRESH ASYNC START(‘2023-08-04 18:15:00’) EVERY (interval 10 minute) as
select
a.day_id
,e.platform_code as industry_code
,e.platform_name as industry_desc
,e.site_code
,m.factoryno as factory_code
,e.factory_name
,j.production_line_code as product_line_code
,j.production_line_desc as product_line_desc
,a.code as order_code
,m.est as order_date
,m.prod_code
,m.prod_desc
,a.team_code
,k.team_desc
,a.shift_code
,l.shift_desc
,a.workuser_barcode
,a.testtime
,from_unixtime(unix_timestamp(a.testtime)- 3600 * 8,‘yyyy-mm-dd’) work_date
,testresult
,a.work_cell_code as pro_work_cell_code
,j.work_cell_desc as pro_work_cell_desc
,c.phenomenon_reason_code
,c.phenomenon_reason_name
,h.work_cell_code as duty_work_cell_code
,h.work_cell_desc as duty_work_cell_desc
,h.repairoptions
,h.repairtype
,h.repairdesc
,h.reason_code
,h.defect_location_name
,h.defect_reason_code
,h.defect_reason_name
,a.RepairStatus
,h.reserved3
,from_unixtime(unix_timestamp() ,‘yyyy-MM-dd HH:mm:ss’) etl_date
from dl_zz.tt_cosmo_mes_bns_qm_processtest a
left join dl_zz.tt_cosmo_mes_bns_qm_processtestdetail b
on substring(a.processtest_id,15) = b.processtest_id
and a.site_code = b.site_code
left join dl_zz.tt_cosmo_mes_base_phenomenon_reason c
on b.Reason_Code2 = c.Phenomenon_Reason_Code
and substring(b.processtestdetail_id,10,4) = SUBSTRING(c.phenomenon_reason_id,10,4)
left join dl_zz.tt_cosmo_mes_base_team_t k
on a.Team_Code=k.team_code
and substring (k.team_id,1,13) = substring (a.processtest_id,1,13)
and k.active=‘1’
left join dl_zz.tt_cosmo_mes_base_shifts_t l
on a.shift_code=l.shift_code
and substring (l.shift_id,1,13) = substring (a.processtest_id,1,13)
and l.active=‘1’
left join (
select distinct
f.site_code,f.Work_Cell_Code,g.work_cell_desc,f.workuser_barcode,f.processtestdetail_id,f.Repairoptions,f.reserved3
,f.RepairType,f.RepairDesc,f.Reason_Code,l.defect_location_name,r.defect_reason_code,r.Defect_Reason_Name
from dl_zz.tt_cosmo_mes_bns_qm_repairapply f
left join dl_zz.tt_cosmo_mes_pm_work_cells_t g
on f.Reason_Code4 =g.Work_Cell_Code
and g.active=‘1’
and SUBSTRING(f.repairapply_id,10,4) = SUBSTRING(g.work_cell_id,10,4)
left join dl_zz.tt_cosmo_mes_base_defect_location l
on f.Reason_Code=l.Defect_Location_Code
and SUBSTRING(f.repairapply_id,10,4) = SUBSTRING(l.defect_location_id,10,4)
left join dl_zz.tt_cosmo_mes_base_defect_reason r
on f.Reason_Code3=r.defect_reason_code
and SUBSTRING(f.repairapply_id,10,4) = SUBSTRING(r.Defect_Reason_Id,10,4)
) h
on h.processtestdetail_id=substring(b.processtestdetail_id,15)
left join (select substring(aa.work_cell_id ,10,4) site_code ,work_cell_code ,work_cell_desc , production_line_code,production_line_desc
From dl_zz.tt_cosmo_mes_pm_work_cells_t aa
left join dl_zz.tt_cosmo_mes_pm_production_lines_t b
on aa.production_line_id = substring(b.production_line_id,15)
and substring(aa.work_cell_id ,10,4) = substring(b.production_line_id,10,4)
where aa.active=‘1’
) j
on a.work_cell_code=j.work_cell_code and SUBSTRING(a.processtest_id,10,4)= j.site_code
left join dl_zz.tt_cosmo_mes_base_production_order_t m
on a.code = m.code
and substring(m.id,1,13)=substring(a.processtest_id,1,13)
left join (select distinct site_code,factory_name,platform_code,platform_name
from dl_zz.dim_platform_line l
where status in (‘0’,‘1’) and status in(‘0’, ‘1’) and site_code is not null
) e
on SUBSTRING(a.processtest_id,10,4)= trim(e.site_code);