From: | Amit Dor-Shifer <amit(dot)dor(dot)shifer(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: partitioning a dataset + employing hysteresis condition |
Date: | 2011-11-14 05:35:37 |
Message-ID: | CAAznTxGO3PKYy3+x_BsUo8Y3ie1NL7DsyYRp3C=MuiU7s7bLmA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Mon, Nov 14, 2011 at 4:29 PM, Amit Dor-Shifer
<amit(dot)dor(dot)shifer(at)gmail(dot)com>wrote:
> 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,
>
... fixed data set:
INSERT INTO phone_calls(start_time, device_id, term_status) VALUES (now() -
interval '10 minutes', 2, 0);
INSERT INTO phone_calls(start_time, device_id, term_status) VALUES (now() -
interval '9 minutes', 2, 1);
INSERT INTO phone_calls(start_time, device_id, term_status) VALUES (now() -
interval '7 minutes', 2, 1);
INSERT INTO phone_calls(start_time, device_id, term_status) VALUES (now() -
interval '6 minutes', 2, 1);
INSERT INTO phone_calls(start_time, device_id, term_status) VALUES (now() -
interval '5 minutes', 2, 0);
INSERT INTO phone_calls(start_time, device_id, term_status) VALUES (now() -
interval '4 minutes', 2, 2);
INSERT INTO phone_calls(start_time, device_id, term_status)VALUES (now() -
interval '3 minutes', 2, 2);
INSERT INTO phone_calls(start_time, device_id, term_status)VALUES (now() -
interval '2 minutes', 2, 2);
From | Date | Subject | |
---|---|---|---|
Next Message | Venkat Balaji | 2011-11-14 05:45:07 | Re: Incremental backup with RSYNC or something? |
Previous Message | Amit Dor-Shifer | 2011-11-14 05:29:52 | partitioning a dataset + employing hysteresis condition |