Re: How to find events within a timespan to each other?

(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