如何在SQLite中用SELECT语句查询最近10分钟每分钟1条的数据

前提说明 #

假设你的业务表名为your_table，表中存储时间的字段为create_time，示例中val为你需要查询的业务字段，可根据实际需求调整。 #

场景1：时间字段为ISO格式字符串/TIMESTAMP类型 #

如果你的时间字段存储格式为标准时间格式（如2021-12-06 11:37:23），可直接使用如下语句：

SELECT 
  strftime('%m/%d/%Y %H:%M:00', create_time) as minute_time,
  -- 可根据业务需求替换聚合规则，如AVG(val)/SUM(val)/ANY_VALUE(val)
  MAX(val) as val
FROM your_table
WHERE create_time >= datetime('now', '-10 minutes')
GROUP BY strftime('%Y-%m-%d %H:%M', create_time)
ORDER BY minute_time ASC;

输出的minute_time字段完全符合你要求的MM/DD/YYYY HH:MM:00格式。 #

场景2：需要补全无数据的空分钟 #

上述语句仅返回有数据的分钟记录，如果需要把最近10分钟所有分钟都展示、无数据的分钟返回空值，可使用递归CTE生成时间序列关联查询：

WITH RECURSIVE minute_series AS (
  SELECT datetime('now', '-10 minutes', 'start of minute') as minute_time
  UNION ALL
  SELECT datetime(minute_time, '+1 minute')
  FROM minute_series
  WHERE minute_time < datetime('now', 'start of minute')
)
SELECT 
  strftime('%m/%d/%Y %H:%M:00', ms.minute_time) as minute_time,
  t.val
FROM minute_series ms
LEFT JOIN your_table t 
  ON strftime('%Y-%m-%d %H:%M', t.create_time) = strftime('%Y-%m-%d %H:%M', ms.minute_time)
ORDER BY ms.minute_time ASC;

场景3：时间字段为Unix时间戳（整数类型，单位秒） #

如果时间存储为秒级时间戳，调整时间转换逻辑即可：

SELECT 
  strftime('%m/%d/%Y %H:%M:00', create_time, 'unixepoch') as minute_time,
  MAX(val) as val
FROM your_table
WHERE create_time >= unixepoch('now', '-10 minutes')
GROUP BY strftime('%Y-%m-%d %H:%M', create_time, 'unixepoch')
ORDER BY minute_time ASC;

注意事项 #

• 请将语句中的your_table、create_time、val替换为你实际的表名、时间字段名、业务字段名
• SQLite 3.39.0及以上版本支持ANY_VALUE(字段名)取分组内任意一条记录，低版本可使用MIN(字段名)/MAX(字段名)替代

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