Skip site navigation (1) Skip section navigation (2)

Sequential event query

From: Steve Crawford <scrawford(at)pinpointresearch(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Sequential event query
Date: 2008-06-24 20:53:48
Message-ID: 48615EDC.6040501@pinpointresearch.com (view raw or flat)
Thread:
Lists: pgsql-sql
I have a table that includes the following columns:
event_time timestamptz
device_id integer
event_type integer
...

There are hundreds of unique device_ids, about ten event_types and 
millions of records in the table. Devices can run the gamut from idle to 
fully utilized so for any given time-period a device might have anywhere 
from zero to thousands of events. I am trying to concoct two queries.

1: Analysis query to determine the distribution of sequential 
event_types. For example, if the event_types, in chronological order, were:
1
3
1
4
4
5
4
2
2
2
4
4
7
4
4

I would want to get:
event_type, sequential_events, occurrences
1,1,2
2,3,1
3,1,1
4,1,1
4,2,3
5,1,1
7,1,1


2: Listing of all devices where the most recent N events are all 
identical. As noted above, the varying load on the devices means that 
for device 1, the last N might be the last 2 minutes but for device 3 it 
might be a day or two. I am looking for a query that will list any 
device having no variation in the recent events.

Cheers,
Steve



Responses

pgsql-sql by date

Next:From: Richard BroersmaDate: 2008-06-24 20:57:19
Subject: Re: ANSI Standard
Previous:From: Andreas Joseph KroghDate: 2008-06-24 19:40:01
Subject: Re: Verify Execute

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group