Re: First Aggregate Funtion?

Has there ever been any talk of adding a first aggregate function?
It would make porting from Oracle and Access much easier.

Or is there something in the contrib modules that I might have missed?

Thanks,

--
Tony Caduto
AM Software Design
http://www.amsoftwaredesign.com
Home of PG Lightning Admin for Postgresql
Your best bet for Postgresql Administration

On Fri, Mar 31, 2006 at 03:02:47PM -0600, Tony Caduto wrote:
> Has there ever been any talk of adding a first aggregate function?
> It would make porting from Oracle and Access much easier.
>
> Or is there something in the contrib modules that I might have missed?

There are several oracle compatability modules:

http://pgfoundry.org/projects/oracompat/
http://pgfoundry.org/projects/orafce/

I'm sure there's many more if you look...
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

On 3/31/06, Martijn van Oosterhout <kleptog(at)svana(dot)org> wrote:
> On Fri, Mar 31, 2006 at 03:02:47PM -0600, Tony Caduto wrote:
> > Has there ever been any talk of adding a first aggregate function?
> > It would make porting from Oracle and Access much easier.
> >
> > Or is there something in the contrib modules that I might have missed?
>
> There are several oracle compatability modules:
>
> http://pgfoundry.org/projects/oracompat/
> http://pgfoundry.org/projects/orafce/
>
> I'm sure there's many more if you look...

If all you want is FIRST() and LAST() then:

-- Create a function that always returns the first non-NULL item
CREATE OR REPLACE FUNCTION public.first_agg ( anyelement, anyelement )
RETURNS anyelement AS $$
SELECT CASE WHEN $1 IS NULL THEN $2 ELSE $1 END;
$$ LANGUAGE SQL STABLE;

-- And then wrap an aggreagate around it
CREATE AGGREGATE public.first (
sfunc = public.first_agg,
basetype = anyelement,
stype = anyelement
);

-- Create a function that always returns the last non-NULL item
CREATE OR REPLACE FUNCTION public.last_agg ( anyelement, anyelement )
RETURNS anyelement AS $$
SELECT $2;
$$ LANGUAGE SQL STABLE;

-- And then wrap an aggreagate around it
CREATE AGGREGATE public.last (
sfunc = public.last_agg,
basetype = anyelement,
stype = anyelement
);

Hope that helps!

--
Mike Rylander
mrylander(at)gmail(dot)com
GPLS -- PINES Development
Database Developer
http://open-ils.org

On Fri, Mar 31, 2006 at 15:02:47 -0600,
Tony Caduto <tony_caduto(at)amsoftwaredesign(dot)com> wrote:
> Has there ever been any talk of adding a first aggregate function?
> It would make porting from Oracle and Access much easier.

Note, that without special support those functions aren't going to run
very fast. So you are still probably going to want to go back and
rewrite them to use something like DISTINCT ON anyway.

The above implementation of "first" aggregate returns the first non-NULL item
value.

To get *first row item value* for a column use the below implementation.

-- create a function that push at most two element on given array
-- push the first row value at second index of the array
CREATE OR REPLACE FUNCTION two_value_holder(anyarray, anyelement)
returns anyarray as $$
select case when array_length($1,1) < 2 then array_append($1,$2) else
$1 end ;
$$ language sql immutable;

-- create a function that returns second element of an array
CREATE OR replace FUNCTION second_element (ANYARRAY)
RETURNS ANYELEMENT AS $$ SELECT $1[2]; $$ LANGUAGE SQL;

-- create first aggregate function that return first_row item value
CREATE AGGREGATE first(anyelement)(
SFUNC=two_value_holder,
STYPE=ANYARRAY,
INITCOND='{NULL}',
FINALFUNC=second_element
);

I hope this work..
--
Sudalai

-----
sudalai
--
View this message in context: http://postgresql.nabble.com/First-Aggregate-Funtion-tp1943031p5857866.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

