Thread: Detecting change in event properties

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

I must say that window functions are amazing - they're a whole new
world, really.

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?

Robert James <srobertjames@gmail.com> writes:
>> (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(...).

Right.

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

I don't see any way to achieve that with any of the built-in window
functions, but I believe it could be done by a custom window function.
Are you up for some C coding?

            regards, tom lane

On 10/25/13, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert James <srobertjames@gmail.com> writes:
>>> (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(...).
>
> Right.
>
>> 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?
>
> I don't see any way to achieve that with any of the built-in window
> functions, but I believe it could be done by a custom window function.
> Are you up for some C coding?

Hmmm... certainly nothing I would trust on a production db.

Is there a way to do it without C not using window functions? Perhaps
with some type of JOIN?

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

>Von: pgsql-general-owner@postgresql.org [pgsql-general-owner@postgresql.org]" im Auftrag von "Elliot
[yields.falsehood@gmail.com]
>Gesendet: Freitag, 25. Oktober 2013 20:33
>
>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?


Hello,

I would misuse GUC variables for this.
(using the functions current_setting and set_config)

define a set get and switch fuction (I use operators for better readability)
something like:

select 'a' ==> 'foo'
'a'
select 'b' <==> 'foo'
'a'
select <== 'foo'
'b'


and  in your query:

SELECT
 case when test then col <==> 'foo' else <== 'foo' end

regards,

Marc Mamin

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.

Marc Mamin-2 wrote
> I would misuse GUC variables for this.
> (using the functions current_setting and set_config)
>
> define a set get and switch fuction (I use operators for better
> readability)
> something like:
>
> select 'a' ==> 'foo'
> 'a'
> select 'b' <==> 'foo'
> 'a'
> select <== 'foo'
> 'b'
>
>
> and  in your query:
>
> SELECT
>  case when test then col <==> 'foo' else <== 'foo' end

Is it possible to alter GUC on a record-by-record basis?

Is this something you have actually done?

Even if it does technically work this seems like a last-resort kind of
solution.  The syntax (though that could be hidden in a wrapper function) is
definitely unusual and the abuse of the GUC system in this manner is
surprising.

How would this interface with a window function?  The main consideration is
dealing with multiple partitions and the fact that a window column
calculation requires the use of a function while this solution would seem to
preclude that.

David J.




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

Elliot wrote
> 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
> );

Conceptually similar to my array_last_nonnull(array_agg(...)) methodology
and the GUC methodology but has the advantage of saving minimal state
(compared to the array_agg()) and not abusing GUC for storage of the
single-value state.

The example would need the same alteration to the frame clause but otherwise
would appear to work in the manner presumed by the OP's original question.

David J.




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

>Marc Mamin-2 wrote
>> I would misuse GUC variables for this.
>> (using the functions current_setting and set_config)
>>
>> define a set get and switch fuction (I use operators for better
>> readability)
>> something like:
>>
>> select 'a' ==> 'foo'
>> 'a'
>> select 'b' <==> 'foo'
>> 'a'
>> select <== 'foo'
>> 'b'
>>
>>
>> and  in your query:
>>
>> SELECT
>>  case when test then col <==> 'foo' else <== 'foo' end
>
>Is it possible to alter GUC on a record-by-record basis?

yes, the underlying functions are volatile.
Which quite certainly imply that you cannot repeat the statement within a GROUP or ORDER BY...
And you must of course ensure that it get called on an ordered set.


>Is this something you have actually done?

yes

>
>Even if it does technically work this seems like a last-resort kind of
>solution.  The syntax (though that could be hidden in a wrapper function) is
>definitely unusual and the abuse of the GUC system in this manner is
>surprising.

Sure. There are a some caveat too:

 - it is quite slow

 - you should first initialize the GUC to avoid testing it in each call.
   This should be feasable with an extra function in a top WITH clause

 - GUC are stored as text. You need to define functions for each type you are interested in.
   This implies extra work for casting/decasting

 - NULLs are transformed to empty strings per default, so you may need to care for this in your functions.

Marc Mamin

>
>David J.
>
>