PostgreSQL如何逐行比对查询各id对应的版本降级记录

可行性说明 #

该需求完全可以在PostgreSQL中实现，核心思路是使用窗口函数LAG()对相同id的记录按时间升序排序，获取上一条记录的版本号，对比当前版本号小于上一条版本号即可判定为降级。

结构化查询结果实现 #

假设你的表名为version_records，查询语句如下：

SELECT 
  id,
  prev_version AS version,
  current_version AS downgraded_to,
  ts
FROM (
  SELECT
    id,
    version AS current_version,
    LAG(version) OVER (PARTITION BY id ORDER BY ts) AS prev_version,
    ts
  FROM version_records
) AS t
WHERE prev_version > current_version
ORDER BY id, ts;

运行该语句即可得到你要求的结构化输出结果。

文本提示输出实现 #

在上述逻辑基础上，对字段做字符串拼接即可：

SELECT 
  'id:' || id || ' downgraded their version from ' || prev_version || ' to ' || current_version || ' at ' || ts AS downgrade_notice
FROM (
  SELECT
    id,
    version AS current_version,
    LAG(version) OVER (PARTITION BY id ORDER BY ts) AS prev_version,
    ts
  FROM version_records
) AS t
WHERE prev_version > current_version
ORDER BY id, ts;

运行该语句即可得到你要求的文本提示格式输出。

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