From: Masaru Sugawara <pgsql@amail.plala.or.jp>
To: pgsql-sql@postgresql.org
Subject: Re: selecting records X minutes apart
Date: Sun, 05 Jun 2011 21:06:25 +0900
Message-ID: <20110605210624.952F.2D56284C@amail.plala.or.jp> (raw)
In-Reply-To: <20110603195253.GE1324@slacker.ja10629.home>
References: <20110603195253.GE1324@slacker.ja10629.home>
On Fri, 3 Jun 2011 15:52:53 -0400
lists-pgsql@useunix.net wrote:
I also think you might want to use WITH RECURSIVE clause.
This SQL searches the case of an interval of 5 minutes or more,
and sets a relationship between a parent to its child.
CREATE TABLE tbl(id integer, ts time) ;
INSERT INTO tbl VALUES
(0, '20:00'),
(0, '20:05'),
(0, '20:08'),
(0, '20:10'),
(0, '20:11'), -- added as a sample.
(1, '20:03'),
(1, '20:04'),
(1, '20:05'),
(1, '20:09'),
(1, '20:16'); -- added as a sample.
SELECT * FROM tbl;
--
WITH RECURSIVE rec(id , ts_p, ts_c) AS (
SELECT a1.id, min(a1.ts), min(b1.ts)
FROM tbl AS a1, tbl AS b1
WHERE a1.id=b1.id AND a1.ts + interval'5 minute' <= b1.ts
GROUP BY a1.id
UNION ALL
SELECT t2.id, t2.ts_p, t2.ts_c
FROM rec AS t1 INNER JOIN
(SELECT a2.id, a2.ts as ts_p, min(b2.ts) AS ts_c
FROM tbl AS a2, tbl AS b2
WHERE a2.id = b2.id AND a2.ts + interval'5 minute' <= b2.ts
GROUP BY a2.id, a2.ts
UNION ALL
SELECT a3.id, a3.ts, null
FROM tbl AS a3
) AS t2 ON t1.id = t2.id AND t1.ts_c=t2.ts_p
)
SELECT DISTINCT id, ts_p AS ts FROM rec
ORDER BY 1,2;
Regards,
Masaru Sugawara
> I have a table that, at a minimum, has ID and timestamp columns. Records
> are inserted into with random IDs and timestamps. Duplicate IDs are allowed.
>
> I want to select records grouped by ID, ordered by timestamp that are X minutes
> apart. In this case X is 5.
>
> Note, the intervals are not X minute wall clock intervals, they are X minute
> intervals from the last accepted record, per-id.
>
> For instance here is some sample input data:
>
> ID TS (HH:MM)
> -------------------
> 0 20:00
> 1 20:03
> 1 20:04
> 0 20:05
> 1 20:05
> 0 20:08
> 1 20:09
> 0 20:10
>
> I'd want the select to return:
>
> ID TS (HH:MM)
> -------------------
> 0 20:00
> 0 20:05
> 0 20:10
> 1 20:03
> 1 20:09
>
>
> Does my question make sense?
>
> Thanks in advance,
> Wayne
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: pgsql@amail.plala.or.jp
Subject: Re: selecting records X minutes apart
In-Reply-To: <20110605210624.952F.2D56284C@amail.plala.or.jp>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox