Combine a table-returning function with the source table
For using the MonetDB-rmath extension, I was interested in using table-returning functions to return multiple values. Is there a fast and elegant way to combine the results with the source table in SQL? To make this concrete, consider some test data and a function to calculate a 95% confidence interval from a mean and standard error:
drop table test; create table test as select cast(value as double) as value from generate_series(0,5); drop function cis(double,double); create function cis(mu double, se double) returns table(lci double, uci double) begin return select mu-1.96*se, mu+1.96*se; end;
We can then call the table-returning function:
select * from cis((select value, 1.0 from test));
If we want to re-join the results with the source table, we could use row_number() or use lateral:
-- Using row_number: select * from (select *, row_number() over() as rid from test) as t1 natural join (select *, row_number() over() as rid from cis((select value,1.0 from test))) as t2;
-- Using lateral: select * from test, lateral cis(test.value, cast(1.0 as double)) as t2;
I believe that the latter will lead to a slow loop, even when BAT functions are available. The table-returning function can be used in the select statement only for a scalar expression:
select cis(0,1).*; -- ok select cis(0,1).* from test; -- Error: no such operator 'cis' select cis(value,1).* from test; -- Error: identifier 'value' unknown
Again, is there a fast and elegant way to combine the source table with a table-returning function in SQL?
Kindly, Mark.
As a follow-up to my earlier inquiry: how difficult would it be to allow for a table-returning function in the select clause in MonetDB? This elegant solution is possible in Postgresql:
test=> drop function cis(double precision,double precision); DROP FUNCTION test=> create function cis(mu double precision, se double precision) returns table(lci double precision, uci double precision) as 'select mu-1.96*se, mu+1.96*se' language sql; CREATE FUNCTION test=> select generate_series, (cis(generate_series,3)).* from generate_series(1,3); generate_series | lci | uci -----------------+-------+------ 1 | -4.88 | 6.88 2 | -3.88 | 7.88 3 | -2.88 | 8.88 (3 rows)
Other wishes would include more complete support for window functions. The mal code by Moritz Bruder looked like a good start (https://www.monetdb.org/pipermail/users-list/2017-February/009709.html).
Kindly, Mark.
On 05/25/2018 11:06 AM, Mark Clements wrote:
For using the MonetDB-rmath extension, I was interested in using table-returning functions to return multiple values. Is there a fast and elegant way to combine the results with the source table in SQL? To make this concrete, consider some test data and a function to calculate a 95% confidence interval from a mean and standard error:
drop table test; create table test as select cast(value as double) as value from generate_series(0,5); drop function cis(double,double); create function cis(mu double, se double) returns table(lci double, uci double) begin return select mu-1.96*se, mu+1.96*se; end;
We can then call the table-returning function:
select * from cis((select value, 1.0 from test));
If we want to re-join the results with the source table, we could use row_number() or use lateral:
-- Using row_number: select * from (select *, row_number() over() as rid from test) as t1 natural join (select *, row_number() over() as rid from cis((select value,1.0 from test))) as t2;
-- Using lateral: select * from test, lateral cis(test.value, cast(1.0 as double)) as t2;
I believe that the latter will lead to a slow loop, even when BAT functions are available. The table-returning function can be used in the select statement only for a scalar expression:
select cis(0,1).*; -- ok select cis(0,1).* from test; -- Error: no such operator 'cis' select cis(value,1).* from test; -- Error: identifier 'value' unknown
Again, is there a fast and elegant way to combine the source table with a table-returning function in SQL?
Kindly, Mark.
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
Hai Mark,
I’m just wonder if this gives you wat you want:
create function cis(mu double, se double) returns table(mu_out double, lci double, uci double) begin return select mu, mu-1.96*se, mu+1.96*se; end;
Regards, Jennie
On 30 May 2018, at 16:36, Mark Clements mark.clements@ki.se wrote:
As a follow-up to my earlier inquiry: how difficult would it be to allow for a table-returning function in the select clause in MonetDB? This elegant solution is possible in Postgresql:
test=> drop function cis(double precision,double precision); DROP FUNCTION test=> create function cis(mu double precision, se double precision) returns table(lci double precision, uci double precision) as 'select mu-1.96*se, mu+1.96*se' language sql; CREATE FUNCTION test=> select generate_series, (cis(generate_series,3)).* from generate_series(1,3); generate_series | lci | uci -----------------+-------+------ 1 | -4.88 | 6.88 2 | -3.88 | 7.88 3 | -2.88 | 8.88 (3 rows)
Other wishes would include more complete support for window functions. The mal code by Moritz Bruder looked like a good start (https://www.monetdb.org/pipermail/users-list/2017-February/009709.html).
Kindly, Mark.
On 05/25/2018 11:06 AM, Mark Clements wrote:
For using the MonetDB-rmath extension, I was interested in using table-returning functions to return multiple values. Is there a fast and elegant way to combine the results with the source table in SQL? To make this concrete, consider some test data and a function to calculate a 95% confidence interval from a mean and standard error:
drop table test; create table test as select cast(value as double) as value from generate_series(0,5); drop function cis(double,double); create function cis(mu double, se double) returns table(lci double, uci double) begin return select mu-1.96*se, mu+1.96*se; end;
We can then call the table-returning function:
select * from cis((select value, 1.0 from test));
If we want to re-join the results with the source table, we could use row_number() or use lateral:
-- Using row_number: select * from (select *, row_number() over() as rid from test) as t1 natural join (select *, row_number() over() as rid from cis((select value,1.0 from test))) as t2;
-- Using lateral: select * from test, lateral cis(test.value, cast(1.0 as double)) as t2;
I believe that the latter will lead to a slow loop, even when BAT functions are available. The table-returning function can be used in the select statement only for a scalar expression:
select cis(0,1).*; -- ok select cis(0,1).* from test; -- Error: no such operator 'cis' select cis(value,1).* from test; -- Error: identifier 'value' unknown
Again, is there a fast and elegant way to combine the source table with a table-returning function in SQL?
Kindly, Mark.
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
Jennie,
Thank you for replying to this inquiry - it's appreciated.
Your solution gives the right result - but it requires changing the function call and will not generalise easily to other column types or other number of covariates. If we had another table such as:
drop table test; create table test as select 1 as id, 2 as sex, cast(3 as double) as mean, cast(4.0 as double) as sd; -- and repeating the function create or replace function cis(mu double, se double) returns table(lci double, uci double) begin return select mu-1.96*se, mu+1.96*se; end;
we can get all of the test table combined with the confidence interval by:
-- row_number() select * from (select *, row_number() over() as rid from test) as t1 natural join (select *, row_number() over() as rid from cis((select mean,sd from test))) as t2; -- or using lateral select * from test, lateral cis(test.mean,test.sd);
The row_number solution is fast, and the lateral solution is simple. I was hoping for:
select *, cis(mean,test).* from test;
Again, thank you for your help.
-- Mark
On 06/08/2018 01:53 PM, Ying Zhang wrote:
Hai Mark,
I’m just wonder if this gives you wat you want:
create function cis(mu double, se double) returns table(mu_out double, lci double, uci double) begin return select mu, mu-1.96*se, mu+1.96*se; end;
Regards, Jennie
Hi Mark,
just out of curiosity, (in the given case/example) what's wrong with a simple
select *, mean-1.96*sd as lci, mean+1.96*sd as uci from test;
or
create or replace function ci(se double) returns double begin return 1.96*se; end; select *, mean-ci(sd) as lci, mean+ci(sd) as uci from test;
in case you really want/need to avoid the "redundant" occurrence or "1.96" ...
Thanks!
Best, Stefan
----- On Jun 13, 2018, at 5:30 PM, Mark Clements mark.clements@ki.se wrote:
Jennie,
Thank you for replying to this inquiry - it's appreciated.
Your solution gives the right result - but it requires changing the function call and will not generalise easily to other column types or other number of covariates. If we had another table such as:
drop table test; create table test as select 1 as id, 2 as sex, cast(3 as double) as mean, cast(4.0 as double) as sd; -- and repeating the function create or replace function cis(mu double, se double) returns table(lci double, uci double) begin return select mu-1.96*se, mu+1.96*se; end;
we can get all of the test table combined with the confidence interval by:
-- row_number() select * from (select *, row_number() over() as rid from test) as t1 natural join (select *, row_number() over() as rid from cis((select mean,sd from test))) as t2; -- or using lateral select * from test, lateral cis(test.mean,test.sd);
The row_number solution is fast, and the lateral solution is simple. I was hoping for:
select *, cis(mean,test).* from test;
Again, thank you for your help.
-- Mark
On 06/08/2018 01:53 PM, Ying Zhang wrote:
Hai Mark,
I’m just wonder if this gives you wat you want:
create function cis(mu double, se double) returns table(mu_out double, lci double, uci double) begin return select mu, mu-1.96*se, mu+1.96*se; end;
Regards, Jennie
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
participants (3)
-
Mark Clements -
Stefan Manegold -
Ying Zhang