CODEWITHSUNDEEP
sqlpostgresqlpostgresql-12
AmitA
AmitA

Reputation: 3245

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

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:

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!

Upvotes: 0

Views: 219

Answers (2)

Popeye
Popeye

Reputation: 35910

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269983

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;

Upvotes: 0

Related Questions