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