[Firebird-devel] ODP: ODP: User-defined aggregate functions
>>- A clause to ignore null input values. If used, engine will filter out null >>input and not pass to the routine. >> This need to declare what.parameters shluld be considered I have thinked more about this and i can not imagine how this can work with function which have more then one parameter? e.g first param will have ignore null and second not. What walue will have then first parameter when we write ignore null for it? Pozdrawiam, Karol Bieniaszewski Od: Karol Bieniaszewski Wysłano: niedziela, 30 września 2018 09:01 Do: For discussion among Firebird Developers Temat: [Firebird-devel] ODP: User-defined aggregate functions >>I think four adjustments makes the initial syntax better: >>- Allow usage of more than one input parameter. Maybe even zero would be >>allowed. ok >> Instead of have special semantics for empty data source, declare the >> constant to be returned for it, say: >> create aggregate function my_count returning 0 for empty data source If >> this clause is omitted, null is returned for empty data source. This will limit functionality. What if you need to return something based on input parameters values? Not real sample but SELECT (SELECT CUSTOM_AGG(T2.A, T1.B, T1.C) FROM T2 WHERE T2.X=T1.Y) FROM T1 And you need to return e.g. T1.B*T1.C if there is empty resultset. With declarative value this will be not possible. >>- A clause to ignore null input values. If used, engine will filter out null >>input and not pass to the routine. This need to declare what.parameters >>shluld be considered This will be over complicated. And have same problem as above. >>- Instead of agg_finished, adjust SUSPEND (probably with another keyword) to >>somehing like: >>SUSPEND WHEN FETCHED DO WHEN FINISHED DO This is only my humble opinion, but i do not like suspend concept here. Engine know when provide values and when it need result from agg. See my previous email (with „Agg_result_needed”) and criticize it if you see any problems there. regards, Karol Bieniaszewski Od: Adriano dos Santos Fernandes Wysłano: niedziela, 30 września 2018 04:52 Do: For discussion among Firebird Developers Temat: Re: [Firebird-devel] User-defined aggregate functions I think four adjustments makes the initial syntax better: - Allow usage of more than one input parameter. Maybe even zero would be allowed. - Instead of have special semantics for empty data source, declare the constant to be returned for it, say: create aggregate function my_count returning 0 for empty data source If this clause is omitted, null is returned for empty data source. - A clause to ignore null input values. If used, engine will filter out null input and not pass to the routine. This need to declare what.parameters shluld be considered - Instead of agg_finished, adjust SUSPEND (probably with another keyword) to somehing like: SUSPEND WHEN FETCHED DO WHEN FINISHED DO Adriano (Sorry to not be more detailed, I'm writing from smartphone) Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[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 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
