PostgreSQL分组时如何获取指定列的最新数据？

云上老周

2026-8-20

PostgreSQL + Sequelize 分组后取最新字段值的最优方案

核心方案1：用PostgreSQL原生`DISTINCT ON`语法

这是处理「分组后取每组某排序规则下第一条数据」最简洁高效的方式，比子查询更直观，PostgreSQL对该语法有专门优化。

原生SQL示例

假设你的表名为locations，直接写SQL实现需求：

SELECT DISTINCT ON (address)
  address,
  distance,
  created_at
FROM locations
ORDER BY address, created_at DESC;

这段逻辑会先按address分组，每组内按created_at倒序排列，自动取每组第一条数据，正好匹配你要的「每个address对应最新created_at的distance值」需求。

Sequelize中实现

通过sequelize.literal()嵌入原生语法：

const result = await Location.findAll({
  attributes: [
    [sequelize.literal('DISTINCT ON (address) address'), 'address'],
    'distance',
    'created_at'
  ],
  order: [['address', 'ASC'], ['created_at', 'DESC']],
  raw: true // 可选，返回原始数据对象
});

核心方案2：窗口函数（兼容聚合场景）

如果需要同时搭配SUM这类聚合函数，用ROW_NUMBER()窗口函数更灵活，避免嵌套子查询的繁琐：

原生SQL示例

WITH ranked_data AS (
  SELECT
    address,
    distance,
    created_at,
    SUM(your_column) OVER (PARTITION BY address) AS total_sum,
    ROW_NUMBER() OVER (PARTITION BY address ORDER BY created_at DESC) AS rn
  FROM locations
)
SELECT address, distance, created_at, total_sum
FROM ranked_data
WHERE rn = 1;

Sequelize中实现

直接执行原生查询：

const result = await sequelize.query(`
  WITH ranked_data AS (
    SELECT
      address,
      distance,
      created_at,
      SUM(your_column) OVER (PARTITION BY address) AS total_sum,
      ROW_NUMBER() OVER (PARTITION BY address ORDER BY created_at DESC) AS rn
    FROM locations
  )
  SELECT address, distance, created_at, total_sum
  FROM ranked_data
  WHERE rn = 1;
`, { type: sequelize.QueryTypes.SELECT });