如何用SQL筛选符合条件的时序数据及邻近时间戳数据？

问题说明 #

你需要从含ts（时间戳）、val（数值）字段的时序数据表中筛选两类数据：

1. 本身满足val > 1的行
2. 与上述行时间相近的行，分两种规则：
   • a) 时间戳与目标行差值在±30秒范围内
   • b) 与目标行处于同一整分钟（00秒至59秒区间）

你之前尝试的SQL无法成功，核心问题是子查询返回多行导致语法错误，且该写法在大表场景下性能极差。

最优实现方案 #

规则a：±30秒范围内的邻近行 #

采用JOIN关联+索引优化是大表场景的最优选择，避免子查询的性能瓶颈：

SELECT DISTINCT t.*
FROM your_table t
JOIN your_table tr 
  ON ABS(DATEDIFF(second, t.ts, tr.ts)) <= 30
WHERE tr.val > 1;

• 优化要点：给ts字段创建普通索引，给val字段创建普通索引（或联合索引(val, ts)），让数据库快速定位val>1的目标行，再通过索引匹配时间范围内的邻近行。
• 用DISTINCT避免重复行（某行可能同时和多个目标行的时间差在30秒内）。

如果数据库支持时间戳直接加减（如MySQL、PostgreSQL），可改写条件以更好地利用索引：

-- MySQL 示例
SELECT DISTINCT t.*
FROM your_table t
JOIN your_table tr 
  ON t.ts BETWEEN tr.ts - INTERVAL 30 SECOND AND tr.ts + INTERVAL 30 SECOND
WHERE tr.val > 1;

规则b：同一整分钟的邻近行 #

核心是将时间戳截断到整分钟后关联匹配：

SELECT DISTINCT t.*
FROM your_table t
JOIN your_table tr 
  ON DATE_TRUNC('minute', t.ts) = DATE_TRUNC('minute', tr.ts)
WHERE tr.val > 1;

• 优化要点：给DATE_TRUNC('minute', ts)创建函数索引（如PostgreSQL），或提前计算并存储截断到整分钟的minute_ts字段，给该字段创建普通索引，大幅提升关联效率。
• 不同数据库截断函数差异：MySQL用DATE_FORMAT(ts, '%Y-%m-%d %H:%i:00')，SQL Server用DATEADD(minute, DATEDIFF(minute, 0, ts), 0)。

原SQL失败原因 #

你之前的SQL中，子查询(SELECT ts FROM table WHERE val > 1)返回多个时间戳值，而DATEDIFF仅能处理单个值，导致语法错误。即使改为IN子查询，该写法会逐行扫描全表，大表下性能完全不可用。

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