Hi,

When I discussed a case this morning where we need a kind of least common
multiple of some values, I came to realize that nearly no relational dbms
has a "product" aggregation function while all have "sum" available.

In our case, this was not a real issue, as we are assuming a value from
between 3 and 10 records to be multiplied, and doing the multiplication in
a cursor in a stored function does not need a lot of code, and is not
performance critical as well.

But to me it appears strange there does not seem to be more business
requirement for this. DOes averybody just sum numbers, and never multiply?
And obviously there is a bit more potential for overflow when multiplying
huge data sets than when summing them.
Maybe these are the reasons why nobody implements this?

What do you think?

Frank

On Tue, 23 Oct 2007 19:41:48 +0200, Frank Ploessel
<fpl... (AT) d_e (DOT) i_m_s_h_e_a_l_t_h.c_o_m> wrote:

1. yes. sum is common, product is rare
2. product is easy if numbers are positive. e^sum(ln(C))
3. it would be nice if we could write new aggregate functions somehow

On Wed, 24 Oct 2007 09:45:22 +0200, krisztian pinter
<pinter.krisztian (AT) chello (DOT) hu> wrote:


I was thinking of that, but as we needed exact results in that case, we
went for the cursor approach.

Frank

New aggregate functions can be implemented using the LIST() aggregate and a
UDF, for example using sa_split_list().

The performance will not be as good as an in-built aggregate function but it
may be a way to get some functionality that you need.

If there is sufficient interest in particular aggregate functions or the
ability to implement efficient user-defined aggregates, please consider
posting a request for such features to the product_futures_discussion news
group.

create temporary function F_Prod( @str LONG VARCHAR )
returns numeric
begin
declare @prod numeric;
set @prod=1;
for vals as curs cursor for
select row_value from sa_split_list( @str )
do
set @prod = @prod * cast( row_value as numeric );
end for;
return @prod;
end
go

select emp_fname, count(*), F_Prod(list(emp_id)), exp( sum( ln(
emp_id ) ) )
from employee
group by emp_fname


Regards,
- Ivan

"krisztian pinter" <pinter.krisztian (AT) chello (DOT) hu> wrote

On 24 Oct 2007 09:53:37 -0700, "Ivan T. Bowman"
<ibowman (AT) ianywhere (DOT) NOSPAM.com> wrote:

Cool! ...the next time Glenn yells at me about using UDFs, I can say
"Ivan said it's ok!" <evil grin>

Breck

--
Breck Carter [Team iAnywhere]
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
The book: http://www.risingroad.com/SQL_Anywhe...ers_Guide.html
breck.carter (AT) risingroad (DOT) com

One of Ivan's paragraphs bears repeating:

UDF's cause a procedure context to get instantiated and that will NEVER
be as fast as a built-in aggregate function. CPU consumption will
increase and overall throughput will be reduced, in comparison to a
builtin function. Caveat emptor.....

Glenn

Breck Carter [Team iAnywhere] wrote:
--
Glenn Paulley
Director, Engineering (Query Processing)
iAnywhere Solutions Engineering

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://case-express.sybase.com

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Whitepapers, TechDocs, and bug fixes are all available through the iAnywhere
Developer Community at www.ianywhere.com/developer