Partition by in Impala SQL throwing an error

问题:

I am trying to calculate the running total of loss by the months on Impala using TOAD

The following query is throwing the error -select list expression not produced by aggregation output (missing from group by clause )

select
segment,
year(open_dt) as open_year,
months,
sum(balance)
sum(loss) over (PARTITION by segment,year(open_dt) order by months) as NCL 
from

tableperf
where
year(open_dt) between 2015 and 2018

group by 1,2,3

回答1:

You are mixing aggregation and window functions. I think you might want:

select segment, year(open_dt) as open_year, months,
       sum(balance)
       sum(sum(loss)) over (PARTITION by segment, year(open_dt) order by months) as NCL 
from tableperf
where year(open_dt) between 2015 and 2018
group by 1, 2, 3;

This calculates the cumulative loss within each year. Note the use of sum(sum(loss)). The inner sum() is an aggregation function. The outer sum() is a window function.