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