Simplify a chain of computations

I would like to make a query similar to this:

SELECT
  time,
  close - LAG(close) OVER (ORDER BY time) AS "diff"
  CASE WHEN diff > 0 THEN diff ELSE 0 END AS gain,
  CASE WHEN diff < 0 THEN diff ELSE 0 END AS loss,
  AVG(gain) OVER (ORDER BY time ROWS 40 PRECEDING) as avg_gain,
  AVG(loss) OVER (ORDER BY time ROWS 40 PRECEDING) AS avg_loss
  avg_gain / avg_loss AS rs,
  100 - (100 / NULLIF(1+rst.rs, 0)) as rsi
FROM candles_5min
WHERE symbol = 'AAPL';

But from what I can tell, SQL doesn't allow references to columns created within the same SELECT. So I have to do something like:

SELECT rst.time, 100 - (100 / NULLIF((1+rst.rs), 0)) as rsi
FROM (SELECT
avgs.time,
avgs.avg_gain / NULLIF(avgs.avg_loss, 0) AS rs
FROM (SELECT glt.time, AVG(glt.gain) OVER (ORDER BY time ROWS 40 PRECEDING) as avg_gain,
    AVG(glt.loss) OVER (ORDER BY time ROWS 40 PRECEDING) AS avg_loss
FROM (SELECT
    dt.time,
    CASE WHEN dt.diff > 0 THEN dt.diff ELSE 0 END AS gain,
    CASE WHEN diff < 0 THEN diff ELSE 0 END AS loss
    FROM (SELECT
    time,
    close - LAG(close) OVER (ORDER BY time) AS "diff"
    FROM candles_5min
    WHERE symbol = 'AAPL') AS dt) AS glt) AS avgs) AS rst

Is there any way to simplify a query like this one? I'm using PostgreSQL.

There is a window function based on another window function, which cannot be nested in the same SELECT list. So you need a minimum of two query levels. But that minimum seems feasible:

SELECT time
     , 100 - (100 / NULLIF((1 + AVG(GREATEST(diff, 0)) OVER w
                          / NULLIF(AVG(LEAST(diff, 0)) OVER w, 0)), 0)) AS rsi
FROM  (
   SELECT time
        , close - LAG(close) OVER (ORDER BY time) AS diff
   FROM   candles_5min
   WHERE  symbol = 'AAPL'
   ) dt
WINDOW w AS (ORDER BY time ROWS 40 PRECEDING);

fiddle

You can replace the lengthy CASE expressions with dedicated (100 % equivalent) GREATEST() and LEAST().

You can use a WINDOW clause to avoid spelling out the same frame for multiple window functions in the same SELECT list. Results in the same query plan, so no effect on performance. The manual:

> ... the WINDOW clause saves typing when the same window definition is needed for more than one window function.

Overall, I expect my rewrite to be only slightly faster. But you asked for a simplified query, not performance, so that's ok.

You could rewrite it with ctes instead of subqueries, that woul keep it better readable and you cann better debug it.

But you can do

SELECT
time,
close - LAG(close) OVER (ORDER BY time) AS "diff",
CASE WHEN close - LAG(close) OVER (ORDER BY time)  > 0 THEN close - LAG(close) OVER (ORDER BY time) ELSE 0 END AS gain,
CASE WHEN close - LAG(close) OVER (ORDER BY time)  < 0 THEN close - LAG(close) OVER (ORDER BY time) ELSE 0 END AS loss,
AVG(CASE WHEN close - LAG(close) OVER (ORDER BY time)  > 0 THEN close - LAG(close) OVER (ORDER BY time) ELSE 0 END ) OVER (ORDER BY time ROWS 40 PRECEDING) as avg_gain,
AVG(CASE WHEN close - LAG(close) OVER (ORDER BY time)  < 0 THEN close - LAG(close) OVER (ORDER BY time) ELSE 0 END) OVER (ORDER BY time ROWS 40 PRECEDING) AS avg_loss,
AVG(CASE WHEN close - LAG(close) OVER (ORDER BY time)  > 0 THEN close - LAG(close) OVER (ORDER BY time) ELSE 0 END ) OVER (ORDER BY time ROWS 40 PRECEDING) 
/ AVG(CASE WHEN close - LAG(close) OVER (ORDER BY time)  < 0 THEN close - LAG(close) OVER (ORDER BY time) ELSE 0 END) OVER (ORDER BY time ROWS 40 PRECEDING) AS rs,
100 - (100 / NULLIF(1+
(AVG(CASE WHEN close - LAG(close) OVER (ORDER BY time)  > 0 THEN close - LAG(close) OVER (ORDER BY time) ELSE 0 END ) OVER (ORDER BY time ROWS 40 PRECEDING) 
/ AVG(CASE WHEN close - LAG(close) OVER (ORDER BY time)  < 0 THEN close - LAG(close) OVER (ORDER BY time) ELSE 0 END) OVER (ORDER BY time ROWS 40 PRECEDING)), 0)) as rsi
FROM candles_5min
WHERE symbol = 'AAPL';

you should benchmark it