TimPelgrim
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 ammount. I need to determine the sum of all the ratings but also the cumulative sum of the ammount 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.
P.S. I asked this question on Stackoverflow: https://stackoverflow.com/questions/61256005/i-cannot-add-a-specific-frame-clause-in-a-window-using-ectos-over-2-function
Trending in Questions
Other Trending Topics
Categories:
Sub Categories:
Forums
Popular Tags
- #ecto
- #liveview
- #troubleshooting
- #learning-elixir
- #library
- #deployment
- #erlang
- #testing
- #genserver
- #mix
- #absinthe
- #remote-other
- #otp
- #plug
- #how-to-question
- #macros
- #postgres
- #elixirconf
- #channels
- #exunit
- #discussion
- #code-sync
- #podcasts
- #javascript
- #onsite
- #dialyzer
- #docker
- #authentication
- #umbrella
- #full-time-contract
- #podcasts-by-brainlid
- #ecto-query
- #elixirconf-us
- #blog-post
- #ai
- #elixir-ls
- #phoenix_html
- #iex
- #graphql
- #genstage
- #websockets
- #supervisor
- #advent-of-code
- #distillery
- #processes
- #api
- #forms
- #hex
- #security
- #metaprogramming











Showing Posts 1 to 2- Show Best Posts
- Show All (oldest first)
- Show All (newest first)
idi527
Could something like this work:
For reference: Ecto.Query — Ecto v3.14.0
TimPelgrim
Wow, it’s right there isn’t it. I was so sure I read all the documentation… Thanks a lot, this is indeed what I was looking for.