Re: [HACKERS] First Aggregate Funtion?

2015-07-21 Thread Jim Nasby

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?

2015-07-20 Thread sudalai

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?

2015-07-20 Thread Merlin Moncure
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?

2015-07-20 Thread Paul A Jungwirth
 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?

2015-07-20 Thread Merlin Moncure
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?

2015-07-20 Thread Marko Tiikkaja

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?

2015-07-20 Thread Corey Huinker
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?

2015-07-15 Thread Robert Haas
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?

2015-07-14 Thread sudalai
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?

2006-04-05 Thread Bruno Wolff III
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?

2006-03-31 Thread Tony Caduto

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?

2006-03-31 Thread Martijn van Oosterhout
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?

2006-03-31 Thread Mike Rylander
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