Postgres window functions: applying an aggregate function on a window frame's records that satisfy a condition based on the current row

Question

This question is about SQL syntax for Postgres 12.

Let's assume a stock_prices table with the following columns: ticker, date, price.

I'm interested in performing calculation on the previous 10-day window, such as:

SELECT 
  ticker,
  date, 
  price, 
  AVG(price) 
    OVER (PARTITION BY ticker ORDER BY date ROWS BETWEEN 10 PRECEDING AND 1 PRECEDING) as avg_previous_10d
FROM stock_prices

In addition to the simple average calculation above, I'd like to perform calculations on records within the previous 10-day window that satisfy conditions related to the current record. Specifically, within the previous 10-day window:

Count the number of times the price was higher than the current row's price
Calculate the average of prices that were higher than the current row's price
Find the first time the price was higher than the current row's price

And so on.

I understand that I could perform a self join, such as the answer to this question. My question is - is there a straightforward way to do this using the window functions syntax? Or is self join the only way to go?

Thanks!

score 0 · Answer 1 · answered Dec 25 '20 at 15:53

It sounds like a lateral join might be the best approach. The following assumes you have one row for each day:

SELECT sp.*, sp2.*
FROM stock_prices sp CROSS JOIN LATERAL
     (SELECT AVG(sp2.price) as avg_previous_10d,
             COUNT(*) FILTER (WHERE sp2.price > sp.price) as cnt_highest_previous_10day,
             AVG(sp2.price) FILTER (WHERE sp2.price > sp.price) as avg_higher_previous_10day
      FROM stock_prices sp2
      WHERE sp2.ticker = sp.ticker AND
            sp2.date >= sp.date - INTERVAL '10 DAY' AND
            sp2.date < sp.date
     ) sp2;

This is not 100% equivalent to your query, because it uses calendar time. That might be an advantage. But if you want the version that is exactly equivalent:

SELECT sp.*, sp2.*
FROM stock_prices sp CROSS JOIN LATERAL
     (SELECT AVG(sp2.price) as avg_previous_10d,
             COUNT(*) FILTER (WHERE sp2.price > sp.price) as cnt_highest_previous_10day,
             AVG(sp2.price) FILTER (WHERE sp2.price > sp.price) as avg_higher_previous_10day
      FROM (SELECT sp2.*
            FROM stock_prices sp2
            WHERE sp2.ticker = sp.ticker AND
                  sp2.date < sp.date
            ORDER BY sp2.date DESC
            LIMIT 10
           ) sp2
     ) sp2;

score 0 · Answer 2 · answered Dec 25 '20 at 16:34

Why don't you use self join as follows:

SELECT count(1) as num_times,
       Avg(s10.price) as avg_price,
       Min(s10.date) as first_time_higher_price     
  FROM stock_prices scur
  Join stock_prices s10 on s10.ticker = scur.ticker
 Where scur.date = current_date
   And s10.date >= current_date - interval '10 day' 
   And s10.date < current_date
   And s10.price >= scur.price

Postgres window functions: applying an aggregate function on a window frame's records that satisfy a condition based on the current row

2 Answers2