PostgreSQL如何按客户分组查询当前值与上月值及对应日期对比结果

你需要使用窗口函数LAG()而非FIRST_VALUE()实现需求：FIRST_VALUE()仅能返回分组内排序后的第一条数据，而LAG()可以直接提取当前行的前序行数据，完全匹配取上一期数值、日期的场景。

假设你的业务表名为customer_trans，包含核心字段：customer_id（客户ID）、value（业务数值）、ref_date（参考日期），可直接使用以下查询语句：

SELECT
  customer_id,
  value AS "Value",
  TO_CHAR(ref_date, 'DD-MM-YYYY') AS "Date Reference",
  LAG(value) OVER (PARTITION BY customer_id ORDER BY ref_date ASC) AS "Previous Value",
  TO_CHAR(LAG(ref_date) OVER (PARTITION BY customer_id ORDER BY ref_date ASC), 'DD-MM-YYYY') AS "Previous Date"
FROM
  customer_trans
ORDER BY
  customer_id,
  ref_date;

参数说明 #

• PARTITION BY customer_id：按客户ID分组，每个客户的上一期数据只会取同客户的历史记录，不会跨客户取值
• ORDER BY ref_date ASC：按日期升序排序，保证LAG()取到的是时间上更早的最近一期数据
• 如果你需要过滤掉没有上一期数据的每个客户首条记录，可使用CTE包装后过滤空值：

WITH customer_history AS (
  SELECT
    customer_id,
    value,
    ref_date,
    LAG(value) OVER (PARTITION BY customer_id ORDER BY ref_date ASC) AS prev_value,
    LAG(ref_date) OVER (PARTITION BY customer_id ORDER BY ref_date ASC) AS prev_date
  FROM customer_trans
)
SELECT
  customer_id,
  value AS "Value",
  TO_CHAR(ref_date, 'DD-MM-YYYY') AS "Date Reference",
  prev_value AS "Previous Value",
  TO_CHAR(prev_date, 'DD-MM-YYYY') AS "Previous Date"
FROM customer_history
WHERE prev_value IS NOT NULL
ORDER BY customer_id, ref_date;

内容的提问来源于stack exchange，提问作者Lucas Barbosa Olivieri