为了更快的定位您的问题，请提供以下信息，谢谢
【详述】在对明细表进行聚合创建物化视图时，使用了unnest展开数组中的值。 这时会报ERROR 1064 (HY000): resolve partition column failed。
【是否存算分离】否
【StarRocks版本】2.5.20
【集群规模】3fe+8be
【联系方式】社区7-小帅虎
【SQL】
CREATE MATERIALIZED VIEW rpt.mv_realtime_ads_bi_ff_tra_dlvr_sto_active_d
PARTITION BY dt
DISTRIBUTED BY HASH(store_id, source_id, active_id) BUCKETS 3
REFRESH ASYNC START(‘2025-02-25 00:00:00’) EVERY(INTERVAL 1 MINUTE)
PROPERTIES(
“replication_num”=“3”,
“partition_ttl_number”=“8”,
“partition_refresh_number”=“4”,
“auto_refresh_partitions_limit”=“8”
)
AS
SELECT b.dt
,b.store_id
,b.source_id
,c.unnest AS active_id
,MAX(b.store_type) AS store_type
,bitmap_union(to_bitmap(b.order_global_id)) AS order_main_id
,SUM(b.deliver_qty) AS dlvr_qty
,SUM(b.discount_total) AS discount_amt
,SUM(b.deliver_amt) AS dlvr_amt
,SUM(b.deliver_amt / (1 + b.sales_tax_rate / 100)) AS dlvr_amt_no_tax
,now() AS etl_time
FROM (
SELECT a.dt
,a.store_id
,a.source_id
,a.store_type
,a.order_global_id
,a.rt_item_no
,a.deliver_qty
,a.discount_total
,a.deliver_amt
,a.sales_tax_rate
,ARRAY_REMOVE(array_filter(a.ids,a.group_ids),’_’) AS active_ids
FROM (
SELECT dt
,store_id
,source_id
,store_type
,order_global_id
,rt_item_no
,deliver_qty
,discount_total
,deliver_amt
,sales_tax_rate
,CAST(json_query(discount_infos,’$[].active_id’) AS ARRAY) AS ids
,array_map(x -> IF(x != 1,0,x),CAST(json_query(discount_infos,’$[].discount_group’) AS ARRAY)) AS group_ids
,discount_infos
FROM dwd.fct_realtime_rtac_tra_pushtime_item_di
WHERE source_id = 50
AND kind != ‘GIFT’
AND json_length(discount_infos) > 0
) a
) b
, UNNEST(b.active_ids)AS c
WHERE array_length(b.active_ids) > 0
GROUP BY b.dt
,b.store_id
,b.source_id
,c.UNNEST
;
