Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id EC86A632A69 for ; Wed, 7 Jul 2010 19:01:56 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 24524-06 for ; Wed, 7 Jul 2010 22:01:49 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from lo.gmane.org (lo.gmane.org [80.91.229.12]) by mail.postgresql.org (Postfix) with ESMTP id 3351363258B for ; Wed, 7 Jul 2010 19:01:49 -0300 (ADT) Received: from list by lo.gmane.org with local (Exim 4.69) (envelope-from ) id 1OWcga-00044L-Os for pgsql-sql@postgresql.org; Thu, 08 Jul 2010 00:01:44 +0200 Received: from f054052087.adsl.alicedsl.de ([78.54.52.87]) by main.gmane.org with esmtp (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Thu, 08 Jul 2010 00:01:44 +0200 Received: from tim by f054052087.adsl.alicedsl.de with local (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Thu, 08 Jul 2010 00:01:44 +0200 X-Injected-Via-Gmane: http://gmane.org/ Mail-Followup-To: pgsql-sql@postgresql.org To: pgsql-sql@postgresql.org From: Tim Landscheidt Subject: Re: How to find events within a timespan to each other? Date: Wed, 07 Jul 2010 22:01:31 +0000 Organization: Lines: 49 Message-ID: References: <4C34EF8F.5010202@gmx.net> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Complaints-To: usenet@dough.gmane.org X-Gmane-NNTP-Posting-Host: f054052087.adsl.alicedsl.de Mail-Copies-To: never User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/23.1 (gnu/linux) Cancel-Lock: sha1:0G+YuIT7OgN8mky5fTGgcPj9hso= X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-5 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201007/37 X-Sequence-Number: 34350 (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