Hi, >From optimization POV one more system var should be provided Agg_result_needed true when engine need result of calculation false if not.
sample with avg: 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_result_needed and (agg_i>0)) then RETURN accumulated / agg_i; end Why it is needed. e.g. table with 1000 records query SELECT AVG(FIELD) FROM TABLE need result only after all 1000 records are parsed. One thousand additions and only one division. Without it we will have 1000 additions and 1000 divisions. For query like SELECT AVG(FIELD) FROM TABLE GROUP BY FLDX We will have same amount of divisions as group keys. For window function also not problem. „Yeld” is really not needed because the database engine controls calls not the function itself. Engine know when agg_finished is true/false and when it need result of agg (e.g. intermediate result in window function). „While” is also not needed here. Do you see any problem with this implementation? Speed, implementation in Firebird, udr, user POV? Regards, Karol Bieniaszewski Od: Karol Bieniaszewski Wysłano: sobota, 29 września 2018 19:29 Temat: [Firebird-devel] ODP: ODP: User-defined aggregate functions >>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
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
