PostgreSQL单查询获取最大值、最小值及最大ts对应值

需求说明 #

给定数据表结构及数据如下：

value  ts
2.0    1
3.0    5
7.0    3
1.0    2
5.0    4

需要一次性查询获取：

• value字段的最大值
• value字段的最小值
• 最大ts对应的value值（预期结果：最大值7.0，最小值1.0，最大ts对应值3.0）

核心问题解答 #

1. 能否实现该需求？ #

完全可以实现，但你提供的查询语句无法得到预期结果——聚合函数（max/min）是对全表数据做聚合计算，order by ts desc不会影响聚合函数的输出结果，且多数数据库中并没有可直接在聚合场景使用的first()函数。

2. 关于“返回首个值”的函数 #

多数数据库提供的是窗口函数而非聚合函数来实现“取首个/末个值”的需求，比如FIRST_VALUE()、LAST_VALUE()，也可以结合ROW_NUMBER()来定位目标行。

3. 正确的查询写法 #

方法一：通用子查询写法（适配多数数据库）

通过子查询先找到最大的ts，再关联获取对应的value，同时计算全局的最大、最小值：

SELECT
  MAX(value) AS max_value,
  MIN(value) AS min_value,
  (SELECT value FROM table_name WHERE ts = (SELECT MAX(ts) FROM table_name)) AS value_with_max_ts
FROM table_name;

方法二：窗口函数写法（以MySQL、PostgreSQL为例）

利用窗口函数直接在同一查询中完成全局聚合和目标值提取：

SELECT DISTINCT
  MAX(value) OVER () AS max_value,
  MIN(value) OVER () AS min_value,
  FIRST_VALUE(value) OVER (ORDER BY ts DESC) AS value_with_max_ts
FROM table_name;

也可以先筛选出ts最大的行，再和全局聚合结果关联：

WITH max_ts_row AS (
  SELECT value FROM table_name ORDER BY ts DESC LIMIT 1
)
SELECT
  MAX(t.value) AS max_value,
  MIN(t.value) AS min_value,
  m.value AS value_with_max_ts
FROM table_name t, max_ts_row m;

结果验证 #

执行上述任意正确查询后，会得到符合预期的结果：

max_value | min_value | value_with_max_ts
----------|-----------|-------------------
7.0       | 1.0       | 3.0

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