I use ecto to query a Postgres database and I need to add a Window function to keep a cumulative sum. This works fine except for duplicate values of the "amount" in question, they are added together for the cumulative sum. In raw sql the solution is to add a custom frame clause (ROWS UNBOUNDED PRECEDING) but there is no obvious way to add this in ecto using the query DSL.
In our database we have multiple products, most of which receive multiple ratings with an amount. I need to determine the sum of all the ratings but also the cumulative sum of the amount within a specific subset (not relevant to the problem, just for some context). An example of the current query is:
from(p in Product,
left_join: ratings in assoc(p, :ratings),
select: [
p.id,
sum(ratings.amount),
sum(sum(ratings.amount))
|> over(
partition_by: p.selection_0,
order_by: sum(ratings.amount)
)
],
group_by: p.id
)
As I said, this works fine except for products that have an equal total amount. This is to be expected as it works the same in raw postgresql, as asked and answered in this question: Duplicate lines with postgres window functions
I cannot find an obvious way to add the frame_clause to the window created using the over/2 function. I thought about using fragments but the expect an keylist notation (e.g. where: fragment("...")) which is not applicable to the frame_clause.
Looks like you got your answer over on the Elixir Forums:
from(p in Product,
left_join: ratings in assoc(p, :ratings),
select: [
p.id,
sum(ratings.amount),
over(sum(sum(ratings.amount)), :w1)
],
windows: [
w1: [
partition_by: p.selection_0,
order_by: sum(ratings.amount),
frame: fragment("rows unbounded preceding")
]
],
group_by: p.id
)
For reference: https://hexdocs.pm/ecto/Ecto.Query.html#windows/3-frame