>>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: firebird-devel@lists.sourceforge.net 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
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel