POSTGRES：单连接查询获取最大ts对应数据及记录总数

算力阿峰

2026-7-31

关联表分组统计与最新记录查询解决方案

需求说明

现有alarm和alarm_data两个带外键关联的表，需要实现：

统计每个alarm_id对应的alarm_data记录总数
获取每个alarm_id下alarm_data表中最新ts对应的value值
最终每个alarm_id仅返回一条包含alarm_id、最新ts、记录总数的结果

问题根源

之前的查询因将value加入GROUP BY子句，导致同一alarm_id下不同value会生成多条记录，不符合需求。

解决方案

方案1：窗口函数+子查询

通过窗口函数标记最新记录，同时单独统计总数，最后关联结果：

SELECT 
    a.alarm_id,
    ad_latest.ts AS latest_ts,
    ad_latest.value AS latest_value,
    ad_count.total_records
FROM alarm a
LEFT JOIN (
    -- 统计每个alarm_id的记录总数
    SELECT 
        alarm_id,
        COUNT(*) AS total_records
    FROM alarm_data
    GROUP BY alarm_id
) ad_count ON a.alarm_id = ad_count.alarm_id
LEFT JOIN (
    -- 筛选每个alarm_id下最新的记录
    SELECT 
        alarm_id,
        ts,
        value,
        ROW_NUMBER() OVER (PARTITION BY alarm_id ORDER BY ts DESC) AS rn
    FROM alarm_data
) ad_latest ON a.alarm_id = ad_latest.alarm_id AND ad_latest.rn = 1;

方案2：聚合函数+相关子查询

直接按alarm_id分组，通过聚合函数获取最新ts，再用子查询匹配对应value：

SELECT 
    ad.alarm_id,
    MAX(ad.ts) AS latest_ts,
    -- 根据alarm_id和最新ts获取对应value
    (SELECT value FROM alarm_data WHERE alarm_id = ad.alarm_id AND ts = MAX(ad.ts)) AS latest_value,
    COUNT(*) AS total_records
FROM alarm_data ad
GROUP BY ad.alarm_id;