Re: partitioning a dataset + employing hysteresis condition

On Nov 15, 2011, at 15:28, Gavin Flower <GavinFlower(at)archidevsys(dot)co(dot)nz> wrote:

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

Alternative thought,

Have a Boolean field which is set to true for non-zero entries and false for zeros. Upon entering a zero into the table, for a given device, set all currently true records to false. Combine with a partial index on the true and you can quickly get a listing of all devices in error mode and all the recent error entries.

David J.