PostgreSQL如何按用户分组关联最近3条值合并为单行

直接用窗口函数实现即可，性能远高于自关联、lateral join 类写法，单次表扫描即可完成计算，数据量越大优势越明显。

实现SQL #

替换代码里的表名为你实际使用的表名即可：

SELECT
  id,
  date,
  "user",
  value,
  COALESCE(
    array_to_string(
      array_agg('''' || value || '''') OVER (
        PARTITION BY "user"
        ORDER BY date DESC, id DESC -- 加id做兜底排序，避免同时间数据顺序混乱
        ROWS BETWEEN 1 FOLLOWING AND 3 FOLLOWING
      ),
      ', '
    ),
    ''
  ) AS "last values"
FROM your_table_name
ORDER BY "user", date DESC;

逻辑说明 #

• 窗口函数按user字段分区，同用户数据按时间倒序排列，最新的数据排在最前
• 帧规则ROWS BETWEEN 1 FOLLOWING AND 3 FOLLOWING表示取当前行之后的最多3行数据，也就是时间早于当前行的、距离当前行最近的3条历史记录，完全匹配需求
• array_agg把范围内的value值拼接为数组，每个值前后拼接单引号匹配要求的输出格式，再用array_to_string转成逗号分隔的字符串
• COALESCE函数处理没有历史数据的最早记录，把null转为空字符串
• 排序加id DESC是兜底逻辑，避免同一用户存在完全相同时间的记录时，排序结果不稳定

性能说明 #

该写法时间复杂度为O(n)，仅需一次表扫描加窗口计算，百万级数据也能快速出结果。不推荐使用自关联、left join lateral类写法，这类写法本质是逐行关联查询，数据量上来后性能会明显下降。

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