Timescale/PostgreSQL时间序列数据行转列（透视）方案咨询

假设你的时序数据表结构如下（可根据实际字段名调整）：

CREATE TABLE time_series_data (
  ts TIMESTAMPTZ NOT NULL,  -- 同一数据集的统一时间戳
  const_val TEXT NOT NULL,  -- 同一数据集的标识常量
  metric TEXT NOT NULL,     -- 指标名称（如温度、湿度）
  value NUMERIC NOT NULL    -- 指标对应数值
);

方法1：条件聚合（无需额外扩展，推荐） #

这是最通用的方案，语法直观，不需要安装任何扩展，适合已知所有指标名称的场景：

SELECT
  ts,
  const_val,
  -- 为每个指标生成对应列
  MAX(CASE WHEN metric = 'temp' THEN value END) AS temp,
  MAX(CASE WHEN metric = 'humidity' THEN value END) AS humidity,
  MAX(CASE WHEN metric = 'pressure' THEN value END) AS pressure
FROM time_series_data
-- 按数据集的唯一标识分组
GROUP BY ts, const_val
ORDER BY ts;

这里用MAX（或MIN）是因为同一ts+const_val组内每个指标仅对应一个值，聚合函数的作用是过滤CASE语句产生的NULL值，提取有效指标数值。

方法2：使用crosstab函数（适合多指标场景） #

PostgreSQL的tablefunc扩展提供了crosstab函数，专门用于行转列，适合指标数量较多的场景。首先需要安装扩展：

CREATE EXTENSION IF NOT EXISTS tablefunc;

然后执行行转列查询：

SELECT * FROM crosstab(
  -- 源数据查询：按数据集分组排序
  'SELECT ts, const_val, metric, value FROM time_series_data ORDER BY 1,2',
  -- 指定要转成列的指标列表
  'SELECT DISTINCT metric FROM time_series_data ORDER BY 1'
) AS ct (
  -- 定义输出表结构：前两列是数据集标识，后续是各指标列
  ts TIMESTAMPTZ,
  const_val TEXT,
  temp NUMERIC,
  humidity NUMERIC,
  pressure NUMERIC
);

性能优化建议 #

如果数据量较大，建议在ts和const_val上创建复合索引，加速分组操作：

CREATE INDEX idx_ts_const ON time_series_data (ts, const_val);

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