如何简化PostgreSQL中计算RSI的多层嵌套SQL查询？

问题背景 #

我尝试编写如下SQL计算RSI：

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+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 dt.diff < 0 THEN dt.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中有没有办法简化这类查询？

简化方案 #

1. 使用WITH子句（公共表表达式CTE）

CTE可以将多层嵌套的子查询拆分为多个命名的逻辑块，结构更清晰，可读性更强，逻辑和嵌套子查询完全一致，但写法更直观：

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

2. 合并计算步骤，减少层级

可以将gain和loss的判断逻辑直接嵌入窗口函数中，省去单独的中间表，进一步压缩查询层级：

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

3. 极致简洁版（单层子查询）

如果追求最小化查询层级，可将所有计算逻辑整合到一个子查询中，不过可读性会略有下降：

SELECT
    time,
    100 - (100 / NULLIF(1 + (avg_gain / NULLIF(avg_loss, 0)), 0)) AS rsi
FROM (
    SELECT
        time,
        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
    FROM candles_5min
    WHERE symbol = 'AAPL'
) AS calc;

PostgreSQL对CTE的优化能力较强（PostgreSQL 12及以上版本支持智能选择是否物化CTE），使用CTE不会带来额外的性能开销，同时能大幅提升查询的可维护性。

内容的提问来源于stack exchange，提问作者functorial