partitioning a dataset + employing hysteresis condition

From: Amit Dor-Shifer <amit(dot)dor(dot)shifer(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: partitioning a dataset + employing hysteresis condition
Date: 2011-11-14 05:29:52
Message-ID: CAAznTxGRZ54RLEYfM=XE10uke4E5Li9OYp7JU_7WhcqCo2xadA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,
I've got this table:
create table phone_calls
(
start_time timestamp,
device_id integer,
term_status integer
);

It describes phone call events. A 'term_status' is a sort-of an exit status
for the call, whereby a value != 0 indicates some sort of error.
Given that, I wish to retrieve data on devices with a persisting error on
them, of a specific type. I.E. that their last term_status was, say 2. I'd
like to employ some hysteresis on the query: only consider a device as
errorring if:
1. the last "good" (0) term_status pre-dates a "bad" (2) term_status.
2. it has at least N "bad" term_status events following the last "good" one.
3. The time span between the first "bad" term_status event and the last one
is >= T minutes

For instance, w/the following data set:

INSERT INTO phone_calls(start_time, device_id, term_status) VALUES (now() -
interval '10 minutes', 1, 2, 0);
INSERT INTO phone_calls(start_time, device_id, term_status) VALUES (now() -
interval '9 minutes', 1, 2, 1);
INSERT INTO phone_calls(start_time, device_id, term_status) VALUES (now() -
interval '7 minutes', 1, 2, 1);
INSERT INTO phone_calls(start_time, device_id, term_status) VALUES (now() -
interval '6 minutes', 1, 2, 1);
INSERT INTO phone_calls(start_time, device_id, term_status) VALUES (now() -
interval '5 minutes', 1, 2, 0);
INSERT INTO phone_calls(start_time, device_id, term_status) VALUES (now() -
interval '4 minutes', 1, 2, 2);
INSERT INTO phone_calls(start_time, device_id, term_status)VALUES (now() -
interval '3 minutes', 1, 2, 2);
INSERT INTO phone_calls(start_time, device_id, term_status)VALUES (now() -
interval '2 minutes', 1, 2, 2);

with N=3, T=3
The query should return device_id 2 as errorring, as it registered 3 "bad"
events for at least 3 minutes.

I assume some partitioning needs to be employed here, but am not very
sure-footed on the subject.

Would appreciate some guidance.
10x,

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Amit Dor-Shifer 2011-11-14 05:35:37 Re: partitioning a dataset + employing hysteresis condition
Previous Message David Morton 2011-11-14 03:18:10 Where to get PG 9.0.5 SLES RPM's !?