PostgreSQL中DISTINCT ON如何获取分组第二行而非首行

你之前直接用OFFSET/LIMIT不生效的核心原因是：这两个关键字是对整个查询的全局结果集做截断，不会按station_id分组后对每个组单独做偏移，因此无法直接拿到组内第二行。
下面给两种可直接运行的实现，都能自动过滤仅存在1条读数的站点，匹配你给出的样例预期结果。

方案1：基于DISTINCT ON语法（符合你的使用偏好） #

逻辑分两步：

1. 先查出每个站点最新（reading_id最高）的记录，把这些记录从结果集中排除
2. 对剩下的记录沿用你原本的DISTINCT ON写法，取每个站点剩余记录里最新的一条，就是全局第二新的记录

SELECT DISTINCT ON (station_id) *
FROM readings
WHERE (station_id, reading_id) NOT IN (
    -- 子查询拿到所有站点的最新记录ID
    SELECT DISTINCT ON (station_id) station_id, reading_id
    FROM readings
    ORDER BY station_id, reading_id DESC
)
ORDER BY station_id, reading_id DESC;

基于你提供的样例数据执行，返回结果完全符合预期：

• 站点147排除最新的reading_id=105后，剩余最新记录是reading_id=100
• 站点148排除最新的reading_id=104后，剩余最新记录是reading_id=103
• 站点149只有1条记录，排除后无剩余数据，自动被过滤

方案2：窗口函数实现（大数据量场景推荐） #

用ROW_NUMBER()窗口函数直接给每个站点内的读数按新旧打排名，直接取排名为2的记录即可，逻辑更直观，在数据量较大时执行效率比嵌套DISTINCT ON更高。

WITH ranked_readings AS (
    SELECT
        *,
        -- 按站点分组，读数越新排名越靠前
        ROW_NUMBER() OVER (PARTITION BY station_id ORDER BY reading_id DESC) AS record_rank
    FROM readings
)
SELECT station_id, reading_id, temp, air_pressure
FROM ranked_readings
WHERE record_rank = 2;

关联stations表的扩展写法 #

如果需要关联拿站点的名称、经纬度信息，直接在最终查询环节关联即可，以窗口函数方案为例：

WITH ranked_readings AS (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY station_id ORDER BY reading_id DESC) AS record_rank
    FROM readings
)
SELECT r.*, s.station_name, s.lat, s.lng
FROM ranked_readings r
INNER JOIN stations s ON r.station_id = s.id
WHERE r.record_rank = 2;

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