MySQL/Postgres查询5分钟间隔数据

我需要查询方面的帮助，假设这是表中的数据。

timestamp           
------------------- 
2010-11-16 10:30:00
2010-11-16 10:37:00
2010-11-16 10:40:00 
2010-11-16 10:45:00
2010-11-16 10:48:00
2010-11-16 10:55:00
2010-11-16 10:56:00

我想要获取比最后一行晚至少 5 分钟的第一行（时间戳）。在这种情况下，查询应返回：

timestamp           
------------------- 
2010-11-16 10:30:00
2010-11-16 10:37:00
2010-11-16 10:45:00
2010-11-16 10:55:00

递归CTE

由于每一行都依赖于前一行，因此很难使用基于集合的方法来解决。诉诸于一个递归CTE https://www.postgresql.org/docs/current/queries-with.html（这是标准 SQL）：

WITH RECURSIVE cte AS (
   (
   SELECT ts FROM tbl
   ORDER BY ts
   LIMIT 1
   )

   UNION ALL
   (
   SELECT t.ts
   FROM   cte c
   JOIN   tbl t ON t.ts >= c.ts + interval '5 min'
   ORDER  BY t.ts
   LIMIT 1
   )
   )
TABLE cte ORDER BY ts;

递归 CTE 中不允许使用聚合函数。我替换为ORDER BY / LIMIT 1，当有支持时速度很快index on ts.

每条腿周围的括号UNION需要查询才能允许LIMIT，否则只能在一次结束时允许一次UNION query.

PL/pgSQL 函数

迭代排序表的过程解决方案（使用 plpgsql 函数的示例）可能会快得多，因为它可以使用单个表扫描：

CREATE OR REPLACE FUNCTION f_rowgrid(i interval)
  RETURNS SETOF timestamp
  LANGUAGE plpgsql AS
$func$
DECLARE
   _this  timestamp;
   _last  timestamp := '-infinity';     -- init so that 1 row passes
BEGIN
   FOR _this IN
       SELECT ts FROM tbl ORDER BY 1
   LOOP
       IF _this >= _last + i THEN
          RETURN NEXT _this;
          _last := _this;
       END IF;
   END LOOP;
END
$func$;

Call:

SELECT * FROM  f_rowgrid('5 min');

db<>fiddle - demonstrating both
_{Old sqlfiddle http://sqlfiddle.com/#!17/8a555f/1}

以下是此类 plpgsql 函数的更复杂示例：

• GROUP BY 和聚合连续数值 https://stackoverflow.com/questions/8014577/group-by-and-aggregate-sequential-numeric-values/8014694#8014694

可以轻松地通过动态 SQL 实现通用化EXECUTE适用于任意表。

• 这里有很多关于 SO 的代码示例。 https://stackoverflow.com/search?q=%5Bplpgsql%5D%20execute%20regclass%20%5Bdynamic-sql%5D