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
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 |