Re: aggregation with complex function

Does this mean ".. as the group size” ? Yes

And from what you say it looks like the inputs of this function are only the distinct values of those two columns. Yes

So, say that for a group identified by any given (value1,value2) pair, the function returns the same value for each of the tuples in that group, right? Sadly, no. Here is the problem. The function returns different values for each row of the group, so I am actually force to do a join with the group!

On 09 Jun 2016, at 17:01, Roberto Cornacchia wrote:

"this function returns a table with as many rows as the group"

Does this mean ".. as the group size" ? And from what you say it looks like the inputs of this function are only the distinct values of those two columns. So, say that for a group identified by any given (value1,value2) pair, the function returns the same value for each of the tuples in that group, right?

Unless I am misunderstanding something, why don't you use a simple function that returns only one value (not a table with the same value repeated), and then join it back to the big table?

like:

create view pairs as select distinct value1,value2 from table);

select table.value1, table.value2, my_func(table.value1, table.value2) * table.value3 -- or whatever this needs to do from pairs, table where pairs.value1 = table.value1 and pair.value2 = table.value2;

This would not be very inefficient, because you are joining the big table with a much smaller table, and there are no concatenations involved.

Roberto

On 9 June 2016 at 16:42, Stefano Fioravanzo mailto:fioravanzos@gmail.com> wrote: Yes that should work. But, reasoning a little bit, I have a bigger problem.

At the beginning I spoke about 'group by' because what should be really done is this: From one big table I need to group by two columns (actually I do not have to use any aggregation function but…), for each group formed i need to call a function (with the two parameters used for grouping), this function returns a table with as many rows as the group, which has to be joined to the group (not the whole table!!!). So, for each group i have to do a join with a table coming from a function called for each group. Each resulting ‘group joined’ has to be concatenated in a final table.

The thing is, I could create temporary tables to do these things, but I have several hundred millions rows in my table, so I would like to do everything on the fly.

Specifically, I could very well use the query you proposed me in the previous mail, but that would produce another table with several hundred millions rows, to be joined with an equally big table…I would like to avoid that!

Any thoughts?

Thanks in advance

Stefano

...

On 09 Jun 2016, at 16:24, Roberto Cornacchia mailto:roberto.cornacchia@gmail.com> wrote:

Sorry, wrong syntax indeed. It is much simpler:

select * from my_func( (select distinct value1,value2 from table) );

On 9 June 2016 at 15:58, Stefano Fioravanzo mailto:fioravanzos@gmail.com> wrote: Roberto,

Yes you are right, I expressed myself wrong. What you suggested is exactly what I need, but it gives me an error.

syntax error, unexpected FROM, expecting UNION or EXCEPT or INTERSECT or ‘)'

The double from is suspicious, was it intended?

Stefano

...

On 09 Jun 2016, at 15:32, Roberto Cornacchia mailto:roberto.cornacchia@gmail.com> wrote:

Stefano,

What you wrote does not look like an aggregation.

This is an aggregation: you make groups on value1,value2, and for each group you aggregate values (e.g. value3,value4).

select value1, value2, my_aggr_function(value3, value4) from table group by value1, value2

However, if that returns more than one tuple per group, it's again not an aggregation.

I have the impression that what you are looking for is: for every tuple (which might come from a previous aggregation), return a table, and concatenate the results. This would look like:

select * from ( (select * from my_func(x.value1, x.value2) from (select distinct value1,value2 from table) as x) );

This is possible in MonetDB. It will make a table out of each distinct (value1,value2) pair, and give you back the concatenation of all results (mind the double parentheses in the from clause, they are needed).

I have done this and works well.

Roberto

On 9 June 2016 at 15:18, Stefano Fioravanzo mailto:fioravanzos@gmail.com> wrote: Hello,

I have an embedded python function which, given two integer parameters, after doing some stuff returns a table. I would like to group by a table by these two input parameter, and for each resulting group call the function. The result would be the concatenation of all the tables returned by the function.

I understand that a thing like this would be very easy if the function returned just one value e.g.: select my_aggr_function(value1, value2) from table group by value1, value2

But how can I do this if my_aggr_function returns multiple rows??

Thank you,

Stefano _______________________________________________ users-list mailing list users-list@monetdb.org mailto:users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list https://www.monetdb.org/mailman/listinfo/users-list

_______________________________________________ users-list mailing list users-list@monetdb.org mailto:users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list https://www.monetdb.org/mailman/listinfo/users-list

_______________________________________________ users-list mailing list users-list@monetdb.org mailto:users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list https://www.monetdb.org/mailman/listinfo/users-list

_______________________________________________ users-list mailing list users-list@monetdb.org mailto:users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list https://www.monetdb.org/mailman/listinfo/users-list

_______________________________________________ users-list mailing list users-list@monetdb.org mailto:users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list https://www.monetdb.org/mailman/listinfo/users-list

_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list