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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-sql by date

  From Date Subject
Next Message Richard Broersma 2008-06-24 20:57:19 Re: ANSI Standard
Previous Message Andreas Joseph Krogh 2008-06-24 19:40:01 Re: Verify Execute