Hive嵌套SUM over partition报错Expression not in GROUP BY key如何解决
问题原因
- 语法特性不兼容:Hive 语法解析暂不支持在窗口函数内直接嵌套聚合函数(即
sum(sum(xxx)) over()写法),这类写法在标准SQL、Presto等引擎中已被支持,但Hive需要将聚合和窗口计算拆分执行。 - 字段笔误:你原查询存在字段名错误,建表时的销量字段为
quantity,查询里误用了不存在的revenue字段,也会触发语义校验报错。
解决方案
将内层聚合和外层窗口累计求和拆分为两个步骤执行即可,以下两种写法均可正常运行:
写法1:子查询实现(兼容性最好,全Hive版本支持)
select store, item, sum(item_total) over (partition by store order by item) as cumulative_quantity from ( -- 内层先按store+item分组聚合出每个商品的总销量 select store, item, sum(quantity) as item_total from test group by store, item ) t
写法2:CTE实现(Hive 2.0及以上版本支持,可读性更高)
with item_agg as ( select store, item, sum(quantity) as item_total from test group by store, item ) select store, item, sum(item_total) over (partition by store order by item) as cumulative_quantity from item_agg
输出结果
两种写法执行后都会得到你预期的结果:
| store | item | cumulative_quantity |
|---|---|---|
| depot | 101 | 8 |
| depot | 102 | 12 |
内容的提问来源于stack exchange,提问作者d-_-b
相关产品推荐
相关产品推荐

