Re: Detecting change in event properties

Robert James wrote
> * Finding field x of the _previous_ row that meets a certain criteria
> (which the current row may or may not meet)
> )

This question could be phrased better. I provide an answer to my
interpretation below.

You'll need to play with the frame definition because I don't yet have that
syntax memorized and am too lazy to figure it out right now.

The following gives, for every row, the last "val" having a value less than
25. It does this by converting all other values to NULL than returning the
most proximate value that is not null. The ORDER BY in the OVER() clause
gives you an "unbounded preceding to current row" frame by default so the
current row is a valid value for the final answer.

WITH data (key, val) AS ( VALUES (1,10),(2,20),(3,30),(4,10),(5,25) )
SELECT key, val, array_last_nonnull(array_agg(CASE WHEN val < 25 THEN val
ELSE NULL END) OVER (ORDER BY key)) FROM data

where "array_last_nonnull(...)" is defined as:

CREATE OR REPLACE FUNCTION array_last_nonnull(in_array anyarray)
RETURNS anyelement
AS $$

SELECT unnest FROM (
SELECT unnest, row_number() OVER () AS array_index FROM (
SELECT unnest($1)
) explode ) filter
WHERE unnest IS NOT NULL
ORDER BY array_index DESC
LIMIT 1;

$$
LANGUAGE sql
STRICT
IMMUTABLE
;

This is probably not the most preformant solution but it is fairly simple,
easy to debug (i.e., you can always view the array_agg data), and gives you
a benchmark to compare against should you attempt alternatives.

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Detecting-change-in-event-properties-tp5775959p5775971.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.