mysql 查询最近5分钟,MySQL / Postgres查询5分钟间隔数据

I need help with the query, let's say that this is the data in table.

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

I want to get every first row (timestamp) that is at least 5 minutes later than the last. In this case the query should return:

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

解决方案

Recursive CTE

Since each row depends on the one before, it is hard to solve with a set-based approach. Resorting to a recursive CTE (which is standard 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;

Note the update from my first draft:

Aggregate functions are not allowed in a recursive CTE. I substituted with ORDER BY / LIMIT 1, which should be fast when supported by an index on ts.

The parentheses around each leg of the UNION query are necessary to allow LIMIT, which would otherwise only be permitted once at the end of a UNION query.

PL/pgSQL function

A procedural solution (example with a plpgsql function) iterating through the sorted table would probably be a lot faster, since it can make do with a single table scan:

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;

Call:

SELECT * FROM f_rowgrid('5 min')

SQL Fiddle demonstrating both.

Here is a somewhat more complex example for this type of plpgsql function:

Could easily be made generic with dynamic SQL and EXECUTE to work for arbitrary tables.