PostgreSQL - how to create a window frame with a condition?

Question

Let's say I have the following table:

CREATE TABLE stock_prices ( stock TEXT NOT NULL, date DATE NOT NULL, price REAL NOT NULL, UNIQUE (stock, date) ); 

I want to calculate for every day the highest price for each stock in the previous 3-month window.

I cannot make a simple coincidence with date - INTERVAL(3 'MONTH') , since my table stock_price has some "holes" for holidays and weekends. Similarly, a naive window doesn't work either:

 SELECT stock, date, LAST_VALUE(price) OVER (PARTITION BY stock ORDER BY date ROWS 90 PRECEDING) FROM stock_prices 

I almost need a window frame here with a condition based on the current line. Is this possible in PostgreSQL?

Answer 1

You can fill the table with the missing rows using the generate_series () function, so the window function will return the correct data. You can select a report period with start and end dates in generate_series () :

 select stock, date, price, max(price) over (partition by stock order by date rows 90 preceding) from ( select d::date as date, s.stock, sp.price from generate_series('2016-01-01'::date, '2016-07-28', '1d') g(d) cross join ( select distinct stock from stock_prices ) s left join stock_prices sp on gd = sp.date and s.stock = sp.stock ) s order by 1, 2; 

This is an alternative solution with a simple subquery:

 select stock, date, price, ( select max(price) from stock_prices sp2 where sp2.stock = sp1.stock and sp2.date >= sp1.date- interval '90days' and sp2.date <= sp1.date ) highest_price from stock_prices sp1 order by 1, 2; 

will be much more expensive. In this case, you should definitely use the index

 create index on stock_prices (stock, date); 

Answer 2

The generate_series parameter should work well, although since the months are not always 30 days, it will not always correspond to calendar months.

If you want to use the interval, you can also do independent merging and aggregation. This will combine each row into all rows that meet the criteria (in this case, I set the interval to 1 week) and get the Max value within this result set:

 select a.stock, a.date, a.price, max( b.price ) from stock_prices as a left join stock_prices as b on a.stock = b.stock and b.date between (a.date - interval '7 days') and a.date group by a.stock, a.date, a.price order by a.stock, a.date 

SQL script here: http://sqlfiddle.com/#!15/bbec8/2