On Tue, Jul 14, 2015 at 9:23 AM, sudalai <sudalait2(at)gmail(dot)com> wrote:
> The above implementation of "first" aggregate returns the first non-NULL item
> value.
>
> To get *first row item value* for a column use the below implementation.
>
> -- create a function that push at most two element on given array
> -- push the first row value at second index of the array
> CREATE OR REPLACE FUNCTION two_value_holder(anyarray, anyelement)
> returns anyarray as $$
> select case when array_length($1,1) < 2 then array_append($1,$2) else
> $1 end ;
> $$ language sql immutable;
>
> -- create a function that returns second element of an array
> CREATE OR replace FUNCTION second_element (ANYARRAY)
> RETURNS ANYELEMENT AS $$ SELECT $1[2]; $$ LANGUAGE SQL;
>
> -- create first aggregate function that return first_row item value
> CREATE AGGREGATE first(anyelement)(
> SFUNC=two_value_holder,
> STYPE=ANYARRAY,
> INITCOND='{NULL}',
> FINALFUNC=second_element
> );
>
> I hope this work..

I don't think so, because arrays can contain duplicates.

rhaas=# select coalesce(first(x.column1), 'wrong') from (values
(null), ('correct')) x;
coalesce
----------
wrong
(1 row)

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

>I don't think so, because arrays can contain duplicates.

I just add two element to the array. One for INITCOND value NULL, second
for first row value.
So Array size is always 2. So no duplicates.

>rhaas=# select coalesce(first(x.column1), 'wrong') from (values
>(null), ('correct')) x;
>coalesce
>----------
> wrong
>(1 row)
It works correct..
I didn't said it returns, first non-null value for a column from aggregate
window.
I said my implementation returns first row value for a column.
Here first row element is "null ", hence it returns null.

check this....
db=# select
db-# coalesce(first(x.column1),'null') as col1 ,
db-# coalesce(first(x.column2),'null') as col2,
db-# coalesce(first(x.column3),'null') as col3
db-# from (values (null,'abc',null), ('correct','wrong','notsure'),
('second','second1','second3')) x
db-# ;
col1 | col2 | col3
------+------+------
null | abc | null
(1 row)

Its work correct. It returns first row value for a column.

--Sudalai

-----
sudalai
--
View this message in context: http://postgresql.nabble.com/First-Aggregate-Funtion-tp1943031p5858584.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

On Mon, Jul 20, 2015 at 8:40 AM, sudalai <sudalait2(at)gmail(dot)com> wrote:
>
>>I don't think so, because arrays can contain duplicates.
>
> I just add two element to the array. One for INITCOND value NULL, second
> for first row value.
> So Array size is always 2. So no duplicates.
>
>>rhaas=# select coalesce(first(x.column1), 'wrong') from (values
>>(null), ('correct')) x;
> >coalesce
>>----------
>> wrong
>>(1 row)
> It works correct..
> I didn't said it returns, first non-null value for a column from aggregate
> window.
> I said my implementation returns first row value for a column.
> Here first row element is "null ", hence it returns null.
>
>
> check this....
> db=# select
> db-# coalesce(first(x.column1),'null') as col1 ,
> db-# coalesce(first(x.column2),'null') as col2,
> db-# coalesce(first(x.column3),'null') as col3
> db-# from (values (null,'abc',null), ('correct','wrong','notsure'),
> ('second','second1','second3')) x
> db-# ;
> col1 | col2 | col3
> ------+------+------
> null | abc | null
> (1 row)
>
> Its work correct. It returns first row value for a column.

I was able to get ~45% runtime reduction by simply converting
"two_value_holder" from sql to plpgsql. SQL functions (unlike
pl/pgsql) are parsed and planned every time they are run unless they
are inlined. Our aggregation API unfortunately is a hard fence
against inlining; solving this is a major optimization target IMO.

merlin

> The above implementation of "first" aggregate returns the first non-NULL item
> value.

I'm curious what advantages this approach has over these FIRST/LAST
functions from the Wiki?:

https://wiki.postgresql.org/wiki/First/last_%28aggregate%29

Also to get the "first non-null value" you can apply an ordering to
just the aggregate function, e.g.:

select first(id order by start_time nulls last) from events;

If you want speed you should probably write a C version.

Is there something I'm missing?

Also since we're on the hackers list is this a proposal to add these
functions to core Postgres?

Yours,
Paul

On Mon, Jul 20, 2015 at 11:06 AM, Paul A Jungwirth <
pj(at)illuminatedcomputing(dot)com> wrote:

