如何在PostgreSQL中对百万级时序数据按时间分组？

直接通过PostgreSQL的聚合函数在数据库层面完成分组，完全替代Python循环，性能会有数量级的提升。以下是具体实现方案：

核心SQL实现 #

根据你需要的数组结构，有两种常用方式：

方式1：聚合为结构化数组（记录类型） #

将每个参数的parameter和raw值组合成记录，再聚合成数组：

SELECT
  channel_id,
  datetime,
  array_agg(row(parameter, raw)::(text, numeric)) AS params_array
-- 注意：这里的(text, numeric)要匹配你的parameter和raw字段类型，比如parameter是varchar、raw是float就对应(varchar, float)
FROM your_table_name
-- 可选：添加时间区间过滤，减少聚合数据量
WHERE datetime BETWEEN '起始时间' AND '结束时间'
GROUP BY channel_id, datetime;

方式2：聚合为JSON数组（可读性更强） #

如果需要更直观的键值对结构，用JSON格式聚合：

SELECT
  channel_id,
  datetime,
  json_agg(json_build_object('parameter', parameter, 'raw', raw)) AS params_json
FROM your_table_name
WHERE datetime BETWEEN '起始时间' AND '结束时间'
GROUP BY channel_id, datetime;

如果处理超大数组，推荐用jsonb_agg替代json_agg，性能更优。

性能优化建议 #

1. 创建复合索引：针对分组字段创建索引，大幅提升GROUP BY的效率：

CREATE INDEX idx_channel_datetime ON your_table_name(channel_id, datetime);

2. 先过滤再聚合：始终在WHERE子句中限定时间区间，避免全表扫描聚合。
3. 分批处理（可选）：如果时间区间极大，可按天/小时拆分查询，避免单次返回结果集过大。

Python中使用示例 #

用psycopg2直接执行上述SQL，获取聚合后的结果，无需再循环处理原始数据：

import psycopg2

# 建立数据库连接
conn = psycopg2.connect(
    dbname="你的数据库名",
    user="你的用户名",
    password="你的密码",
    host="你的数据库地址"
)
cur = conn.cursor()

# 执行聚合查询
sql = """
SELECT channel_id, datetime, json_agg(json_build_object('parameter', parameter, 'raw', raw)) AS params_data
FROM your_table_name
WHERE datetime BETWEEN %s AND %s
GROUP BY channel_id, datetime;
"""
cur.execute(sql, ('2023-01-01 00:00:00', '2023-12-31 23:59:59'))
results = cur.fetchall()

# 遍历处理结果
for channel_id, dt, params in results:
    print(f"通道ID: {channel_id}, 时间点: {dt}")
    for param in params:
        print(f"参数: {param['parameter']}, 原始值: {param['raw']}")

cur.close()
conn.close()

内容的提问来源于stack exchange，提问作者Fırat Badur