MySQL/Postgres query 5 minutes interval data

common-table-expression, plpgsql, postgresql, recursive-query, sql

Solution

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
   )
   )
TABLE cte ORDER BY ts;

Aggregate functions are not allowed in a recursive CTE. I substituted with `ORDER BY` / `LIMIT 1`, which is 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
  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 here - demonstrating both Old sqlfiddle

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

- GROUP BY and aggregate sequential numeric values

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

- There are quite a few code examples here on SO.

Problem

Original source

Related problems

• GROUP BY and aggregate sequential numeric values