Hi Steve,
Am having difficulties (there is a chance I could be the only one)
trying to see how the results you've listed under "I would want to get:"
section can be generated from the information you have provided in your
implicit problem statement. Please reconstruct your question, show
simple and clear sample data along with desired results.
For example where does the event_type, sequential_events fields get
their values from given the sample data in a single field you've provided?
Allan.
Steve Crawford wrote:
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
May be a select event_type,sequential_events, count(*) as occurances
from blah group by ...
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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
