we are doing a complex Postgres query using the WINDOW clause.
As this is very specific and only applies to Postgres I don’t assume this will be officially supported by Ecto. However I wonder if there is a way to specify custom clauses (similar to
Something along those lines could work:
from s in Salary, select: fragment("avg(salary) OVER w"), custom_clause: "WINDOW w AS (...)"
SELECT avg(salary) OVER w FROM salaries WINDOW w AS (...)
Hope that makes sense.
I’ve just run into the same thing. Did you ever find a solution? (short of using raw SQL and
I ‘think’ fragments should still work fine for it. Do you have a precise example of existing working code and what you want it to do instead?
Yes you can repeat the partitioning clauses in each portion of the
select with fragments. But it gets ‘ugly’ with many columns.
Here’s some SQL we’d like to build in Ecto:
when (first_value(event_details ->> 'map_number') over w)::int % 2 = 0 then 'even'
round(extract(epoch from first_value(created_at) over w))::int,
(first_value(event_details ->> 'map_number') over w)::int,
(first_value(event_details ->> 'correct_count') over w)::numeric /
nullif((first_value(event_details ->> 'total_count') over w)::int, 0)::numeric
window w as (partition by (event_details ->> 'map_number')::int order by created_at desc)
That was an experiment in improving the code from the existing solution of selecting
rank() over … as rank in a sub-query, then selecting from that
where rank = 1. This works perfectly fine, but we were playing around with
first_value to simplify away the sub-select.
Being able to add that last line makes it simpler. Repeating the window function in each part of the select (via a fragment) works, but does not look make it look simpler.
Ah, might need to wait for someone to look at that, not something that I’ve done yet myself, hmm…