PostgreSQL如何同表二次查询筛选出用户存款金额下降的记录

SQL的执行顺序中，WHERE子句的执行优先级高于窗口函数计算、以及SELECT阶段的别名定义，所以你直接在WHERE中引用窗口函数生成的pre_deposit别名时，这个字段还没被计算出来，自然会提示不存在。

你提到的四个选项里，left-join（左连接）是唯一适配该需求的选项，但它并不是这个场景的最优解：

• union的作用是合并多个查询的结果集，完全不满足对比同用户前后存款的需求
• right-join、outer-join 要么会生成大量无用的空值记录，要么逻辑冗余，没有使用必要

最优实现方案（无需二次查表） #

直接用子查询或者CTE（公共表表达式）包裹带窗口函数的逻辑，在外层加过滤条件即可，性能比关联查询更好，逻辑也更简洁：

WITH customer_with_prev AS (
    SELECT person_id, 
           employee_id,
           deposit,
           ts,
           lag(deposit) OVER client_window AS pre_deposit
    FROM customer 
    WINDOW client_window AS (PARTITION BY person_id ORDER BY ts)
)
SELECT person_id, employee_id, deposit, ts
FROM customer_with_prev
WHERE pre_deposit > deposit;

执行后即可得到你期望的输出结果。

左连接实现方案（符合你要求的同表二次查询逻辑） #

如果你必须用同表二次查询的方式实现，左连接的写法如下：

SELECT c1.person_id, c1.employee_id, c1.deposit, c1.ts
FROM customer c1
LEFT JOIN customer c2 
  ON c1.person_id = c2.person_id 
  AND c2.ts = (
      SELECT MAX(ts) FROM customer c3 
      WHERE c3.person_id = c1.person_id AND c3.ts < c1.ts
  )
WHERE c2.deposit > c1.deposit;

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