> > The above implementation of "first" aggregate returns the first non-NULL
> item
> > value.
>
> I'm curious what advantages this approach has over these FIRST/LAST
> functions from the Wiki?:
>
> https://wiki.postgresql.org/wiki/First/last_%28aggregate%29
>
> Also to get the "first non-null value" you can apply an ordering to
> just the aggregate function, e.g.:
>
> select first(id order by start_time nulls last) from events;
>
> If you want speed you should probably write a C version.
>
> Is there something I'm missing?
>
> Also since we're on the hackers list is this a proposal to add these
> functions to core Postgres?
>
> Yours,
> Paul
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

If it is a proposal to add to core, I'd like to suggest a close cousin
function of first()/last(): only(). [1]

It would behave like first() but would throw an error if it encountered
more than one distinct value in the window.

This would be helpful in dependent grouping situations like this:
select a.keyval, a.name_of_the thing, sum(b.metric_value) as
metric_value
from a
join b on b.a_keyval = a.keyval
group by a.keyval, a.name_of_the_thing

Now, everyone's made this optimization to reduce group-by overhead:
select a.keyval, min(a.name_of_the_thing) as name_of_the_thing,
sum(b.metric_value) as metric_value
from a
join b on b.a_keyval = a.keyval
group by a.keyval

Which works fine, but it's self-anti-documenting:
- it implies that name of the thing *could* be different across rows
with the same keyval
- it implies we have some business preference for names that are first
in alphabetical order.
- it implies that the string has more in common with the summed metrics
(imagine this query has dozens of them) than the key values to the left.

Using first(a.name_of_the_thing) is less overhead than min()/max(), but has
the same issues listed above.

By using only(a.name_of_the_thing) we'd have a bit more clarity that the
author expected all of those values to be the same across the aggregate
window, and discovering otherwise was reason enough to fail the query.

*IF* we're considering adding these to core, I think that only() would be
just a slight modification of the last() implementation, and could be done
at the same time.

[1] I don't care what it gets named. I just want the functionality.

On 7/20/15 6:02 PM, Corey Huinker wrote:
> By using only(a.name_of_the_thing) we'd have a bit more clarity that the
> author expected all of those values to be the same across the aggregate
> window, and discovering otherwise was reason enough to fail the query.
>
> *IF* we're considering adding these to core, I think that only() would be
> just a slight modification of the last() implementation, and could be done
> at the same time.
>
> [1] I don't care what it gets named. I just want the functionality.

A big +1 from me. In fact, I wrote a patch implementing this for 9.5
but never got around to finishing it.

.m

On Mon, Jul 20, 2015 at 10:06 AM, Paul A Jungwirth
<pj(at)illuminatedcomputing(dot)com> wrote:
>> The above implementation of "first" aggregate returns the first non-NULL item
>> value.
>
> I'm curious what advantages this approach has over these FIRST/LAST
> functions from the Wiki?:
>
> https://wiki.postgresql.org/wiki/First/last_%28aggregate%29
>
> Also to get the "first non-null value" you can apply an ordering to
> just the aggregate function, e.g.:
>
> select first(id order by start_time nulls last) from events;
>
> If you want speed you should probably write a C version.

C functions come with a lot of administration headaches, and the
performance gain will probably not be significant unless you totally
bypass the SPI interface. Even then, I suspect (vs the pl/pgsql
variant which caches plan) the majority of overhead is is in calling
the function, not the actual implementation. It's be interesting to
see the results though.

merlin

On 7/20/15 11:07 AM, Marko Tiikkaja wrote:
> On 7/20/15 6:02 PM, Corey Huinker wrote:
>> By using only(a.name_of_the_thing) we'd have a bit more clarity that the
>> author expected all of those values to be the same across the aggregate
>> window, and discovering otherwise was reason enough to fail the query.
>>
>> *IF* we're considering adding these to core, I think that only() would be
>> just a slight modification of the last() implementation, and could be
>> done
>> at the same time.
>>
>> [1] I don't care what it gets named. I just want the functionality.
>
> A big +1 from me. In fact, I wrote a patch implementing this for 9.5
> but never got around to finishing it.

A big +1 here too; I've wanted this many times in the past.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Data in Trouble? Get it in Treble! http://BlueTreble.com