当物化视图查询逻辑中存在嵌套，添加分区字段会报错
SQL 错误 [1064] [42000]: Materialized view partition expression cc.day_id could only ref to base table
请问这种想要创建分区该如何处理
物化视图创建语句如下：
CREATE MATERIALIZED VIEW dh_test.dwd_cosmo_fac_test
PARTITION by day_id
DISTRIBUTED BY hash(site_code, line_code) BUCKETS 5
REFRESH ASYNC START(‘2023-07-17 15:30:00’) EVERY (interval 30 minute)
as
with down_detail as (
select
substring(prodprocess_id, 10, 4) site_code,
workuser_barcode,
leave_time as sctime,
lag(leave_time) over(
partition by TRIM(a.plcode),
from_unixtime(unix_timestamp(leave_time) -3600 * 8, ‘%Y-%m-%d’)
order by
leave_time ASC
) as sctime0,
lag(prod_code) over(
partition by TRIM(a.plcode),
from_unixtime(unix_timestamp(leave_time) -3600 * 8, ‘%Y-%m-%d’)
order by
leave_time asc
) as prod_code0,
lag(workuser_barcode) over(
partition by TRIM(a.plcode),
from_unixtime(unix_timestamp(leave_time) -3600 * 8, ‘%Y-%m-%d’)
order by
leave_time asc
) as workuser_barcode0,
plcode,
order_code,
prod_code,
material_desc,
team_code,
shift_code,
lag(shift_code) over(
partition by TRIM(a.plcode),
from_unixtime(unix_timestamp(leave_time) -3600 * 8, ‘%Y-%m-%d’)
order by
leave_time asc
) as shift_code0,
day_id ,
Enterprise_Id,
Enterprise_code
from
dl_zz.tt_cosmo_mes_bns_pm_prodprocess a
left join (
select
mlcode,
material_desc,
product_type_code
from
di_zz.duk_zzfx_map_lq_xw_prod
group by
mlcode,
material_desc,
product_type_code
) lq on lq.mlcode = a.prod_code
where
day_id >= date_sub(current_date, 30)
and substr(order_code, 1, 2) in (‘1’, ‘2’, ‘3’, ‘10’, ‘20’, ‘30’)
and case
when plcode in (‘xx’,‘aa’)
and trim(Work_Cell_Code) like ‘%97’ then lq.product_type_code not in (‘xxx’)
when plcode not in (‘xxx’, ‘xxx’, ‘xxx’, ‘xxx’, ‘ff’, ‘ee’) and spare1 = 1 then (
case
when substring(prodprocess_id, 10, 4) != ‘3333’ then lq.product_type_code not in (‘xxx’)
else lq.product_type_code not in (‘xxx’)
end
)
end
),
stop_calendar as (
select
period_id,
site_code,
plcode,
plan_start_time3,
plan_end_time3
from
(
select
b.,
case
when plan_end_time1 < plan_start_time
or plan_end_time1 is null then plan_start_time
else plan_start_time1
end plan_start_time3,
case
when plan_end_time < plan_start_time2
or plan_start_time2 is null then plan_end_time
else plan_end_time2
end plan_end_time3
from
(
select
a.,
lag(plan_start_time, 1) over (
partition by period_id,
site_code,
plcode
order by
plan_start_time
) plan_start_time1,
lag(plan_end_time, 1) over (
partition by period_id,
site_code,
plcode
order by
plan_start_time
) plan_end_time1,
lead(plan_start_time, 1) over (
partition by period_id,
site_code,
plcode
order by
plan_start_time
) plan_start_time2,
lead(plan_end_time, 1) over (
partition by period_id,
site_code,
plcode
order by
plan_start_time
) plan_end_time2
from
(
select
distinct full_date period_id,
substring(cal.id,10,4) site_code,
cal.plcode,
cal.plan_time,
cal.plan_describe,
concat(full_date, ’ ', plan_start_time) plan_start_time,
if(
plan_start_time > plan_end_time,
concat(date_add(full_date, 1), ’ ', plan_end_time),
concat(full_date, ’ ', plan_end_time)
) plan_end_time
from
dl_zz.dim_period p
left join dl_zz.tt_cosmo_mes_base_stop_calendar cal on 1 = 1
where
full_date >= date_sub(current_date, 30)
and full_date <= current_date
and (
plan_start_date is null
or plan_start_date = ‘’
)
union all
select
distinct full_date period_id,
substring(cal.id,10,4) site_code,
cal.plcode,
cal.plan_time,
cal.plan_describe,
concat(plan_start_date, ’ ', plan_start_time) plan_start_time,
concat(plan_end_date, ’ ', plan_end_time) plan_end_time
from
dl_zz.dim_period p
left join dl_zz.tt_cosmo_mes_base_stop_calendar cal on 1 = 1
and plan_start_date = full_date
where
plan_start_date >= date_sub(current_date, 30)
and plan_start_date <= current_date
and (
plan_start_date is not null
and plan_start_date <> ‘’
)
) a
) b
) c
group by
period_id,
site_code,
plcode,
plan_start_time3,
plan_end_time3
)
select
distinct l.platform_code,
l.platform_name,
l.platform2_code,
l.platform2_name,
cc.site_code,
l.factory_code,
l.factory_name,
cc.plcode line_code,
l.line_name,
cc.order_code orderno,
cc.workuser_barcode,
cc.prod_code,
cc.prod_desc,
cc.day_id,
cc.plan_start_time,
cc.plan_end_time,
cc.shift_code,
shifts.shift_desc,
cc.team_code,
team.team_desc,
cc.sctime,
cc.sctime0 sctime2,
cc.workuser_barcode0 printcode2,
case
when time_diff > 8 * 3600 then 0
when rank = 1 and cc.shift_code <> cc.shift_code0 then 0
when rank = 1 and cc.sctime0 <= cc.plan_start_time and cc.sctime >= cc.plan_end_time then cc.time_diff - cc.plan_time
when rank = 1 and cc.sctime0 <= cc.plan_start_time and cc.sctime < cc.plan_end_time then unix_timestamp(cc.plan_start_time) - unix_timestamp(cc.sctime0)
when rank = 1 and cc.sctime0 > cc.plan_start_time and cc.sctime < cc.plan_end_time then 0
when rank = 1 and cc.sctime0 > cc.plan_start_time and cc.sctime > cc.plan_end_time then unix_timestamp(cc.sctime) - unix_timestamp(cc.plan_end_time)
else cc.time_diff
end timediff,
cc.prod_code0,
cc.plan_time,
case
when rank = 1 and cc.shift_code = cc.shift_code0 then 1
else 0
end stop_flag,
case
when sctime0 is null or cc.shift_code <> cc.shift_code0 then 1
end first_flag,
from_unixtime(unix_timestamp()) dh_etl_date
from
(
select
dd.day_id,
dd.site_code,
dd.plcode,
dd.team_code,
dd.shift_code,
dd.shift_code0,
dd.prod_code,
dd.material_desc as prod_desc,
dd.workuser_barcode,
dd.sctime,
dd.sctime0,
dd.prod_code0,
dd.workuser_barcode0,
dd.order_code,
cal.plan_start_time3 plan_start_time,
cal.plan_end_time3 plan_end_time,
case
when dd.shift_code <> dd.shift_code0 then 0
else unix_timestamp(dd.sctime) - unix_timestamp(dd.sctime0)
end time_diff,
unix_timestamp(cal.plan_end_time3) - unix_timestamp(cal.plan_start_time3) plan_time,
case
when cal.plan_start_time3 is not null then ROW_NUMBER() OVER (
partition BY dd.site_code,
dd.plcode,
dd.shift_code,
dd.shift_code,
cal.plan_start_time3
ORDER BY
unix_timestamp(dd.sctime) - unix_timestamp(dd.sctime0) DESC
)
end rank,
ROW_NUMBER() OVER (
partition BY workuser_barcode
ORDER BY
plan_end_time3 DESC
) rank1,
dd.Enterprise_Id,
dd.Enterprise_code
from
down_detail dd
left join stop_calendar cal on dd.plcode = cal.plcode
and dd.site_code = cal.site_code
and dd.day_id = cal.period_id
and (
dd.sctime0 < plan_end_time3
and dd.sctime > plan_start_time3
)
) cc
left join di_zz.dim_mes_dim_platform_line l on cc.plcode = l.line_code
and cc.site_code = l.site_code
left join dl_zz.tt_cosmo_mes_base_shifts_t shifts on cc.shift_code = shifts.shift_code
and substring(shifts.shift_id, 10, 4) = cc.site_code
and shifts.active = ‘1’
inner join (
SELECT
*
FROM
(
SELECT
*,
ROW_NUMBER() OVER(
PARTITION BY team_code,
site_code,
plcode
ORDER BY
last_update_date DESC
) rankid
FROM
dl_zz.tt_cosmo_mes_base_team_t team
where
active = ‘1’
and site_code != ‘1023’
) t
WHERE
rankid = 1
) team on cc.team_code = team.team_code
and substring(team.team_id, 10, 4) = cc.site_code
where
rank1 = 1