Stefan,
This is a good question. For the specific example, you are quite correct.
However, the motivation for the question came from using the MonetDB-rmath extension and embedded R: often it is more efficient to calculate a number of values (e.g. the confidence intervals and p-values) together and then return all of the values as a table. This probably also reflects that I am a statistician and an old R user.
The recent article by Raasveldt suggested that analysis objects from Python could be stored natively rather than being serialised BLOBs. This could be further extended to storing R objects natively. Members of the objects could then be extracted as they are needed. Admittedly, this would lose many relational advantages. My suggestion is halfway, where R and Python can be used for analytics, with simple relational storage of the results.
Actually, is there any way to optimise a lateral call using BATs?
-- Mark
On 13 Jun 2018 17:43, Stefan Manegold Stefan.Manegold@cwi.nl wrote:
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
-- | Stefan.Manegold@CWI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold/http://www.CWI.nl/~manegold/ | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) | _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list