Hive nested SUM over partition - error Expression not in GROUP BY key

Question

I'm trying to get cumulative sum in one query. It works fine in SQL, PRESTO etc. but not in HIVE, which throws error msg.

create table test(store varchar(10), item int, quantity int)
insert into test
select 'depot',101,1
union select 'depot',101,2
union select 'depot',101,5
union select 'depot',102,1
union select 'depot',102,3

store	item	revenue
depot	101	1
depot	101	2
depot	101	5
depot	102	1
depot	102	3

select store, item,
sum(sum(revenue)) over (partition by store order by item)
from test
group by store, item

Expected output:

store	item	quantity
depot	101	8
depot	102	12

Error :

[Code: 40000, SQL State: 42000] Error while compiling statement: FAILED: SemanticException Failed to breakup Windowing invocations into Groups. At least 1 group must only depend on input columns. Also check for circular dependencies. Underlying error: org.apache.hadoop.hive.ql.parse.SemanticException: Line 1:24 Expression not in GROUP BY key 'revenue'

Any suggestions ?

Answer 1

You can separate the queries:

select store, item, sum(sum_revenue) over (partition by store order by item) as revenue
from (
    select store, item, sum(revenue) as sum_revenue
    from test
    group by store, item
) as t