From: Tim Landscheidt <tim@tim-landscheidt.de>
To: pgsql-sql@postgresql.org
Subject: Re: How to find events within a timespan to each other?
Date: Wed, 07 Jul 2010 22:01:31 +0000
Message-ID: <m3ocejgddw.fsf@passepartout.tim-landscheidt.de> (raw)
References: <4C34EF8F.5010202@gmx.net>
(anonymous) wrote:
> is there a way to find events in a log that happen within a
> certain timespan to each other?
> Log is like this
> event_id integer not null default 0 primary key
> event_type_id integer not null default
> user_id integer not null default 0
> event_ts timestamp(0)
> I need every event of a type that happened more often than
> one time within 5 minutes of another one of the same user.
> 17 3 1 ... 12:00
> 18 2 ....
> 19 3 1 ... 13:03
> 20 3 2 ... 13:03
> 21 3 1 ... 13:04
> 22 2 .....
> 23 3 1 ... 13:05
> 24 2 1 ... 13:06
> E.g. the checked event_typ_id may be 3 then the result
> should be line 19, 21, 23
You can use window functions and check whether the preceding
or following timestamp is within range:
| tim=# SELECT event_id, event_type_id, user_id, event_ts
| tim-# FROM (SELECT event_id,
| tim(# event_type_id,
| tim(# user_id,
| tim(# event_ts,
| tim(# LAG(event_ts) OVER (PARTITION BY user_id, event_type_id ORDER BY event_ts ASC) AS PrecedingTimestamp,
| tim(# LAG(event_ts) OVER (PARTITION BY user_id, event_type_id ORDER BY event_ts DESC) AS FollowingTimestamp
| tim(# FROM TestEvents) AS SubQuery
| tim-# WHERE (PrecedingTimestamp IS NOT NULL AND event_ts - PrecedingTimestamp <= '5 minutes') OR
| tim-# (FollowingTimestamp IS NOT NULL AND FollowingTimestamp - event_ts <= '5 minutes');
| event_id | event_type_id | user_id | event_ts
| ----------+---------------+---------+---------------------
| 23 | 3 | 1 | 2010-01-01 13:05:00
| 21 | 3 | 1 | 2010-01-01 13:04:00
| 19 | 3 | 1 | 2010-01-01 13:03:00
| (3 Zeilen)
| tim=#
Tim
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: tim@tim-landscheidt.de
Subject: Re: How to find events within a timespan to each other?
In-Reply-To: <m3ocejgddw.fsf@passepartout.tim-landscheidt.de>
* 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