I'm writing a query which looks something like this:
select parent.id,
parent.date,
sum(child.amount) filter (where child.is_ok) as child_sum,
sum(sum(child.amount) filter (where child.is_ok)) over (order by parent.date)
from parent
left join child on parent.id = child.parent_id
group by parent.id, parent.date
order by parent.date desc
As you can see, I'm using a window function to get a running total over child.amount
.
The question is, is there any standard or non-standard way to reference the child_sum
without copying its expression inside the window function sum
?
I'm using Postgres 10.
You can use a subquery:
SELECT id, date, child_sum,
sum(child_sum) over (order by date)
FROM (SELECT parent.id,
parent.date,
sum(child.amount) FILTER (WHERE child.is_ok) AS child_sum
FROM parent
LEFT JOIN child ON parent.id = child.parent_id
GROUP BY parent.id, parent.date
) AS subq
ORDER BY date DESC;