PostgreSQL中按策略计算前一时刻余额差值的SQL实现

直接使用LAG()窗口函数就能获取同策略前一时刻的TotalBalancePosition值，以下是符合需求的SQL实现：

SELECT
  TimeUTC,
  Strategy,
  TotalBalancePosition,
  PriceInDollars,
  -- 按公式计算Return，同时处理除数为0的情况避免报错
  CASE
    WHEN LAG(TotalBalancePosition) OVER (PARTITION BY Strategy ORDER BY TimeUTC) = 0 THEN NULL
    ELSE (TotalBalancePosition - LAG(TotalBalancePosition) OVER (PARTITION BY Strategy ORDER BY TimeUTC) - PriceInDollars) 
         / LAG(TotalBalancePosition) OVER (PARTITION BY Strategy ORDER BY TimeUTC)
  END AS Return
FROM a
ORDER BY Strategy, TimeUTC;

关键细节说明： #

• PARTITION BY Strategy：限定仅在同一个策略分组内查找前一时刻的记录，不会跨策略取数
• ORDER BY TimeUTC：保证LAG()获取的是当前记录的前一时刻数据，而非无序的前一行
• CASE分支处理除数为0的场景：如果前一时刻的TotalBalancePosition为0，直接返回NULL，避免除法运算报错

如果想简化重复的LAG()调用，让SQL更简洁易读，可以用子查询提前计算前值：

SELECT
  TimeUTC,
  Strategy,
  TotalBalancePosition,
  PriceInDollars,
  CASE
    WHEN prev_balance = 0 THEN NULL
    ELSE (TotalBalancePosition - prev_balance - PriceInDollars) / prev_balance
  END AS Return
FROM (
  SELECT
    *,
    LAG(TotalBalancePosition) OVER (PARTITION BY Strategy ORDER BY TimeUTC) AS prev_balance
  FROM a
) AS sub
ORDER BY Strategy, TimeUTC;

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