Re: Detecting change in event properties

On 2013-10-25 13:35, Robert James wrote:
> On 10/25/13, Robert James <srobertjames@gmail.com> wrote:
>> I have a table of (timed) events, and I'm interested in marking events
>> whose properties have changed from the previous event.
>>
>> I believe this can be done with window functions, but I'm not sure
>> how.  What window function can give me a field from the _previous_
>> row?
>>
>> (To elaborate, I'm interested in:
>> * Finding field x of the _previous_ row
>> * Finding field x of the _next_ row
>> * Finding field x of the _previous_ row that meets a certain criteria
>> (which the current row may or may not meet)
>> )
> The first two are actually trivial - lag(field_x) over (order by [same
> order as query]) and lead(...).
>
> But the last one seems ellusive - How can I find the value of field x
> on the previous row WHERE a criteria is met? Is it possible to do this
> at all with a window function?
>
>

Maybe a custom aggregate that takes the last item in a set?

CREATE OR REPLACE FUNCTION public.last_agg ( anyelement, anyelement )
RETURNS anyelement LANGUAGE sql IMMUTABLE STRICT AS $$
         SELECT $2;
$$;

CREATE AGGREGATE public.last (
         sfunc    = public.last_agg,
         basetype = anyelement,
         stype    = anyelement
);

Same set up as last time:
create temp table data (i int, val char);

insert into data (val, i)
values
('A',1),
('A',2),
('A',3),
('B',4),
('C',5),
('A',6),
('D',7),
('A',8),
('A',9),
('D',10),
('D',11),
('B',12),
('C',13),
('C',14)
;

And usage with a case like this? I read somewhere that filtering in
aggregates is coming soon-ish (or maybe already?) to avoid the case, but
this should suffice.

select i, val, last(case val when 'B' then i end) over (order by i asc)
from data
order by i asc
;

i    val    last
1    A    <NULL>
2    A    <NULL>
3    A    <NULL>
4    B    4
5    C    4
6    A    4
7    D    4
8    A    4
9    A    4
10    D    4
11    D    4
12    B    12
13    C    12
14    C    12