pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
How to find events within a timespan to each other? 2+ messages / 2 participants [nested] [flat]
* How to find events within a timespan to each other? @ 2010-07-07 21:20 Andreas <maps.on@gmx.net> 0 siblings, 1 reply; 2+ messages in thread From: Andreas @ 2010-07-07 21:20 UTC (permalink / raw) To: pgsql-sql Hi, 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 regards Andreas :) ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: How to find events within a timespan to each other? @ 2010-07-07 22:01 Tim Landscheidt <tim@tim-landscheidt.de> parent: Andreas <maps.on@gmx.net> 0 siblings, 0 replies; 2+ messages in thread From: Tim Landscheidt @ 2010-07-07 22:01 UTC (permalink / raw) To: pgsql-sql (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 ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2010-07-07 22:01 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2010-07-07 21:20 How to find events within a timespan to each other? Andreas <maps.on@gmx.net> 2010-07-07 22:01 ` Tim Landscheidt <tim@tim-landscheidt.de>
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