Question 191296 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – 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.

Tags:

2

Answers


  1. 0

    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 diff ELSE 0 END AS gain,
  CASE WHEN close - LAG(close) OVER (ORDER BY time)  < 0 THEN diff ELSE 0 END AS loss,
  AVG(CASE WHEN close - LAG(close) OVER (ORDER BY time)  > 0 THEN diff 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 diff 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 diff ELSE 0 END ) OVER (ORDER BY time ROWS 40 PRECEDING) 
  / AVG(CASE WHEN close - LAG(close) OVER (ORDER BY time)  < 0 THEN diff 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 diff ELSE 0 END ) OVER (ORDER BY time ROWS 40 PRECEDING) 
  / AVG(CASE WHEN close - LAG(close) OVER (ORDER BY time)  < 0 THEN diff ELSE 0 END) OVER (ORDER BY time ROWS 40 PRECEDING)), 0)) as rsi
FROM candles_5min
WHERE symbol = 'AAPL';

    you should benchmark it

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search