Re: Detecting change in event properties

On 2013-10-25 13:35, Robert James wrote:
> On 10/25/13, Robert James <srobertjames(at)gmail(dot)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