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