Re: [HACKERS] First Aggregate Funtion?
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] First Aggregate Funtion?
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.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] First Aggregate Funtion?
On Mon, Jul 20, 2015 at 8:40 AM, sudalai sudala...@gmail.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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] First Aggregate Funtion?
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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] First Aggregate Funtion?
On Mon, Jul 20, 2015 at 10:06 AM, Paul A Jungwirth p...@illuminatedcomputing.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] First Aggregate Funtion?
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] First Aggregate Funtion?
On Mon, Jul 20, 2015 at 11:06 AM, Paul A Jungwirth p...@illuminatedcomputing.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@postgresql.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.
Re: [HACKERS] First Aggregate Funtion?
On Tue, Jul 14, 2015 at 9:23 AM, sudalai sudala...@gmail.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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] First Aggregate Funtion?
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.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] First Aggregate Funtion?
On Fri, Mar 31, 2006 at 15:02:47 -0600, Tony Caduto [EMAIL PROTECTED] 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. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] First Aggregate Funtion?
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@svana.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. signature.asc Description: Digital signature
Re: [HACKERS] First Aggregate Funtion?
On 3/31/06, Martijn van Oosterhout kleptog@svana.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 [EMAIL PROTECTED] GPLS -- PINES Development Database Developer http://open-ils.org ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
