Presto Window Function - (sum over partition by)

I have the following Presto 2 tables, one storing budget information by client & day, and the other one storing spend information by client & day

select day, client_id, budget_id, budget_period, budget_amount from budget_table 

select day, client_id, spend from spend_table 

Below is desired output:

I have tried

select s.day, s.client_id, b.budget_id, b.budget_period, b.budget_amount, s.spend, case when b.budget_period = 'daily' then s.spend when b.budget_period = 'monthly' then sum(s.spend) over (partition by b.budget_id, month(date(s.day))) when as spend_over_period = 'one_time' then sum(s.spend) over (partition by b.budget_id) end as budget_over_period from spend_table as s join budget_table as b on s.day = b.day and s.client_id = b.client_id group by 1,2,3,4,5,6 

But, I get u'EXPRESSION_NOT_AGGREGATE' error. Does anybody know how to query to get the desired output in Presto?

1 Answer

You can remove group by clause completely and use ordering and frame for your window functions with order by date(s.day) and range between unbounded preceding and current row:

select s.day, s.client_id, b.budget_id, b.budget_period, b.budget_amount, s.spend, case when b.budget_period = 'daily' then s.spend when b.budget_period = 'monthly' then sum(s.spend) over (partition by b.budget_id, month(date(s.day)) order by date(s.day) range between unbounded preceding and current row) when b.budget_period = 'one_time' then sum(s.spend) over (partition by b.budget_id order by date(s.day) range between unbounded preceding and current row) end as spend_over_period from spend_table as s join budget_table as b on s.day = b.day and s.client_id = b.client_id order by 2,3,1 

Output:

ncG1vNJzZmirpJawrLvVnqmfpJ%2Bse6S7zGiorp2jqbawutJobXFxZGyGdYOOqameq6Skeri1zZ2msGWWqrukwMiopWarpaJ6sMLEq2SpmaKptrW1zqdkm7E%3D