Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-sql by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group