PostgreSQL中获取指定列首个值的最优方案咨询

安全阿岳

2026-8-3

问题

我有一张结构如下的表：

ts,ticker,m1,m5,m15,m30,h1,h2,h4,d1,high,vwap,low
2020-12-03 00:00:00.000000,DOGEUSDT,0.00336,0.00336,0.00336,0.00336,0.00336,0.00336,0.00336,0.00336,0.003366,0.0033595364,0.003356
2020-12-03 00:01:00.000000,DOGEUSDT,0.00336,0.00336,0.00336,0.00336,0.00336,0.00336,0.00336,0.00336,0.003371,0.0033696603,0.003365
2020-12-03 00:02:00.000000,DOGEUSDT,0.00337,0.00337,0.00337,0.00337,0.00337,0.00337,0.00337,0.00337,0.003376,0.0033727777,0.00337
2020-12-03 00:03:00.000000,DOGEUSDT,0.00337,0.00337,0.00337,0.00337,0.00337,0.00337,0.00337,0.00337,0.003376,0.0033747195,0.003373

我的查询需求固定：针对特定ticker，返回某时间范围内的聚合数据。聚合数据中，timestamp需取按时间排序后的第一条记录的值，ticker（所有返回行均相同）也取首行的值。

目前我有三种实现方式：

方式一：

SELECT
    (array_agg(ts))[1] as ts,
    (array_agg(ticker))[1] as ticker,

方式二：

SELECT
    min(ts) as ts,
    min(ticker) as ticker,

方式三：

SELECT
    min(ts) as ts,
    min(ticker) as ticker,
FROM ...
GROUP BY ts, ticker
LIMIT 1

请问哪种方式是推荐方案？原因是什么？

推荐方案及原因

推荐使用方式二，原因如下：

性能最优：min()是数据库原生聚合函数，优化器能直接利用索引（如果ts或ticker建了索引），执行效率远高于array_agg()——后者需要把所有符合条件的记录值存入数组再取第一个，数据量越大性能差距越明显。
逻辑精准：需求是取时间排序后的第一条ts，min(ts)刚好对应时间范围内最早的记录时间，完全匹配需求；而ticker是查询时指定的特定值，所有返回行的ticker都相同，min(ticker)自然就是这个固定值，逻辑完全成立。
代码简洁易读：写法直观，其他维护者一眼就能看懂你要取最早时间和固定ticker，比方式一的数组操作清晰得多。

另外两种方式的问题：

方式一：array_agg()默认不保证聚合顺序，如果不手动加ORDER BY（比如array_agg(ts ORDER BY ts)），可能取到的不是最早的ts，存在逻辑风险；而且聚合整个数组的操作冗余，性能差。
方式三：GROUP BY ts, ticker会把每条记录单独分组（因为ts是唯一的），再LIMIT 1只能拿到任意一条记录，完全不符合聚合需求，逻辑错误，直接排除。

内容的提问来源于Stack Exchange，提问作者Thomas