postgresql 简化一连串的计算

jslywgbw 于 2023-06-22 发布在 PostgreSQL

关注(0)|答案(2)|浏览(137)

我想做一个类似的查询：

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';

但据我所知，SQL不允许引用在同一个SELECT中创建的列。所以我必须做一些类似的事情：

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

有什么方法可以简化这样的查询吗？我用的是PostgreSQL。

postgresql

来源：https://stackoverflow.com/questions/76527576/simplify-a-chain-of-computations

2条答案

按热度按时间

8yparm6h1#

你可以用ctes而不是子查询来重写它，这会使它更好地可读性，你可以更好地调试它。
但你可以

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';

你应该对它进行基准测试

赞(0）回复(0）举报 2023-06-22

a1o7rhls2#

有一个窗口函数基于另一个窗口函数，它不能嵌套在同一个SELECT列表中。因此，您至少需要两个查询级别。但这个最低限度似乎是可行的：

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
您可以使用专用的（100%等效）GREATEST()和LEAST()替换冗长的CASE表达式。
您可以使用WINDOW子句来避免在同一SELECT列表中为多个窗口函数拼写相同的帧。会导致相同的查询计划，因此对性能没有影响。手册：
... WINDOW子句在多个窗口函数需要相同的窗口定义时节省了键入。
总的来说，我希望我的重写只会稍微快一点。但是您要求的是简化的查询，而不是性能，所以这是可以的。

赞(0）回复(0）举报 2023-06-22

我来回答

postgresql 简化一连串的计算

2条答案

相关问题

热门标签

最新问答