(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_idinteger not null default 0 primary key
> event_type_idinteger not null default
> user_idinteger 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.
> 173 1 ... 12:00
> 182
> 193 1 ... 13:03
> 203 2 ... 13:03
> 213 1 ... 13:04
> 222.
> 233 1 ... 13:05
> 242 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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
