Re: partitioning a dataset + employing hysteresis condition

On 14/11/11 18:35, Amit Dor-Shifer wrote:
>
> On Mon, Nov 14, 2011 at 4:29 PM, Amit Dor-Shifer
> <amit(dot)dor(dot)shifer(at)gmail(dot)com <mailto: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);
Hi ,

This is my attempt...

DROP TABLE IF EXISTS phone_call;

CREATE TABLE phone_call
(
device_id int NOT NULL,
start_time timestamptz NOT NULL,
term_status int NOT NULL,
PRIMARY KEY (device_id, start_time, term_status)
);

INSERT INTO
phone_call
(
device_id,
start_time,
term_status
)
VALUES
(10, '20100701T151433', 0),
(20, '20100701T151533', 0),
(20, '20100701T151633', 2),
(30, '20100701T151433', 0),
(30, '20100701T151533', 2),
(30, '20100701T151633', 2),
(40, '20100701T004022', 0),
(40, '20100701T004122', 2),
(40, '20100701T004622', 2),
(40, '20100701T010022', 2),
(40, '20100701T012122', 2),
(50, '20100701T120000', 0),
(50, '20100701T120100', 2),
(50, '20100701T120200', 2),
(50, '20100701T120300', 2),
(60, '20100701T090000', 0),
(60, '20100701T090200', 2),
(60, '20100701T100000', 0),
(60, '20100701T100100', 2),
(60, '20100701T100200', 2),
(60, '20100701T100300', 2),
(60, '20100701T101000', 2),
(60, '20100701T102000', 2),
(60, '20100701T104000', 2),
(60, '20100701T105000', 2),
(60, '20100701T105200', 2),
(60, '20100701T105600', 2),
(60, '20100701T500300', 0),
(60, '20100701T501400', 2);
INSERT INTO phone_call(start_time, device_id, term_status) VALUES (now()
- interval '10 minutes', 2, 0);
INSERT INTO phone_call(start_time, device_id, term_status) VALUES (now()
- interval '9 minutes', 2, 1);
INSERT INTO phone_call(start_time, device_id, term_status) VALUES (now()
- interval '7 minutes', 2, 1);
INSERT INTO phone_call(start_time, device_id, term_status) VALUES (now()
- interval '6 minutes', 2, 1);
INSERT INTO phone_call(start_time, device_id, term_status) VALUES (now()
- interval '5 minutes', 2, 0);
INSERT INTO phone_call(start_time, device_id, term_status) VALUES (now()
- interval '4 minutes', 2, 2);
INSERT INTO phone_call(start_time, device_id, term_status)VALUES (now()
- interval '3 minutes', 2, 2);
INSERT INTO phone_call(start_time, device_id, term_status)VALUES (now()
- interval '2 minutes', 2, 2);

TABLE phone_call;

SELECT DISTINCT
pc.device_id
FROM
phone_call pc
WHERE
pc.term_status = 0
AND 3 <=
(
SELECT count(*)
FROM phone_call pc1
WHERE
pc1.device_id = pc.device_id
AND pc1.term_status = 2
AND pc1.start_time > pc.start_time
AND pc1.start_time <
COALESCE
(
(
SELECT min(pc2.start_time)
FROM phone_call pc2
WHERE
pc2.term_status = 0
AND pc2.start_time > pc.start_time
)
, '9999-12-31'
)
GROUP BY
pc1.device_id
HAVING
max(pc1.start_time) >= min(pc1.start_time) + interval '2
minute'
)
ORDER BY
pc.device_id
/**/;/**/

Regards,
Gavin