PostgreSQL如何按instrument分组获取每组最新符合筛选条件的行

下面分两种常用实现方式满足需求：

方式1：窗口函数法（兼容MySQL 8+、PostgreSQL、SQL Server等绝大多数主流数据库） #

通过ROW_NUMBER()窗口函数给同instrument下的行按时间倒序排序，取排序第一的行即可：

SELECT instrument, ts, status, analysis_start -- 按需补充你需要查询的其余字段
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY instrument ORDER BY ts DESC) AS row_rank
    FROM cookbook
    WHERE status = true AND ts >= now() - interval '5 minute'
) AS temp
WHERE row_rank = 1;

如果你的数据库支持CTE和字段排除语法，也可以写得更简洁：

WITH temp AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY instrument ORDER BY ts DESC) AS row_rank
    FROM cookbook
    WHERE status = true AND ts >= now() - interval '5 minute'
)
SELECT * EXCLUDE (row_rank)
FROM temp
WHERE row_rank = 1;

注意：如果同一个instrument存在多条ts完全相同的最新记录，以上写法默认只返回其中一条，如果你需要保留所有时间相同的最新记录，把ROW_NUMBER()替换为RANK()即可。

方式2：PostgreSQL专属简化写法 #

如果你用的是PostgreSQL，可以用DISTINCT ON语法实现更高效简洁的查询：

SELECT DISTINCT ON (instrument) *
FROM cookbook
WHERE status = true AND ts >= now() - interval '5 minute'
ORDER BY instrument, ts DESC;

DISTINCT ON (instrument)会自动保留每个instrument分组下排序后的第一条记录，这里按ts DESC倒序排序，取到的就是每个设备最新的那条数据。

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