aggregation with complex function

Stefano Fioravanzo

9 Jun 2016 9 Jun '16

1:18 p.m.

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

Show replies by date

Roberto Cornacchia

9 Jun 9 Jun

1:32 p.m.

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 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 https://www.monetdb.org/mailman/listinfo/users-list

Stefano Fioravanzo

1:58 p.m.

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 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 https://www.monetdb.org/mailman/listinfo/users-list

Roberto Cornacchia

2:24 p.m.

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 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 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 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 https://www.monetdb.org/mailman/listinfo/users-list

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

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

Stefano Fioravanzo

2:42 p.m.

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 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 https://www.monetdb.org/mailman/listinfo/users-list

Roberto Cornacchia

3:01 p.m.

"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 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 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 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 < 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 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 https://www.monetdb.org/mailman/listinfo/users-list

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

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

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

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

Roberto Cornacchia

3:04 p.m.

Actually I meant more something like this, although MonetDB would translate the two very similarly: create view pairs as select distinct value1, value2, my_func(table.value1, table.value2) as new_value from table; select table.value1, table.value2, pairs.new_value from pairs, table where pairs.value1 = table.value1 and pair.value2 = table.value2; On 9 June 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 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 < 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 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 < 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 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 https://www.monetdb.org/mailman/listinfo/users-list

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

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

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

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

Stefano Fioravanzo

3:09 p.m.

...

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

Roberto Cornacchia

3:22 p.m.

Oh, so the result of the function does not only depend on the inputs. That's unfortunate. Have you actually tried to make a join between two large tables? MonetDB might handle it better one would think. So basically this: select t1.value1, t1.value2, my_func(t1.value1, t1.value2) from table t1, table t2 where t1.value1 = t2.value1 and t1.value2 = t2.value2; On 9 June 2016 at 17:09, Stefano Fioravanzo wrote:

...

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 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 < 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 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 < 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 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 https://www.monetdb.org/mailman/listinfo/users-list

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

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

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

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

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

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

Stefano Fioravanzo

16 Jun 16 Jun

12:39 p.m.

Roberto, regarding the previous problem: “I have a function that for a given row of a table returns a table. I want to execute the function for each row of the table and concatenate all the sub-tables returned from the function.” You proposed this solution: select * from my_func((select value1, value2 from table)); But with this query, my_func is called just once and as input parameters I have all the rows from the query. So, in order to achieve what I want, I’d have to loop through the rows using python and concatenate the results by myself. Is there a way to automate this thing? Stefano

...

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

Oh, so the result of the function does not only depend on the inputs. That's unfortunate.

Have you actually tried to make a join between two large tables? MonetDB might handle it better one would think. So basically this:

select t1.value1, t1.value2, my_func(t1.value1, t1.value2) from table t1, table t2 where t1.value1 = t2.value1 and t1.value2 = t2.value2;

On 9 June 2016 at 17:09, Stefano Fioravanzo mailto:fioravanzos@gmail.com> wrote:

...
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 mailto:roberto.cornacchia@gmail.com> 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 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

Roberto Cornacchia

1:09 p.m.

No, that is a somewhat hidden feature. - my_func takes two atomic values and returns a table: select * from my_func(2,3); - my_func takes a query returning tuples with two columns and returns a table (the concatenation of all tables): select * from my_func( (select value1, value2 from my_table) ); So this syntax does the loop + concatenation for you. Note that my_func is identical in the two cases: it takes two atomic values and returns a table. On 16 June 2016 at 14:39, Stefano Fioravanzo wrote:

...

Roberto,

regarding the previous problem: “I have a function that for a given row of a table returns a table. I want to execute the function for each row of the table and concatenate all the sub-tables returned from the function.”

You proposed this solution:

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

But with this query, my_func is called just once and as input parameters I have all the rows from the query. So, in order to achieve what I want, I’d have to loop through the rows using python and concatenate the results by myself.

Is there a way to automate this thing?

Stefano

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

Oh, so the result of the function does not only depend on the inputs. That's unfortunate.

Have you actually tried to make a join between two large tables? MonetDB might handle it better one would think. So basically this:

select t1.value1, t1.value2, my_func(t1.value1, t1.value2) from table t1, table t2 where t1.value1 = t2.value1 and t1.value2 = t2.value2;

