计算语句中的WHERE子句问题：如何获取上月水果数量并计算lost列

现有数据表：

需要新增名为lost的列，计算公式为：lost = harvested - (num_of_fruits - num_of_fruits(last_month))。尝试的初始代码报错：

select 
    id,
    "month",
    num_of_fruits,
    harvested,
    harvested - (num_of_fruits - num_of_fruits WHERE date_trunc('month', "month" - interval '1' month)) as lost,
    -- 其他列
from your_table;

需解决两个问题：

1. SELECT语句中能不能使用WHERE子句？
2. 如何在SELECT内获取上月的num_of_fruits并完成差值计算？

解答 #

1. SELECT子句中不能直接使用WHERE #

WHERE的作用是过滤整行数据，只能出现在SELECT语句的FROM/JOIN之后，或子查询、CTE内部，绝对不能嵌套在单个列的计算表达式里，你的代码违反了SQL语法规范，所以必然报错。

2. 获取上月数据的最优方案：窗口函数LAG() #

要获取同一列的上一行（上月）数据，最简洁高效的方式是用窗口函数LAG()，它可以在当前行中访问结果集里前N行的指定列值。

完整SQL示例

select 
    id,
    "month",
    num_of_fruits,
    harvested,
    -- 计算lost列：第一行无上月数据时结果为NULL，可按需调整
    harvested - (num_of_fruits - LAG(num_of_fruits) OVER (ORDER BY "month")) as lost,
    -- 其他需要选择的列
from your_table
order by "month";

特殊情况处理

对于第一行（2022-01-01），LAG()会返回NULL，导致lost结果为NULL。如果需要给无上月数据的行设置默认值（比如0），可以用COALESCE函数：

harvested - (num_of_fruits - COALESCE(LAG(num_of_fruits) OVER (ORDER BY "month"), 0)) as lost

此时2022-01-01的lost值为3 - (133 - 0) = -130，具体逻辑可根据业务需求调整。

备选方案：自关联子查询（兼容老版本数据库） #

如果你的数据库不支持窗口函数（极少情况），可以用自关联的方式实现：

select 
    t1.id,
    t1."month",
    t1.num_of_fruits,
    t1.harvested,
    t1.harvested - (t1.num_of_fruits - COALESCE(t2.num_of_fruits, 0)) as lost,
    -- 其他列
from your_table t1
left join your_table t2 
    on date_trunc('month', t1."month" - interval '1' month) = date_trunc('month', t2."month")
order by t1."month";

注意：这种方法性能远低于窗口函数，数据量大时不推荐使用。

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