[Firebird-devel] ODP: ODP: User-defined aggregate functions

[Karol B. <liv...@po...>]

>>Your syntax looks too much like a normal function, which I think is 
>>confusing.

For me this is positive aspect. Why do you need something really different.

>>I also don't see how your syntax discerns between accumulation and 
>>finishing, for example how would the value of `accumulated` in 
>>`custom_avg` be retained?

Accumulated is declared as variable and is initialized by engine at start of every grouping level (key)

Consider:
Engine call
Declare variable section and store variables in memory
It then call function for every row and by agg_i inform function if this is first row, next row, empty resultset or it is agg_finished.

>>How will your proposal work when the aggregate function is used in for 
>>example a window function with an order by? In that case intermediate 
>>results are needed.

>>Consider for example the difference between `count(*) over()` and 
>> `count(*) over(order by something)`, or say something like (Firebird 4) 
>>`avg(something) over(order by something rows between 5 preceding and 5 
>>following)`.

Good point. 
But i know how to suport this simple – really one change needed.
Below Avg which can work with window also

create aggregate function custom_avg (i double precision) returns double precision
as
    declare accumulated double precision = 0;
begin
   if (i is not null) then
       accumulated = accumulated + i;                

  if (agg_i>0) then
    RETURN accumulated / agg_i;
end

engine will decide if it need consume RESULT or not. 
E.g. in normal SELECT AVG(FIELD) FROM TABLE engine will use it only on agg_finished
In window, if engine need to have intermediate value it use value assigned in RETURN.
Still really simple.

Do you see more problems with it?
Udr will also work simple with this, it only must be informed about agg_i and agg_finished by param to udr function.

Pozdrawiam,
Karol Bieniaszewski

Od: Mark Rotteveel
Wysłano: sobota, 29 września 2018 17:46
Do: fir...@li...
Temat: Re: [Firebird-devel] ODP: User-defined aggregate functions

On 29-9-2018 16:46, Karol Bieniaszewski wrote:
> Hi,
> 
> Maybe i show my concept not so clearly.
> 
> Look how simple it is with my proposition and also how simple to 
> understand by users.

Your syntax looks too much like a normal function, which I think is 
confusing.

How will your proposal work when the aggregate function is used in for 
example a window function with an order by? In that case intermediate 
results are needed.

Consider for example the difference between `count(*) over()` and 
`count(*) over(order by something)`, or say something like (Firebird 4) 
`avg(something) over(order by something rows between 5 preceding and 5 
following)`.

I also don't see how your syntax discerns between accumulation and 
finishing, for example how would the value of `accumulated` in 
`custom_avg` be retained?

Mark
-- 
Mark Rotteveel


Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel