pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedselecting records X minutes apart
18+ messages / 7 participants
[nested] [flat]
* selecting records X minutes apart
@ 2011-06-03 19:52 lists-pgsql@useunix.net
0 siblings, 6 replies; 18+ messages in thread
From: lists-pgsql@useunix.net @ 2011-06-03 19:52 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: selecting records X minutes apart
@ 2011-06-04 01:01 Kevin Crain <kevin.crain1@gmail.com>
parent: lists-pgsql@useunix.net
5 siblings, 1 reply; 18+ messages in thread
From: Kevin Crain @ 2011-06-04 01:01 UTC (permalink / raw)
To: lists-pgsql@useunix.net; +Cc: pgsql-sql
Will you be using a full timestamp with that or are you only concerned
about hours and minutes? If you want a full timestamp do you care
about the seconds? For example, do you want to be able to do this for
'2011-06-01 23:59:04' and '2011-06-02 00:04:04'?
On Fri, Jun 3, 2011 at 12:52 PM, <lists-pgsql@useunix.net> wrote:
> 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
>
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: selecting records X minutes apart
@ 2011-06-04 11:45 Jasen Betts <jasen@xnet.co.nz>
parent: lists-pgsql@useunix.net
5 siblings, 2 replies; 18+ messages in thread
From: Jasen Betts @ 2011-06-04 11:45 UTC (permalink / raw)
To: pgsql-sql
On 2011-06-03, lists-pgsql@useunix.net <lists-pgsql@useunix.net> wrote:
>
> ID TS (HH:MM)
> -------------------
> 0 20:00
> 0 20:05
> 0 20:10
> 1 20:03
> 1 20:09
>
>
> Does my question make sense?
no, why is (1,20:04) excluded, but (0,20:05) included?
both records are 5 minutes from the newest.
--
⚂⚃ 100% natural
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: selecting records X minutes apart
@ 2011-06-04 15:49 Kevin Crain <kevin.crain1@gmail.com>
parent: Jasen Betts <jasen@xnet.co.nz>
1 sibling, 0 replies; 18+ messages in thread
From: Kevin Crain @ 2011-06-04 15:49 UTC (permalink / raw)
To: Jasen Betts <jasen@xnet.co.nz>; +Cc: pgsql-sql
It looks like maybe he is trying to fetch records that either have no
previous entries or have another record with a timestamp 5 minutes
before them at the time they are inserted...
On Sat, Jun 4, 2011 at 4:45 AM, Jasen Betts <jasen@xnet.co.nz> wrote:
> On 2011-06-03, lists-pgsql@useunix.net <lists-pgsql@useunix.net> wrote:
>>
>> ID TS (HH:MM)
>> -------------------
>> 0 20:00
>> 0 20:05
>> 0 20:10
>> 1 20:03
>> 1 20:09
>>
>>
>> Does my question make sense?
>
> no, why is (1,20:04) excluded, but (0,20:05) included?
> both records are 5 minutes from the newest.
>
> --
> ⚂⚃ 100% natural
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: selecting records X minutes apart
@ 2011-06-04 18:58 lists-pgsql@useunix.net
parent: Kevin Crain <kevin.crain1@gmail.com>
0 siblings, 0 replies; 18+ messages in thread
From: lists-pgsql@useunix.net @ 2011-06-04 18:58 UTC (permalink / raw)
To: pgsql-sql
The TS column type is actually a timestamp with out timezone and yes I want to
take seconds into account so both of your entries would be included in the
result.
On Fri, Jun 03, 2011 at 06:01:53PM -0700, Kevin Crain wrote:
> Will you be using a full timestamp with that or are you only concerned
> about hours and minutes? If you want a full timestamp do you care
> about the seconds? For example, do you want to be able to do this for
> '2011-06-01 23:59:04' and '2011-06-02 00:04:04'?
>
> On Fri, Jun 3, 2011 at 12:52 PM, <lists-pgsql@useunix.net> wrote:
> > 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
> >
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: selecting records X minutes apart
@ 2011-06-04 19:15 lists-pgsql@useunix.net
parent: Jasen Betts <jasen@xnet.co.nz>
1 sibling, 1 reply; 18+ messages in thread
From: lists-pgsql@useunix.net @ 2011-06-04 19:15 UTC (permalink / raw)
To: pgsql-sql
On Sat, Jun 04, 2011 at 11:45:08AM +0000, Jasen Betts wrote:
> On 2011-06-03, lists-pgsql@useunix.net <lists-pgsql@useunix.net> wrote:
> >
> > ID TS (HH:MM)
> > -------------------
> > 0 20:00
> > 0 20:05
> > 0 20:10
> > 1 20:03
> > 1 20:09
> >
> >
> > Does my question make sense?
>
> no, why is (1,20:04) excluded, but (0,20:05) included?
> both records are 5 minutes from the newest.
Jasen,
(1,20:04) is excluded because it's timestamp is less than 5 minutes from the
previous record with the same ID (1,20:03), (0,20:05) is included for the
opposite reason.
Let me restate my requirement again with a little more detail. I want to
select records grouped by ID, ordered by timestamp, in ascending order so I'm
starting with the oldest, that are at least X minutes apart.
I hope that helps.
Thanks again,
Wayne
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: selecting records X minutes apart
@ 2011-06-04 20:09 Richard Broersma <richard.broersma@gmail.com>
parent: lists-pgsql@useunix.net
0 siblings, 1 reply; 18+ messages in thread
From: Richard Broersma @ 2011-06-04 20:09 UTC (permalink / raw)
To: lists-pgsql@useunix.net; +Cc: pgsql-sql
On Sat, Jun 4, 2011 at 12:15 PM, <lists-pgsql@useunix.net> wrote:
> I want to
> select records grouped by ID, ordered by timestamp, in ascending order so I'm
> starting with the oldest, that are at least X minutes apart.
Here my guess:
SELECT id, ts
FROM Yourtable AS A
AND NOT EXISTS ( SELECT *
FROM Yourtable AS B
WHERE B.id = A.id
AND B.ts > A.ts - INTERVAL '5 MINUTES'
AND B.tx < A.ts )
ORDER BY id, ts;
--
Regards,
Richard Broersma Jr.
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: [SQL] selecting records X minutes apart
@ 2011-06-04 20:42 Charlie <scorpdaddy@hotmail.com>
0 siblings, 0 replies; 18+ messages in thread
From: Charlie @ 2011-06-04 20:42 UTC (permalink / raw)
To: lists-pgsql@useunix.net; pgsql-sql
SELECT a.*
FROM foo a
INNER JOIN foo b
ON b.id = a.id
AND b.ts >= a.ts + '5 minutes'
GROUP BY a.id, a.ts
ORDER BY a.id, a.ts
Not clear yet on the needs, but this may give a start.
----- Reply message -----
From: lists-pgsql@useunix.net
Date: Sat, Jun 4, 2011 4:15 pm
Subject: [SQL] selecting records X minutes apart
To: <pgsql-sql@postgresql.org>
On Sat, Jun 04, 2011 at 11:45:08AM +0000, Jasen Betts wrote:
> On 2011-06-03, lists-pgsql@useunix.net <lists-pgsql@useunix.net> wrote:
> >
> > ID TS (HH:MM)
> > -------------------
> > 0 20:00
> > 0 20:05
> > 0 20:10
> > 1 20:03
> > 1 20:09
> >
> >
> > Does my question make sense?
>
> no, why is (1,20:04) excluded, but (0,20:05) included?
> both records are 5 minutes from the newest.
Jasen,
(1,20:04) is excluded because it's timestamp is less than 5 minutes from the
previous record with the same ID (1,20:03), (0,20:05) is included for the
opposite reason.
Let me restate my requirement again with a little more detail. I want to
select records grouped by ID, ordered by timestamp, in ascending order so I'm
starting with the oldest, that are at least X minutes apart.
I hope that helps.
Thanks again,
Wayne
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: selecting records X minutes apart
@ 2011-06-04 20:49 lists-pgsql@useunix.net
parent: Richard Broersma <richard.broersma@gmail.com>
0 siblings, 0 replies; 18+ messages in thread
From: lists-pgsql@useunix.net @ 2011-06-04 20:49 UTC (permalink / raw)
To: Richard Broersma <richard.broersma@gmail.com>; +Cc: pgsql-sql
Did you mean WHERE in place of your first AND? If so I already had something
like this but it only returns one set, the oldest group of entries for each
ID.
On Sat, Jun 04, 2011 at 01:09:39PM -0700, Richard Broersma wrote:
> On Sat, Jun 4, 2011 at 12:15 PM, <lists-pgsql@useunix.net> wrote:
> > I want to
> > select records grouped by ID, ordered by timestamp, in ascending order so I'm
> > starting with the oldest, that are at least X minutes apart.
>
>
> Here my guess:
>
> SELECT id, ts
> FROM Yourtable AS A
> AND NOT EXISTS ( SELECT *
> FROM Yourtable AS B
> WHERE B.id = A.id
> AND B.ts > A.ts - INTERVAL '5 MINUTES'
> AND B.tx < A.ts )
>
> ORDER BY id, ts;
>
> --
> Regards,
> Richard Broersma Jr.
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: selecting records X minutes apart
@ 2011-06-05 00:51 Kevin Crain <kevin.crain1@gmail.com>
parent: lists-pgsql@useunix.net
5 siblings, 1 reply; 18+ messages in thread
From: Kevin Crain @ 2011-06-05 00:51 UTC (permalink / raw)
To: lists-pgsql@useunix.net; +Cc: pgsql-sql
Why is (0,20:10) listed in your expected results when there is a (0,20:08)?
On Fri, Jun 3, 2011 at 12:52 PM, <lists-pgsql@useunix.net> wrote:
> 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
>
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: selecting records X minutes apart
@ 2011-06-05 00:56 Kevin Crain <kevin.crain1@gmail.com>
parent: lists-pgsql@useunix.net
5 siblings, 0 replies; 18+ messages in thread
From: Kevin Crain @ 2011-06-05 00:56 UTC (permalink / raw)
To: lists-pgsql@useunix.net; +Cc: pgsql-sql
My approach would be to add a column for LAST_TS and place a trigger
on insert that populates this new column. Then you have something you
can put in your WHERE clause to test on.
On Fri, Jun 3, 2011 at 12:52 PM, <lists-pgsql@useunix.net> wrote:
> 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
>
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: selecting records X minutes apart
@ 2011-06-05 01:36 lists-pgsql@useunix.net
parent: Kevin Crain <kevin.crain1@gmail.com>
0 siblings, 1 reply; 18+ messages in thread
From: lists-pgsql@useunix.net @ 2011-06-05 01:36 UTC (permalink / raw)
To: pgsql-sql
Let's a take a look at just the input set for ID 0.
0 20:00
0 20:05
0 20:08
0 20:10
I want records, starting from the oldest record (20:00), that are at least 5
minutes apart. So 20:00, 20:05, 20:10 but 20:08 - 20:05 is only 3 minutes so
it is to be ignored.
I was hoping to do this with a single SQL query that renders good runtime
performance but it may not possible. But I'm by no means proficient in SQL.
On Sat, Jun 04, 2011 at 05:51:18PM -0700, Kevin Crain wrote:
> Why is (0,20:10) listed in your expected results when there is a (0,20:08)?
>
>
> On Fri, Jun 3, 2011 at 12:52 PM, <lists-pgsql@useunix.net> wrote:
> > 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
> >
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: selecting records X minutes apart
@ 2011-06-05 02:10 Richard Broersma <richard.broersma@gmail.com>
parent: lists-pgsql@useunix.net
0 siblings, 0 replies; 18+ messages in thread
From: Richard Broersma @ 2011-06-05 02:10 UTC (permalink / raw)
To: lists-pgsql@useunix.net; +Cc: pgsql-sql
On Sat, Jun 4, 2011 at 6:36 PM, <lists-pgsql@useunix.net> wrote:
> 0 20:00
> 0 20:05
> 0 20:08
> 0 20:10
>
> I want records, starting from the oldest record (20:00), that are at least 5
> minutes apart. So 20:00, 20:05, 20:10 but 20:08 - 20:05 is only 3 minutes so
> it is to be ignored.
Here is my next guess: --although I guess my use of WITH Recursive is correct
WITH RECURSIVE Accum_ts( id, ts ) AS
( SELECT id, MIN( ts )
FROM Yourtable
GROUP BY id )
UNION ALL ( SELECT B.id, MIN( B.ts )
FROM Yourtable AS B
WHERE B.id = Accum_ts.id
AND B.ts >= Accum_ts.ts - INTERVAL '5 MINUTES'
GROUP BY B.id
HAVING MIN( B.ts ) <= MAX( B.ts ))
SELECT id, ts
FROM Accum_ts
ORDER BY id, ts;
--
Regards,
Richard Broersma Jr.
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: [SQL] selecting records X minutes apart
@ 2011-06-05 04:02 Charlie <scorpdaddy@hotmail.com>
0 siblings, 0 replies; 18+ messages in thread
From: Charlie @ 2011-06-05 04:02 UTC (permalink / raw)
To: lists-pgsql@useunix.net; pgsql-sql
WITH foo AS
(
SELECT column1::integer id, column2::timestamp ts
FROM (VALUES
(0, '1-Jan-2010 20:00'),
(1, '1-Jan-2010 20:03'),
(1, '1-Jan-2010 20:04'),
(0, '1-Jan-2010 20:05'),
(1, '1-Jan-2010 20:05'),
(0, '1-Jan-2010 20:08'),
(1, '1-Jan-2010 20:09'),
(0, '1-Jan-2010 20:10')) vals
)
SELECT *
FROM
(
SELECT
id,
ts,
(
SELECT b.ts FROM foo b
WHERE b.id = a.id
AND b.ts > a.ts
ORDER BY b.ts
LIMIT 1
) - ts gap
FROM foo a
) c
ORDER BY id, ts
;
Still can't make heads or tails of the needs yet. But running the snippet above may give some more ideas.
Looking at id 1 tuples: 20:03 is in because it has a record >= X away. But so does 20:04. 20:04 is out because it has a record that is < X away. But so does 20:03, which is in. Etc.
----- Reply message -----
From: lists-pgsql@useunix.net
Date: Fri, Jun 3, 2011 4:52 pm
Subject: [SQL] selecting records X minutes apart
To: <pgsql-sql@postgresql.org>
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
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: selecting records X minutes apart
@ 2011-06-05 11:52 Masaru Sugawara <pgsql@amail.plala.or.jp>
parent: lists-pgsql@useunix.net
5 siblings, 1 reply; 18+ messages in thread
From: Masaru Sugawara @ 2011-06-05 11:52 UTC (permalink / raw)
To: pgsql-sql
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'),
(1, '20:03'),
(1, '20:04'),
(1, '20:05'),
(1, '20:09'),
(1, '20:16');
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;
> 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
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: selecting records X minutes apart
@ 2011-06-05 12:06 Masaru Sugawara <pgsql@amail.plala.or.jp>
parent: lists-pgsql@useunix.net
5 siblings, 0 replies; 18+ messages in thread
From: Masaru Sugawara @ 2011-06-05 12:06 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: selecting records X minutes apart
@ 2011-06-05 19:42 lists-pgsql@useunix.net
parent: Masaru Sugawara <pgsql@amail.plala.or.jp>
0 siblings, 0 replies; 18+ messages in thread
From: lists-pgsql@useunix.net @ 2011-06-05 19:42 UTC (permalink / raw)
To: pgsql-sql
Thank you all who replied!! It looks like Sugawara's recursive solution does
the trick. Unfortunately performance is quite poor for the sample dataset I'm
working with which is a table of about 50000 records. Indeed, there are
indexes applied to the table. I believe the recursive select is being
executed a great number of times causing the first part of the query to take a
long time.
The fastest solution I've come up with is a plpgsql procedure the loops over a
select where the result is ordered by (id,tstamp) and examines the tstamp
values and only returns rows that meet the interval criteria. This technique
takes roughly 2 seconds to filter out records over my 50000 record sample
set.... which is acceptable but not nearly as elegant as a single SQL
statement.
Again, thank you for all the replies.
Wayne
On Sun, Jun 05, 2011 at 08:52:30PM +0900, Masaru Sugawara wrote:
> 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'),
> (1, '20:03'),
> (1, '20:04'),
> (1, '20:05'),
> (1, '20:09'),
> (1, '20:16');
>
> 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;
>
>
>
>
>
>
> > 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
>
>
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: selecting records X minutes apart
@ 2011-06-12 21:50 Gavin Flower <GavinFlower@archidevsys.co.nz>
0 siblings, 0 replies; 18+ messages in thread
From: Gavin Flower @ 2011-06-12 21:50 UTC (permalink / raw)
To: pgsql-sql
How about this (that does not require special functions nor triggers:
DROP TABLE IF EXISTS val;
CREATE TABLE val
(
id int,
ts timestamp
);
INSERT INTO val
VALUES
(0, '1-Jan-2010 20:00'),
(1, '1-Jan-2010 20:03'),
(1, '1-Jan-2010 20:04'),
(0, '1-Jan-2010 20:05'),
(1, '1-Jan-2010 20:05'),
(0, '1-Jan-2010 20:08'),
(1, '1-Jan-2010 20:09'),
(0, '1-Jan-2010 20:10');
WITH val_first AS
(
SELECT
id,
min(ts) AS ts
FROM
val
GROUP BY
id
)
SELECT
v.id,
v.ts::time
FROM
val v,
val_first vf
WHERE
v.id = vf.id AND
EXTRACT(EPOCH FROM v.ts - vf.ts)::int % 300 = 0
ORDER BY
id,
ts;
^ permalink raw reply [nested|flat] 18+ messages in thread
end of thread, other threads:[~2011-06-12 21:50 UTC | newest]
Thread overview: 18+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-06-03 19:52 selecting records X minutes apart lists-pgsql@useunix.net
2011-06-04 01:01 ` Kevin Crain <kevin.crain1@gmail.com>
2011-06-04 18:58 ` lists-pgsql@useunix.net
2011-06-04 11:45 ` Jasen Betts <jasen@xnet.co.nz>
2011-06-04 15:49 ` Kevin Crain <kevin.crain1@gmail.com>
2011-06-04 19:15 ` lists-pgsql@useunix.net
2011-06-04 20:09 ` Richard Broersma <richard.broersma@gmail.com>
2011-06-04 20:49 ` lists-pgsql@useunix.net
2011-06-05 00:51 ` Kevin Crain <kevin.crain1@gmail.com>
2011-06-05 01:36 ` lists-pgsql@useunix.net
2011-06-05 02:10 ` Richard Broersma <richard.broersma@gmail.com>
2011-06-05 00:56 ` Kevin Crain <kevin.crain1@gmail.com>
2011-06-05 11:52 ` Masaru Sugawara <pgsql@amail.plala.or.jp>
2011-06-05 19:42 ` lists-pgsql@useunix.net
2011-06-05 12:06 ` Masaru Sugawara <pgsql@amail.plala.or.jp>
2011-06-04 20:42 Re: [SQL] selecting records X minutes apart Charlie <scorpdaddy@hotmail.com>
2011-06-05 04:02 Re: [SQL] selecting records X minutes apart Charlie <scorpdaddy@hotmail.com>
2011-06-12 21:50 Re: selecting records X minutes apart Gavin Flower <GavinFlower@archidevsys.co.nz>
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