分区依据表达式引用既未分组也不聚合的列 SKU

Question

我正在尝试执行此查询

select
rank() over (partition by substr(upper(sku),1,19) order by round(avg(inpf.amount),2) desc) as ranking from erp.input_factor  inpf

我不断收到错误： 分区依据表达式引用在 [5：40] 时既未分组也不聚合的列 SKU

当然，我不希望将 sku 添加到组中，这就是为什么它在 over 子句中的原因。 这里看起来有什么问题？

实际查询：

select sq.fab_id , sq.sku as sku
from
(
select upper(inpf.fab_id) as fab_id, substr(upper(sku),1,19) as sku ,round(avg(inpf.amount),2) as amount,
rank() over (partition by substr(upper(sku),1,19) order by round(avg(inpf.amount),2) desc) as ranking ,
fi.main_construction as construct from erp.input_factor  inpf
left join
erp.fabric_information fi
on upper(inpf.fab_id) = upper(fi.fab_id)
where length(inpf.fab_id) > 3
group by inpf.fab_id , substr(upper(sku),1,19) , fi.main_construction
) sq
where (sq.construct = 1)

Answer 1

这是BigQuery的特点。 一个简单的解决方案是子查询：

select sq.fab_id , sq.sku as sku
from (select upper(inpf.fab_id) as fab_id, sku19 as sku, 
round(avg(inpf.amount),2) as amount,
rank() over (partition by sku19 order by inpf.amount desc) as ranking,
fi.main_construction as construct
from erp.input_factor inpf left join
(select fi.*, substr(upper(sku), 1, 19) as sku19
from erp.fabric_information fi
) fi
on upper(inpf.fab_id) = upper(fi.fab_id)
where length(inpf.fab_id) > 3
group by inpf.fab_id, sku19 , fi.main_construction
) sq
where sq.construct = 1;

注意：你没有注意到sku来自哪里，所以我只是猜测。