【详述】创建物化视图 用date_trunc(‘DAY’ ,scrap_date) 分区报错
【业务影响】
【StarRocks版本】2.5
1064 - Materialized view partition function date_trunc check failed
麻烦发下完整的建表语句 我尝试复现下
CREATE MATERIALIZED VIEW test.order_mv1
PARTITION BY date_trunc(‘DAY’ ,scrap_date)
DISTRIBUTED BY HASH(scrap_date,organization_id,inventory_item_id,wip_entity_id,department_id,workstation_id,duty_id,defect_id)
REFRESH ASYNC START(‘2023-07-04 00:00:00’) EVERY (interval 30 minute) as
– REFRESH MANUAL as
with defect_init as
( select date_trunc(‘DAY’ ,dtl.last_update_date) as dt, dtl.last_update_date as scrap_date,
dtl.created_by as srcap_user,dth.organization_id,dth.me_operation_code,
dth.inventory_item_id,msi.segment1 as inventory_item_number,
msi.description as inventory_item_name,dth.wip_entity_id,we.wip_entity_name,
dth.department_id,bd.department_code,bd.description as department_name,
dth.workstation_id,bwd.description as workstation_name,dth.pcs_defect_qty,dtd.duty_id,
case when ddd.type = 0 then bd2.department_code
when ddd.type = 1 then dept.code
when ddd.type = 2 then s.segment1
else ddd.department_id end duty_code,
dtd.defect_id,dcd.code as defect_code,dcd.description as defect_name,
coalesce(bd2.description, coalesce(dept.name, s.vendor_name)) as duty_dist_name,
ddd.percent,
ROUND(dtd.primary_quantity * ddd.percent / 100 , 2) as dist_scrap_quantity,
(iwo.quantity_in_queue + iwo.quantity_completed) as checkin_quantity,
(sum(dtd.primary_quantity * ddd.percent)
over(partition by dth.wip_entity_id,dth.department_id,dtd.duty_id,
dtd.defect_id)) / 100 as defect_scrap_qty,
swdj.ATTRIBUTE5 runningNum,wdj.ATTRIBUTE6 attribute6 ,ddd.attribute1 remark
from ODS.IMES_IMES_WIP_DEFECT_TRX_HEADER dth
join ODS.IMES_IMES_WIP_DEFECT_TRX_LINE dtl on dth.header_id = dtl.header_id and dth.organization_id = dtl.organization_id
join ODS.IMES_IMES_WIP_DEFECT_TRX_DIST dtd on dtd.line_id = dtl.line_id and dtd.organization_id = dtl.organization_id
join ODS.EBS_WIP_WIP_ENTITIES we on dth.wip_entity_id = we.wip_entity_id and dth.organization_id = we.organization_id
join ODS.EBS_SCC_SCC_WIP_DISCRETE_JOBS swdj on dth.wip_entity_id = swdj.wip_entity_id and dth.organization_id = swdj.organization_id
join ODS.EBS_WIP_WIP_DISCRETE_JOBS wdj on dth.wip_entity_id = wdj.wip_entity_id and dth.organization_id = wdj.organization_id
join ODS.EBS_BOM_BOM_DEPARTMENTS bd on bd.department_id = dth.department_id and dth.organization_id = bd.organization_id
join ODS.EBS_INV_MTL_SYSTEM_ITEMS_B msi on msi.inventory_item_id = dth.inventory_item_id and dth.organization_id = msi.organization_id
left join ODS.IMES_IMES_BOM_WORKSTATION_DEFINE bwd on bwd.workstation_id = dth.workstation_id and dth.organization_id = bwd.organization_id
left join ODS.IMES_IMES_IMES_WIP_OPERATION iwo on iwo.wip_entity_id = dth.wip_entity_id and dth.organization_id = iwo.organization_id
and dth.operation_seq_num = iwo.wsm_op_seq_num
and iwo.workstation_id = dth.workstation_id
left join ODS.IMES_IMES_DEFECT_DUTY_HEADER dd on dd.duty_id = dtd.duty_id and dtd.organization_id = dd.organization_id
left join ODS.IMES_IMES_DEFECT_CODE_DEFINE dcd on dcd.defect_id = dtd.defect_id and dtd.organization_id = dcd.organization_id
left join ODS.IMES_IMES_DEFECT_DUTY_DISTRIBUTION ddd on ddd.category_id = dtd.category_id and dtd.organization_id = ddd.organization_id
left join ODS.EBS_BOM_BOM_DEPARTMENTS bd2 on bd2.department_code = ddd.department_id and bd2.organization_id = ddd.organization_id
or bd2.department_id = ddd.department_id
and ddd.type = 0
left join ODS.IMES_IMES_DEFECT_DUTY_DEPARTMENT dept on dept.department_id = ddd.department_id and dept.organization_id = ddd.organization_id
and ddd.type = 1
left join ODS.EBS_AP_AP_SUPPLIERS s on s.vendor_id = ddd.department_id
and ddd.type = 2
where 1 = 1
and not exists(
select 1 from ODS.IMES_IMES_WIP_DEFECT_CHANGE_HEADER wdch where 1=1
and wdch.organization_id = dth.organization_id
and wdch.wip_entity_id = dth.wip_entity_id
and wdch.operation_seq_num = dth.operation_seq_num
and wdch.workstation_id = dth.workstation_id
and wdch.workstation_seq_num = dth.workstation_seq_num
and wdch.src_defect_id = dcd.defect_id
)
union all
select date_trunc('DAY' ,wdch.last_update_date) as dt , wdch.last_update_date scrap_date,
wdch.src_scrap_user srcap_user,wdch.organization_id,wdch.me_operation_code,wdch.inventory_item_id,
msib.SEGMENT1 inventory_item_number,msib.DESCRIPTION inventory_item_name,wdch.wip_entity_id,we.WIP_ENTITY_NAME,
wdch.department_id,bd.DEPARTMENT_CODE,bd.DESCRIPTION department_name,wdch.workstation_id,
bwd.description workstation_name,wdth.pcs_defect_qty,wdcp.department_id duty_id,
case when wdcp.type = '0' then bd1.DEPARTMENT_CODE
when wdcp.type = '1' then ddd.code
when wdcp.type = '2' then sup.SEGMENT1 end duty_code,
wdcl.defect_id,dcd.code as defect_code,dcd.description defect_name,
case when wdcp.type = '0' then bd1.DESCRIPTION
when wdcp.type = '1' then ddd.name
when wdcp.type = '2' then sup.VENDOR_NAME end duty_dist_name,
wdcp.percent,
round(wdcl.defect_qty*wdcp.percent/100 ,2) dist_scrap_quantity,
(iwo.quantity_in_queue + iwo.quantity_completed) checkin_quantity,
(sum(wdcl.defect_qty*wdcp.percent) over (partition by wdch.wip_entity_id,wdch.operation_seq_num,
wdch.src_duty_id,wdcl.defect_id) / 100) defect_scrap_qty,
swdj.ATTRIBUTE5 runningNum,
wdj.ATTRIBUTE6,wdcl.remark
from ODS.IMES_IMES_WIP_DEFECT_CHANGE_HEADER wdch
inner join ODS.IMES_IMES_WIP_DEFECT_CHANGE_LINE wdcl on wdcl.header_id = wdch.header_id and wdch.organization_id = wdcl.organization_id
inner join ODS.IMES_IMES_WIP_DEFECT_TRX_HEADER wdth on wdth.organization_id = wdch.organization_id
and wdth.wip_entity_id = wdch.wip_entity_id
and wdth.operation_seq_num = wdch.operation_seq_num
and wdth.workstation_id = wdch.workstation_id
and wdth.workstation_seq_num = wdch.workstation_seq_num
inner join ODS.IMES_IMES_IMES_WIP_OPERATION iwo on iwo.organization_id = wdch.organization_id
and iwo.wip_entity_id = wdch.wip_entity_id
and iwo.wsm_op_seq_num = wdch.operation_seq_num
and iwo.workstation_id = wdch.workstation_id
inner join ODS.EBS_SCC_SCC_WIP_DISCRETE_JOBS wdj on wdj.ORGANIZATION_ID = wdch.organization_id
and wdj.WIP_ENTITY_ID = wdch.wip_entity_id
inner join ODS.IMES_IMES_WIP_DEFECT_CHANGE_PERCENT wdcp on wdcp.line_id = wdcl.line_id and wdcp.organization_id = wdcl.organization_id
inner join ODS.IMES_IMES_DEFECT_CODE_DEFINE dcd on dcd.defect_id = wdcl.defect_id and dcd.organization_id = wdcl.organization_id
inner join ODS.EBS_INV_MTL_SYSTEM_ITEMS_B msib on msib.ORGANIZATION_ID = wdch.organization_id
and msib.INVENTORY_ITEM_ID = wdch.inventory_item_id
inner join ODS.EBS_WIP_WIP_ENTITIES we on we.ORGANIZATION_ID = wdch.organization_id
and we.WIP_ENTITY_ID = wdch.wip_entity_id
inner join ODS.EBS_BOM_BOM_DEPARTMENTS bd on bd.DEPARTMENT_ID = wdch.department_id and bd.organization_id = wdch.organization_id
inner join ODS.EBS_SCC_SCC_WIP_DISCRETE_JOBS swdj on wdch.wip_entity_id = swdj.WIP_ENTITY_ID and swdj.organization_id = wdch.organization_id
left join ODS.IMES_IMES_BOM_WORKSTATION_DEFINE bwd on bwd.workstation_id = wdch.workstation_id and bwd.organization_id = wdch.organization_id
left join ODS.EBS_BOM_BOM_DEPARTMENTS bd1 on bd1.DEPARTMENT_ID = wdcp.department_id and bd1.organization_id = wdcp.organization_id
left join ODS.IMES_IMES_DEFECT_DUTY_DEPARTMENT ddd on ddd.department_id = wdcp.department_id and ddd.organization_id = wdcp.organization_id
left join ODS.EBS_AP_AP_SUPPLIERS sup on sup.VENDOR_ID = wdcp.department_id
)
select di.dt, di.scrap_date,di.srcap_user,di.organization_id,di.me_operation_code,
di.inventory_item_id,di.inventory_item_number,di.inventory_item_name,di.wip_entity_id,
di.wip_entity_name,di.department_id,di.department_code,di.department_name,di.workstation_id,
di.workstation_name,di.pcs_defect_qty,di.duty_id,di.duty_code,
di.defect_id, di.defect_code, di.defect_name, di.duty_dist_name, di.percent, di.checkin_quantity,
di.defect_scrap_qty, sum(di.dist_scrap_quantity) as dist_scrap_quantity, di.runningNum,
di.attribute6,di.remark
from defect_init di
group by dt,di.scrap_date, di.srcap_user, di.organization_id, di.me_operation_code,
di.inventory_item_id, di.inventory_item_number, di.inventory_item_name, di.wip_entity_id,
di.wip_entity_name, di.department_id, di.department_code,di.department_name,di.workstation_id,
di.workstation_name,di.pcs_defect_qty,di.duty_id,di.duty_code,
di.defect_id,di.defect_code,di.defect_name,di.duty_dist_name,di.percent,di.checkin_quantity,
di.defect_scrap_qty,di.runningNum,di.attribute6,di.remark请问这个问题有结论吗