Re: first_value/last_value

On 19 May 2016 at 05:04, Tom Smith <tomsmith1989sk(at)gmail(dot)com> 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(at)gmail(dot)com>
> wrote:
>
>>
>>
>> On Wed, May 18, 2016 at 10:36 PM, Adam Brusselback <
>> adambrusselback(at)gmail(dot)com> 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