如何在PostgreSQL中查询字段新值与上一历史值的对应关系？

完全可以实现，用PostgreSQL内置的窗口函数LAG()就能直接搞定，不需要额外编写函数。

假设你的表结构（可根据实际情况调整） #

先假设存储UI字段历史记录的表结构如下，核心需要有字段名称、字段值、记录时间/排序字段：

CREATE TABLE ui_field_history (
    id SERIAL PRIMARY KEY,
    field_name VARCHAR(50) NOT NULL, -- UI字段的标识，比如"用户名""手机号"
    field_value VARCHAR(255) NOT NULL, -- 字段的历史取值
    record_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP -- 记录产生的时间，用于排序取历史值
);

实现查询的SQL语句 #

SELECT
    field_name AS "UI字段名称",
    field_value AS "当前值",
    -- 获取同一字段的上一个历史值，无历史值时返回NULL
    LAG(field_value) OVER (
        PARTITION BY field_name 
        ORDER BY record_time ASC
    ) AS "上一个历史值",
    record_time AS "记录时间"
FROM ui_field_history
ORDER BY field_name, record_time;

语句说明 #

• LAG(field_value)：专门用于获取当前行的上一行指定字段值的窗口函数
• PARTITION BY field_name：按UI字段名称分组，确保只在同一个字段的历史记录中匹配上一个值
• ORDER BY record_time ASC：按记录时间升序排序，保证取到的是时间上的前一个历史值

自定义无历史值的显示内容 #

如果希望第一条记录（无历史值时）不显示NULL，可以给LAG()加第三个参数指定默认值：

LAG(field_value, 1, "无历史值") OVER (
    PARTITION BY field_name 
    ORDER BY record_time ASC
) AS "上一个历史值"

查询结果示例 #

内容的提问来源于stack exchange，提问作者Luis Enrique Garduno Morales