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

Question

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

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

Answer 1

递归CTE

由于每一行都取决于之前的一行，因此很难使用基于集合的方法来解决。求助于递归CTE（这是标准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)
   )
SELECT * FROM cte ORDER BY ts;

请注意我的初稿中的更新：
递归CTE中不允许使用聚合函数。我用ORDER BY/代替，当/上LIMIT 1的 索引 支持时应该很快ts。

UNION查询每条腿周围的括号对于允许是必须的LIMIT，否则将仅在UNION查询结束时允许一次。

PL / pgSQL函数

遍历已排序表的过程解决方案（带有plpgsql函数的示例）可能会快很多，因为它可以通过单个表扫描来解决：

CREATE OR REPLACE FUNCTION f_rowgrid(i interval)
  RETURNS SETOF timestamp 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$ LANGUAGE plpgsql;

称呼：

SELECT * FROM  f_rowgrid('5 min')

SQL Fiddle 演示了两者。