On 2013-10-25 13:35, Robert James wrote:
On 10/25/13, Robert James <srobertjames@xxxxxxxxx> 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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
