(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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
