SQLite中按10分钟间隔统计30天传感器数据平均值的方法

没问题，我帮你搞定这个时间窗口聚合的需求！针对你的sensor_values表，我们可以利用SQLite的日期函数来实现按10分钟分组计算平均值，同时只获取过去30天的数据。

核心思路 #

要实现10分钟窗口分组，关键是把每条记录的created_at时间戳截断到最近的10分钟边界（比如10:07的记录会被归到10:00-10:10的窗口，10:15的记录归到10:10-10:20的窗口），然后以这个边界时间作为分组依据，计算每个窗口内的value平均值。

完整SQL语句（适用于created_at为datetime字符串/ISO格式） #

SELECT
  -- 生成10分钟窗口的起始时间作为分组标识
  strftime('%Y-%m-%d %H:%M:00', created_at, '-' || (strftime('%M', created_at) % 10) || ' minutes') AS window_start,
  -- 计算每个窗口的平均值
  AVG(value) AS avg_value
FROM sensor_values
WHERE
  -- 过滤过去30天的数据
  created_at >= datetime('now', '-30 days')
GROUP BY
  window_start
ORDER BY
  window_start ASC;

代码解释 #

• 过滤条件：datetime('now', '-30 days')会自动计算当前时间往前推30天的时间，确保只查询目标时间段的数据。
• 分组键生成：
  • strftime('%M', created_at) % 10 计算当前分钟数除以10的余数（比如07分的余数是7）
  • 用'-' || 余数 || ' minutes'生成一个时间偏移量，把当前时间往回推到最近的10分钟起始点
  • 最后用strftime格式化出标准的窗口起始时间（比如2024-05-20 14:00:00）
• 聚合与排序：按窗口起始时间分组后计算平均值，最后按时间升序排列结果，方便查看趋势。

如果created_at是UNIX时间戳（整数） #

如果你的created_at存储的是UNIX时间戳（比如1716220800这样的整数），只需要先把它转换成datetime格式再处理：

SELECT
  strftime('%Y-%m-%d %H:%M:00', datetime(created_at, 'unixepoch'), '-' || (strftime('%M', datetime(created_at, 'unixepoch')) % 10) || ' minutes') AS window_start,
  AVG(value) AS avg_value
FROM sensor_values
WHERE
  datetime(created_at, 'unixepoch') >= datetime('now', '-30 days')
GROUP BY
  window_start
ORDER BY
  window_start ASC;

性能优化建议 #

因为你的数据集很大，建议给created_at字段创建索引，大幅提升查询速度：

CREATE INDEX idx_sensor_created_at ON sensor_values(created_at);

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