On 9 June 2016 at 17:09, Stefano Fioravanzo wrote:

...
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 < roberto.cornacchia@gmail.com> 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 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 < 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 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 < 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 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 https://www.monetdb.org/mailman/listinfo/users-list

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

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

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

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

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

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

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

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

Stefano Fioravanzo

1:36 p.m.

Well, no… Let me show you what is happening to me: Let’s say I have this table (my_table) +------+-------+ | a | b | ------------------ | 1 | 2 | | 10 | 20 | | 100 | 200 | +------+-------+ I create a function which take as input a and b and returns a simple table of two rows like this: +------+-------+ | a | b | ------------------ | a | b | | a+1 | b+1 | +------+-------+ (The input as first row and the input+1 as second row. Ok, now I want to do this thing on the whole table. So, I the function is applied to every row I will get an output like this: +------+-------+ | a | b | ------------------ | 1 | 2 | | 2 | 3 | | 10 | 20 | | 11 | 21 | | 100 | 200 | | 101 | 201 | +------+-------+ Wonderful, the function I wrote is: CREATE FUNCTION myfunc(n INTEGER, d INTEGER) RETURNS TABLE (a INTEGER, b INTEGER) LANGUAGE PYTHON { result = dict() result['a'] = n result['a'] = numpy.append(result['a'], result['a']+1) result['b'] = d result['b'] = numpy.append(result['b'], result['b']+1) return result }; Executing the query: Gives this as output: +------+------+ | a | b | ----------------- | 1 | 2 | | 10 | 20 | | 100 | 200 | | 2 | 3 | | 11 | 21 | | 101 | 201 | +------+------+ Clearly, the input parameters (n and d) are two array containing all the row coming form the query SELECT * FROM mytable. Then numpy sums the whole array to 1 and appends it. To further validate the different behaviour from what you have described, if I use a function that just prints the input like this: CREATE FUNCTION myfunc(n INTEGER, d INTEGER) RETURNS TABLE (s STRING) LANGUAGE PYTHON { result = dict() result['s'] = str(n) return result }; select * from myfunc((SELECT * FROM mytable)); I get: +---------------+ | s | ------------------- | [ 1 10 100] | +---------------+ If the function was really called for each row, I’d expect this: +---------------+ | s | ------------------- | 1 | | 10 | | 100 | +---------------+

...

On 16 Jun 2016, at 15:09, Roberto Cornacchia wrote:

No, that is a somewhat hidden feature.

- my_func takes two atomic values and returns a table:

select * from my_func(2,3);

- my_func takes a query returning tuples with two columns and returns a table (the concatenation of all tables):

select * from my_func( (select value1, value2 from my_table) );

So this syntax does the loop + concatenation for you. Note that my_func is identical in the two cases: it takes two atomic values and returns a table.

On 16 June 2016 at 14:39, Stefano Fioravanzo mailto:fioravanzos@gmail.com> wrote: Roberto,

regarding the previous problem: “I have a function that for a given row of a table returns a table. I want to execute the function for each row of the table and concatenate all the sub-tables returned from the function.”

You proposed this solution:

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

But with this query, my_func is called just once and as input parameters I have all the rows from the query. So, in order to achieve what I want, I’d have to loop through the rows using python and concatenate the results by myself.

Is there a way to automate this thing?

Stefano

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

Oh, so the result of the function does not only depend on the inputs. That's unfortunate.

Have you actually tried to make a join between two large tables? MonetDB might handle it better one would think. So basically this:

select t1.value1, t1.value2, my_func(t1.value1, t1.value2) from table t1, table t2 where t1.value1 = t2.value1 and t1.value2 = t2.value2;

On 9 June 2016 at 17:09, Stefano Fioravanzo mailto:fioravanzos@gmail.com> wrote:

...
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 mailto:roberto.cornacchia@gmail.com> 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 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

Roberto Cornacchia

2:04 p.m.

I wonder if this feature is working correctly with python UDFs, then. I use it for C UDFs, and works as expected. On 16 June 2016 at 15:36, Stefano Fioravanzo wrote:

...

Well, no… Let me show you what is happening to me:

Let’s say I have this table (my_table) +------+-------+ | a | b | ------------------ | 1 | 2 | | 10 | 20 | | 100 | 200 | +------+-------+

I create a function which take as input a and b and returns a simple table of two rows like this: +------+-------+ | a | b | ------------------ | a | b | | a+1 | b+1 | +------+-------+ (The input as first row and the input+1 as second row. Ok, now I want to do this thing on the whole table. So, I the function is applied to every row I will get an output like this: +------+-------+ | a | b | ------------------ | 1 | 2 | | 2 | 3 | | 10 | 20 | | 11 | 21 | | 100 | 200 | | 101 | 201 | +------+-------+

Wonderful, the function I wrote is:

CREATE FUNCTION myfunc(n INTEGER, d INTEGER) RETURNS TABLE (a INTEGER, b INTEGER) LANGUAGE PYTHON { result = dict() result['a'] = n result['a'] = numpy.append(result['a'], result['a']+1) result['b'] = d result['b'] = numpy.append(result['b'], result['b']+1) return result };

Executing the query:

Gives this as output: +------+------+ | a | b | ----------------- | 1 | 2 | | 10 | 20 | | 100 | 200 | | 2 | 3 | | 11 | 21 | | 101 | 201 | +------+------+ Clearly, the input parameters (n and d) are two array containing all the row coming form the query SELECT * FROM mytable. Then numpy sums the whole array to 1 and appends it.

To further validate the different behaviour from what you have described, if I use a function that just prints the input like this:

CREATE FUNCTION myfunc(n INTEGER, d INTEGER) RETURNS TABLE (s STRING) LANGUAGE PYTHON { result = dict() result['s'] = str(n) return result }; select * from myfunc((SELECT * FROM mytable));

I get:

+---------------+ | s | ------------------- | [ 1 10 100] | +---------------+

If the function was really called for each row, I’d expect this:

+---------------+ | s | ------------------- | 1 | | 10 | | 100 | +---------------+

On 16 Jun 2016, at 15:09, Roberto Cornacchia wrote:

No, that is a somewhat hidden feature.

- my_func takes two atomic values and returns a table:

select * from my_func(2,3);

- my_func takes a query returning tuples with two columns and returns a table (the concatenation of all tables):

select * from my_func( (select value1, value2 from my_table) );

So this syntax does the loop + concatenation for you. Note that my_func is identical in the two cases: it takes two atomic values and returns a table.

On 16 June 2016 at 14:39, Stefano Fioravanzo wrote:

...
Roberto,

regarding the previous problem: “I have a function that for a given row of a table returns a table. I want to execute the function for each row of the table and concatenate all the sub-tables returned from the function.”

You proposed this solution:

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

But with this query, my_func is called just once and as input parameters I have all the rows from the query. So, in order to achieve what I want, I’d have to loop through the rows using python and concatenate the results by myself.

Is there a way to automate this thing?

Stefano

On 09 Jun 2016, at 17:22, Roberto Cornacchia < roberto.cornacchia@gmail.com> wrote:

Oh, so the result of the function does not only depend on the inputs. That's unfortunate.

Have you actually tried to make a join between two large tables? MonetDB might handle it better one would think. So basically this:

select t1.value1, t1.value2, my_func(t1.value1, t1.value2) from table t1, table t2 where t1.value1 = t2.value1 and t1.value2 = t2.value2;

On 9 June 2016 at 17:09, Stefano Fioravanzo wrote:

...
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 < roberto.cornacchia@gmail.com> 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 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 < 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 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 < 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 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 https://www.monetdb.org/mailman/listinfo/users-list

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

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

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

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

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

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

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

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

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

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

Stefan Manegold

2:21 p.m.

My (possibly educated?) guess: SQL, MAL, C UDFs get called per tuple (i.e., with scalar arguments); MAL iterates over the input table and concatenates the function results (see EXPLAIN of your query for details), unless (for MAL & C UDFs, only) you provide a "bulk" version of your function that expects arrays instead of scalar arguments (and hence, iterates of the input itself, and produces the entire output table (arrays) in one go; (see http://dev.monetdb.org/hg/MonetDB/file/fcd7666d6014/sql/backends/monet5/UDF/... for details); R & Python UDFs get called per table (i.e., with arrays as arguments); hence, like with the "bulk" versions above, your function needs to iterate over the input and produce the entire output. Hope this helps ... Stefan ----- On Jun 16, 2016, at 4:04 PM, Roberto Cornacchia roberto.cornacchia@gmail.com wrote:

...

I wonder if this feature is working correctly with python UDFs, then. I use it for C UDFs, and works as expected.

On 16 June 2016 at 15:36, Stefano Fioravanzo < fioravanzos@gmail.com > wrote:

Well, no… Let me show you what is happening to me:

Let’s say I have this table (my_table) +------+-------+ | a | b | ------------------ | 1 | 2 | | 10 | 20 | | 100 | 200 | +------+-------+

I create a function which take as input a and b and returns a simple table of two rows like this: +------+-------+ | a | b | ------------------ | a | b | | a+1 | b+1 | +------+-------+ (The input as first row and the input+1 as second row. Ok, now I want to do this thing on the whole table. So, I the function is applied to every row I will get an output like this: +------+-------+ | a | b | ------------------ | 1 | 2 | | 2 | 3 | | 10 | 20 | | 11 | 21 | | 100 | 200 | | 101 | 201 | +------+-------+

Wonderful, the function I wrote is:

CREATE FUNCTION myfunc(n INTEGER, d INTEGER) RETURNS TABLE (a INTEGER, b INTEGER) LANGUAGE PYTHON { result = dict() result['a'] = n result['a'] = numpy.append(result['a'], result['a']+1) result['b'] = d result['b'] = numpy.append(result['b'], result['b']+1) return result };

Executing the query:

Gives this as output: +------+------+ | a | b | ----------------- | 1 | 2 | | 10 | 20 | | 100 | 200 | | 2 | 3 | | 11 | 21 | | 101 | 201 | +------+------+ Clearly, the input parameters (n and d) are two array containing all the row coming form the query SELECT * FROM mytable. Then numpy sums the whole array to 1 and appends it.

To further validate the different behaviour from what you have described, if I use a function that just prints the input like this:

CREATE FUNCTION myfunc(n INTEGER, d INTEGER) RETURNS TABLE (s STRING) LANGUAGE PYTHON { result = dict() result['s'] = str(n) return result }; select * from myfunc((SELECT * FROM mytable));

I get:

+---------------+ | s | ------------------- | [ 1 10 100] | +---------------+

If the function was really called for each row, I’d expect this:

+---------------+ | s | ------------------- | 1 | | 10 | | 100 | +---------------+

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

No, that is a somewhat hidden feature.

- my_func takes two atomic values and returns a table:

select * from my_func(2,3);

- my_func takes a query returning tuples with two columns and returns a table (the concatenation of all tables):

select * from my_func( (select value1, value2 from my_table) );

So this syntax does the loop + concatenation for you. Note that my_func is identical in the two cases: it takes two atomic values and returns a table.

On 16 June 2016 at 14:39, Stefano Fioravanzo < fioravanzos@gmail.com > wrote:

Roberto,

regarding the previous problem: “I have a function that for a given row of a table returns a table. I want to execute the function for each row of the table and concatenate all the sub-tables returned from the function.”

You proposed this solution:

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

But with this query, my_func is called just once and as input parameters I have all the rows from the query. So, in order to achieve what I want, I’d have to loop through the rows using python and concatenate the results by myself.

Is there a way to automate this thing?

Stefano

On 09 Jun 2016, at 17:22, Roberto Cornacchia < roberto.cornacchia@gmail.com > wrote:

Oh, so the result of the function does not only depend on the inputs. That's unfortunate.

Have you actually tried to make a join between two large tables? MonetDB might handle it better one would think. So basically this:

select t1.value1, t1.value2, my_func(t1.value1, t1.value2) from table t1, table t2 where t1.value1 = t2.value1 and t1.value2 = t2.value2;

On 9 June 2016 at 17:09, Stefano Fioravanzo < fioravanzos@gmail.com > wrote:

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 < roberto.cornacchia@gmail.com > 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 < 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 < 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 < 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 < 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 < 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 https://www.monetdb.org/mailman/listinfo/users-list

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

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

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

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

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

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

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

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

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

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

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

Mark Raasveldt

2:14 p.m.

Hey Stefano, You are correct, Python functions are always called only once with all the input values. To achieve what you mentioned you will indeed need to manually loop over all the rows and concatenate the different results. The behavior described by Roberto might happen if you create a C UDF and only provide a scalar implementation of the UDF (not sure as I have never tried to do that), but Python UDFs always function as batch operators because calling a Python function once per row will result in a very large amount of unnecessary overhead. Regards, Mark ----- Original Message ----- From: "Stefano Fioravanzo" To: "users-list" Sent: Thursday, June 16, 2016 3:36:35 PM Subject: Re: aggregation with complex function Well, no… Let me show you what is happening to me: Let’s say I have this table (my_table) +------+-------+ | a | b | ------------------ | 1 | 2 | | 10 | 20 | | 100 | 200 | +------+-------+ I create a function which take as input a and b and returns a simple table of two rows like this: +------+-------+ | a | b | ------------------ | a | b | | a+1 | b+1 | +------+-------+ (The input as first row and the input+1 as second row. Ok, now I want to do this thing on the whole table. So, I the function is applied to every row I will get an output like this: +------+-------+ | a | b | ------------------ | 1 | 2 | | 2 | 3 | | 10 | 20 | | 11 | 21 | | 100 | 200 | | 101 | 201 | +------+-------+ Wonderful, the function I wrote is: CREATE FUNCTION myfunc(n INTEGER, d INTEGER) RETURNS TABLE (a INTEGER, b INTEGER) LANGUAGE PYTHON { result = dict() result['a'] = n result['a'] = numpy.append(result['a'], result['a']+1) result['b'] = d result['b'] = numpy.append(result['b'], result['b']+1) return result }; Executing the query: Gives this as output: +------+------+ | a | b | ----------------- | 1 | 2 | | 10 | 20 | | 100 | 200 | | 2 | 3 | | 11 | 21 | | 101 | 201 | +------+------+ Clearly, the input parameters (n and d) are two array containing all the row coming form the query SELECT * FROM mytable. Then numpy sums the whole array to 1 and appends it. To further validate the different behaviour from what you have described, if I use a function that just prints the input like this: CREATE FUNCTION myfunc(n INTEGER, d INTEGER) RETURNS TABLE (s STRING) LANGUAGE PYTHON { result = dict() result['s'] = str(n) return result }; select * from myfunc((SELECT * FROM mytable)); I get: +---------------+ | s | ------------------- | [ 1 10 100] | +---------------+ If the function was really called for each row, I’d expect this: +---------------+ | s | ------------------- | 1 | | 10 | | 100 | +---------------+

...

On 16 Jun 2016, at 15:09, Roberto Cornacchia wrote:

No, that is a somewhat hidden feature.

- my_func takes two atomic values and returns a table:

select * from my_func(2,3);

- my_func takes a query returning tuples with two columns and returns a table (the concatenation of all tables):

select * from my_func( (select value1, value2 from my_table) );

So this syntax does the loop + concatenation for you. Note that my_func is identical in the two cases: it takes two atomic values and returns a table.

On 16 June 2016 at 14:39, Stefano Fioravanzo mailto:fioravanzos@gmail.com> wrote: Roberto,

regarding the previous problem: “I have a function that for a given row of a table returns a table. I want to execute the function for each row of the table and concatenate all the sub-tables returned from the function.”

You proposed this solution:

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

But with this query, my_func is called just once and as input parameters I have all the rows from the query. So, in order to achieve what I want, I’d have to loop through the rows using python and concatenate the results by myself.

Is there a way to automate this thing?

Stefano

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

Oh, so the result of the function does not only depend on the inputs. That's unfortunate.

Have you actually tried to make a join between two large tables? MonetDB might handle it better one would think. So basically this:

select t1.value1, t1.value2, my_func(t1.value1, t1.value2) from table t1, table t2 where t1.value1 = t2.value1 and t1.value2 = t2.value2;

On 9 June 2016 at 17:09, Stefano Fioravanzo mailto:fioravanzos@gmail.com> wrote:

...
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 mailto:roberto.cornacchia@gmail.com> 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 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

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

Stefano Fioravanzo

2:16 p.m.

Mark, Thanks for the clarification!

...

On 16 Jun 2016, at 16:14, Mark Raasveldt wrote:

Hey Stefano,

You are correct, Python functions are always called only once with all the input values. To achieve what you mentioned you will indeed need to manually loop over all the rows and concatenate the different results.

The behavior described by Roberto might happen if you create a C UDF and only provide a scalar implementation of the UDF (not sure as I have never tried to do that), but Python UDFs always function as batch operators because calling a Python function once per row will result in a very large amount of unnecessary overhead.

Regards,

Mark

----- Original Message ----- From: "Stefano Fioravanzo" To: "users-list" Sent: Thursday, June 16, 2016 3:36:35 PM Subject: Re: aggregation with complex function

Well, no… Let me show you what is happening to me:

Let’s say I have this table (my_table) +------+-------+ | a | b | ------------------ | 1 | 2 | | 10 | 20 | | 100 | 200 | +------+-------+

I create a function which take as input a and b and returns a simple table of two rows like this: +------+-------+ | a | b | ------------------ | a | b | | a+1 | b+1 | +------+-------+ (The input as first row and the input+1 as second row. Ok, now I want to do this thing on the whole table. So, I the function is applied to every row I will get an output like this: +------+-------+ | a | b | ------------------ | 1 | 2 | | 2 | 3 | | 10 | 20 | | 11 | 21 | | 100 | 200 | | 101 | 201 | +------+-------+

Wonderful, the function I wrote is:

CREATE FUNCTION myfunc(n INTEGER, d INTEGER) RETURNS TABLE (a INTEGER, b INTEGER) LANGUAGE PYTHON { result = dict() result['a'] = n result['a'] = numpy.append(result['a'], result['a']+1) result['b'] = d result['b'] = numpy.append(result['b'], result['b']+1) return result };

Executing the query:

Gives this as output: +------+------+ | a | b | ----------------- | 1 | 2 | | 10 | 20 | | 100 | 200 | | 2 | 3 | | 11 | 21 | | 101 | 201 | +------+------+ Clearly, the input parameters (n and d) are two array containing all the row coming form the query SELECT * FROM mytable. Then numpy sums the whole array to 1 and appends it.

To further validate the different behaviour from what you have described, if I use a function that just prints the input like this:

CREATE FUNCTION myfunc(n INTEGER, d INTEGER) RETURNS TABLE (s STRING) LANGUAGE PYTHON { result = dict() result['s'] = str(n) return result }; select * from myfunc((SELECT * FROM mytable));

I get:

+---------------+ | s | ------------------- | [ 1 10 100] | +---------------+

If the function was really called for each row, I’d expect this:

+---------------+ | s | ------------------- | 1 | | 10 | | 100 | +---------------+

...
On 16 Jun 2016, at 15:09, Roberto Cornacchia wrote:

No, that is a somewhat hidden feature.

- my_func takes two atomic values and returns a table:

select * from my_func(2,3);

- my_func takes a query returning tuples with two columns and returns a table (the concatenation of all tables):

select * from my_func( (select value1, value2 from my_table) );

So this syntax does the loop + concatenation for you. Note that my_func is identical in the two cases: it takes two atomic values and returns a table.

On 16 June 2016 at 14:39, Stefano Fioravanzo mailto:fioravanzos@gmail.com> wrote: Roberto,

regarding the previous problem: “I have a function that for a given row of a table returns a table. I want to execute the function for each row of the table and concatenate all the sub-tables returned from the function.”

You proposed this solution:

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

But with this query, my_func is called just once and as input parameters I have all the rows from the query. So, in order to achieve what I want, I’d have to loop through the rows using python and concatenate the results by myself.

Is there a way to automate this thing?

Stefano

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

Oh, so the result of the function does not only depend on the inputs. That's unfortunate.

Have you actually tried to make a join between two large tables? MonetDB might handle it better one would think. So basically this:

select t1.value1, t1.value2, my_func(t1.value1, t1.value2) from table t1, table t2 where t1.value1 = t2.value1 and t1.value2 = t2.value2;

On 9 June 2016 at 17:09, Stefano Fioravanzo mailto:fioravanzos@gmail.com> wrote:

...
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 mailto:roberto.cornacchia@gmail.com> 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 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

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

3189

Age (days ago)

3196

Last active (days ago)

List overview

Download

15 comments

4 participants

participants (4)

Mark Raasveldt
Roberto Cornacchia
Stefan Manegold
Stefano Fioravanzo