执行含窗口函数的SQL时遇PARTITION BY引用SKU列报错求助

嘿，先给你拆解下第一次简化查询报错的问题哈：
你写的这段SQL：

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

这里的核心矛盾是：你用了聚合函数avg(inpf.amount)，但整个查询没有加GROUP BY子句。SQL里有个规则——用聚合函数的时候，要么把非聚合的列都放进GROUP BY，要么用窗口函数来处理分组逻辑，但你这里把聚合和窗口分区混在一起用了：

• avg(inpf.amount)会默认把全表数据聚合成一个单一值，但你同时又让窗口函数按SKU前缀分区，这就相当于让SQL既要全局聚合，又要按SKU前缀拆分，它肯定懵了，所以才会报错说SKU相关的表达式既没分组也没聚合。

再看你提供的实际查询，其实这个写法逻辑是通顺的呀！你在子查询里已经按inpf.fab_id、substr(upper(sku),1,19)、fi.main_construction做了分组，算出每个分组的平均amount，然后用rank()窗口函数按SKU前缀分区、按平均金额降序排名，这个逻辑是成立的。不过可以给你提个小优化点：

小优化建议 #

窗口函数里的排序字段不用重复计算round(avg(inpf.amount),2)，直接用分组后已经算好的amount别名就行，既简洁又高效：

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,
           -- 直接用分组后的amount字段排序，不用重复计算
           rank() over (partition by substr(upper(sku),1,19) order by amount 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)

要是你用的SQL方言不支持在窗口函数里引用分组后的别名（比如某些老版本的MySQL），那再把order by amount换回order by round(avg(inpf.amount),2) desc就行，但前者肯定是更优的选择。

总结一下：第一次的错误是因为你在无GROUP BY的查询里混用了聚合函数和窗口分区，导致SQL无法解析；而你的实际查询写法是正确的，优化一下排序字段会更棒~

内容的提问来源于stack exchange，提问作者systemdebt