时间序列「每N分钟取一行」问题：求可利用索引的纯SQL方案

现有时间序列数据表，字段如下：

• sensor_number（integer类型主键）
• signal_strength（integer类型）
• signal_time（timestamp类型）

每个传感器每分钟生成20-30条数据，需求为：每个传感器按指定间隔（1/2/3分钟等）返回一行数据，且需利用索引保证50亿行数据下的查询性能。

核心思路是让分组/分区条件能被(sensor_number, signal_time)复合索引覆盖，避免对signal_time做复杂函数计算导致索引失效。

方法1：整数偏移量分组（通用间隔场景） #

将时间转换为从起始点开始的分钟数整数，通过整数取模运算实现任意间隔的分组，完全适配索引扫描。

假设指定间隔为N分钟，示例SQL如下：

SELECT
  sensor_number,
  -- 按需选择聚合逻辑：取间隔内第一条/最后一条/平均值
  FIRST_VALUE(signal_strength) OVER (PARTITION BY sensor_number, minute_offset / N ORDER BY signal_time) AS signal_strength,
  -- 计算间隔起始时间
  TIMESTAMP 'epoch' + (minute_offset / N * N * 60) * INTERVAL '1 second' AS interval_start
FROM (
  SELECT
    sensor_number,
    signal_strength,
    signal_time,
    -- 将时间转为从 epoch 开始的总分钟数
    EXTRACT(EPOCH FROM signal_time)::INT / 60 AS minute_offset
  FROM sensor_data
) AS t
GROUP BY sensor_number, minute_offset / N, interval_start, signal_strength;

优势 #

• 整数运算可被数据库优化器识别，直接利用(sensor_number, signal_time)索引做范围扫描
• 支持任意整数分钟间隔，无需修改表结构

方法2：预计算分区键+覆盖索引（固定间隔场景） #

如果常用间隔固定（如仅2/3分钟），可提前添加计算列并创建覆盖索引，进一步提升查询速度。

1. 添加计算列（以2分钟间隔为例）：

ALTER TABLE sensor_data ADD COLUMN interval_2min_key INT GENERATED ALWAYS AS (
  (EXTRACT(EPOCH FROM signal_time)::INT / 60) / 2
) STORED;

2. 创建覆盖索引：

CREATE INDEX idx_sensor_interval_2min ON sensor_data (sensor_number, interval_2min_key) INCLUDE (signal_time, signal_strength);

3. 查询语句：

SELECT
  sensor_number,
  FIRST_VALUE(signal_strength) OVER (PARTITION BY sensor_number, interval_2min_key ORDER BY signal_time) AS signal_strength,
  MIN(signal_time) AS interval_start
FROM sensor_data
GROUP BY sensor_number, interval_2min_key;

优势 #

• 覆盖索引可让查询完全在索引中完成，无需回表
• 分组逻辑直接基于预计算列，性能最优

方法3：索引友好的时间截断写法 #

针对分钟级间隔，可通过时间截断+整数运算组合，让优化器识别并利用索引：

以3分钟间隔为例：

SELECT
  sensor_number,
  FIRST_VALUE(signal_strength) OVER (
    PARTITION BY 
      sensor_number,
      DATE_TRUNC('hour', signal_time) + FLOOR(EXTRACT(MINUTE FROM signal_time)/3)*3 * INTERVAL '1 minute'
    ORDER BY signal_time
  ) AS signal_strength,
  DATE_TRUNC('hour', signal_time) + FLOOR(EXTRACT(MINUTE FROM signal_time)/3)*3 * INTERVAL '1 minute' AS interval_start
FROM sensor_data
GROUP BY sensor_number, interval_start, signal_strength;

优势 #

• 无需额外计算列，直接基于原生时间字段操作
• 时间截断+整数运算的组合可被优化器映射为索引范围扫描

• 必须保证(sensor_number, signal_time)复合索引存在，这是所有方案的基础
• 优先选择整数运算替代复杂时间函数，降低索引扫描的计算开销
• 尽量使用覆盖索引，避免查询时回表读取数据

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