Обсуждение: Sequential event query

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

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

Allan Kamau wrote:
> 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.
Remember the events are listed chronologically. Stated verbosely:

1: Value 1 happened 1 sequential time
3: Value 3 happened 1 sequential time
1: Value 1 happened 1 sequential time
4:
4: Value 4 happened 2 sequential times
5: Value 5 happened 1 sequential time
4 : Value 4 happened 1 sequential time
2:
2:
2 : Value 2 happened 3 sequential times
4:
4 : Value 4 happened 2 sequential times
7 : Value 7 happened 1 sequential time
4 : Value 4 happened 1 sequential time

So the summary is:
Event type 1 appeared in a cluster of 1 event  2 times
Event type 2 appeared in a cluster of 3 events 1 time
Event type 3 appeared in a cluster of 1 event  1 time
Event type 4 appeared in a cluster of 1 event  1 time
Event type 4 appeared in a cluster of 2 events 3 times
Event type 5 appeared in a cluster of 1 event  1 time
Event type 7 appeared in a cluster of 1 event  1 time

Given the summary table, I can then look at distributions of run-lengths 
for different event-types. If I know the distributions for a system 
operating normally, I can establish criteria to use for generating 
alerts and alarms.

I have generated the above using external programs but if I can do so 
with a query, I would prefer to do so.

Cheers,
Steve

Steve Crawford wrote:
> Allan Kamau wrote:
>> 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.
> Remember the events are listed chronologically. Stated verbosely:
>
> 1: Value 1 happened 1 sequential time
> 3: Value 3 happened 1 sequential time
> 1: Value 1 happened 1 sequential time
> 4:
> 4: Value 4 happened 2 sequential times ...
And for completeness I suppose I should state that I am aware that a run 
of, say, 4 sequential events includes runs of 1, 2 and 3 events. I'm 
only interested in the longest run in order to set thresholds.

Cheers,
Steve