I was trying to create postgres query to return open and close price of market bars for different intervals (LAST 7 DAYS, LAST MONTH, LAST 6 MONTHS, etc ...). It seemed easy to do it in simple query but it is not easy as I thought (or I do not know how to do it).
To simplify things, I created 2 examples in dbfiddle which I will shortly explain:
https://dbfiddle.uk/4iPeLzM6 : inserted several bars and in query created 2 intervals, one starting from '2023-03-12' and another one starting from '2023-03-15'. Joining bars with bar_intervals and result seems logical, P1 has all bars and P2 has bars starting from '2023-03-15'
https://dbfiddle.uk/NyoIG1JC : tried to get open price and close price for P1 and P2 intervals, and I would expect this result:
range|open |close
P1 | 10.2| 11.2
P2 | 11.7| 11.2
I assume that window function is not working as I think it does, but it would be helpful if someone could explain why. Especially, it is confusing because in first fiddle result is as I would expect.
Window functions are a bit complicated, but what you are searching for is
The problem is the window, as the first window function sets it, the same row applies to the second column as it has the same window, so you need to define a second window to get your wanted vlaues
with bar_intervals as (select 'P1' as range, '2023-03-12'::timestamp as start_date UNION select 'P2', '2023-03-15')
select distinct on (range)
range, first_value(open) over (PARTITION BY range order by datetime),
first_value(close) OVER (PARTITION BY range order by datetime DESC )
from bar_intervals join bars on datetime >= start_date
| range | first_value | first_value |
|---|---|---|
| P1 | 10.2 | 11.2 |
| P2 | 11.7 | 11.2 |