SQL如何在SUM聚合中使用LAG函数获取前一行值完成求和计算

窗口函数不能直接嵌套在聚合函数中使用，你可以先通过子查询或者公共表表达式（CTE）预计算每一行对应的上一行POWERLIMIT值，再在外层执行聚合计算即可，完整实现如下：

使用CTE实现（兼容大部分主流数据库） #

WITH epm_with_prev AS (
  SELECT 
    INVERTER1POWER,
    INVERTER2POWER,
    -- 按时间戳排序取上一行的POWERLIMIT，第一行无匹配时默认返回0
    LAG(POWERLIMIT, 1, 0) OVER (ORDER BY TIMESTAMP) AS prev_powerlimit
  FROM EPM1
  WHERE TIMESTAMP >= TO_UTC_TIMESTAMP_TZ(:timefrom) AND TIMESTAMP <= TO_UTC_TIMESTAMP_TZ(:timeto)
)
SELECT SUM(
  CASE 
    WHEN (INVERTER1POWER + INVERTER2POWER) < (250000 * prev_powerlimit) 
    THEN INVERTER1POWER + INVERTER2POWER 
    ELSE 0 
  END
) AS SUM1
FROM epm_with_prev;

使用嵌套子查询实现 #

如果你的数据库不支持CTE语法，换用嵌套子查询也能达到相同效果：

SELECT SUM(
  CASE 
    WHEN (INVERTER1POWER + INVERTER2POWER) < (250000 * prev_powerlimit) 
    THEN INVERTER1POWER + INVERTER2POWER 
    ELSE 0 
  END
) AS SUM1
FROM (
  SELECT 
    INVERTER1POWER,
    INVERTER2POWER,
    LAG(POWERLIMIT, 1, 0) OVER (ORDER BY TIMESTAMP) AS prev_powerlimit
  FROM EPM1
  WHERE TIMESTAMP >= TO_UTC_TIMESTAMP_TZ(:timefrom) AND TIMESTAMP <= TO_UTC_TIMESTAMP_TZ(:timeto)
) t

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