Re: Sequential event query

From: Allan Kamau <allank(at)sanbi(dot)ac(dot)za>
To: Steve Crawford <scrawford(at)pinpointresearch(dot)com>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: Sequential event query
Date: 2008-06-25 11:11:10
Message-ID: 486227CE.5000606@sanbi.ac.za
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

In response to

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Mark Stosberg 2008-06-25 13:50:18 GROUP BY on a column which might exist in one of two tables
Previous Message Richard Broersma 2008-06-24 20:57:19 Re: ANSI Standard