Re: first_value/last_value

[Matija Lesar <matija.lesar@xxxxxxxxx>]

On 19 May 2016 at 05:04, Tom Smith <tomsmith1989sk@xxxxxxxxx> wrote:

It would really save all the troubles for many people if postgresql has a built-in first/last function  along with sum/avg.

There is already a C extension and a wiki sample  and  implemented for window function.

I am curious why these two functions were not added along  their window implementation counter part,

for completness and consistency

On Wed, May 18, 2016 at 10:42 PM, Melvin Davidson <melvin6925@xxxxxxxxx> wrote:

On Wed, May 18, 2016 at 10:36 PM, Adam Brusselback <adambrusselback@xxxxxxxxx> wrote:

Here is an example that works in a single query.  Since you have two different orders you want the data back in, you need to use subqueries to get the proper data back, but it works, and is very fast.

CREATE TEMPORARY TABLE foo AS 

SELECT generate_series as bar

FROM generate_series(1, 1000000);

CREATE INDEX idx_foo_bar ON foo (bar);

SELECT *

FROM (

SELECT bar

FROM foo

ORDER BY bar asc

LIMIT 1

) x

UNION ALL 

SELECT *

FROM (

SELECT bar

FROM foo

ORDER BY bar desc

LIMIT 1

) y;

DROP TABLE foo;

Seems to me SELECT min(<column>),  max(<column>) FROM deja.vu ; would also work.

--

Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

You can always create your aggregate function for this.

Here is example for getting non null first and last value:

CREATE OR REPLACE FUNCTION firstval_sfunc(anyelement, anyelement)
returns anyelement
language sql
as $BODY$
    SELECT CASE WHEN $1 is NULL THEN $2 ELSE $1 END;
$BODY$;

CREATE AGGREGATE myfirstval(anyelement) (
    SFUNC = firstval_sfunc,
    STYPE = anyelement
);

CREATE OR REPLACE FUNCTION lastval_sfunc(anyelement, anyelement)
returns anyelement
language sql
as $BODY$
    SELECT CASE WHEN $2 is NULL THEN $1 ELSE $2 END;
$BODY$;

CREATE AGGREGATE mylastval(anyelement) (
    SFUNC = lastval_sfunc,
    STYPE = anyelement
);


Outputs:

select myfirstval(b), mylastval(b) from unnest(array[3,2,null,12,-1]::int[]) b;
 myfirstval | mylastval
------------+-----------
          3 |        -1

 select myfirstval(b order by b), mylastval(b order by b) from unnest(array[3,2,null,12,-1]::int[]) b;
 myfirstval | mylastval
------------+-----------
         -1 |        12

select myfirstval(b), mylastval(b)  from generate_series(10,20000) as b;
 myfirstval | mylastval
------------+-----------
         10 |     20000

select myfirstval(b), mylastval(b) from unnest(array['c','b','t','x']::text[]) b;
 myfirstval | mylastval
------------+-----------
 c          | x

Bye,

Matija Lesar