PostgreSQL时间序列表：单查询获取各传感器参数最新值

针对你的百万级时间序列表需求，SELECT DISTINCT ON完全可行，结合行转列就能得到你要的结果，具体方案如下：

1. 先获取每个(location_id, param)的最新记录 #

使用DISTINCT ON (location_id, param)可以精准筛选出每个传感器每个参数的最新数据：

SELECT DISTINCT ON (location_id, param)
  location_id,
  param,
  value,
  timestamp
FROM telemetry
ORDER BY location_id, param, timestamp DESC;

原理：DISTINCT ON会保留每个分组（这里是location_id+param）中排序后的第一条记录，按timestamp DESC排序后，第一条就是该组合的最新数据。

2. 行转列得到宽表格式 #

要把参数转为列（如temperature、humidity），需要用到PostgreSQL的crosstab函数（来自tablefunc扩展）：

先启用扩展（首次执行即可） #

CREATE EXTENSION IF NOT EXISTS tablefunc;

最终查询语句 #

SELECT *
FROM crosstab(
  -- 子查询：获取每个(location_id, param)的最新值
  $$
    SELECT DISTINCT ON (location_id, param)
      location_id,
      param,
      value
    FROM telemetry
    ORDER BY location_id, param, timestamp DESC
  $$,
  -- 指定要转为列的参数列表
  $$SELECT unnest(ARRAY['temperature', 'humidity'])$$
) AS ct(location_id VARCHAR(255), temperature NUMERIC, humidity NUMERIC);

执行后就能得到你期望的输出格式：

location_id | temperature | humidity
------------|-------------|----------
001         | 20          | 14
002         | 30          | 13

3. 百万级数据的性能优化 #

为了避免全表扫描，建议创建复合覆盖索引：

CREATE INDEX idx_telemetry_loc_param_ts ON telemetry (location_id, param, timestamp DESC) INCLUDE (value);

这个索引直接覆盖了查询所需的所有字段，能让DISTINCT ON查询高效执行，大幅提升百万级数据的处理速度。

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