I have been trying to perform ensemble modelling on logistic regression. I need to take the mean value of various values of Y generated for each regression for each of the unique ObsIDs. For example, if I am taking the average of 5 score tables, this is how I generate a single score table with average values of Y.
create multiset table tbllogregraverage5
SELECT a.obsid_distinct, a.Y
SELECT b.obsid_distinct, b.Y
SELECT c.obsid_distinct, c.Y
SELECT d.obsid_distinct, d.Y
SELECT e.obsid_distinct, e.Y
drop table logregrmean5;
create multiset table logregrmean5
(SELECT obsid_distinct, AVG(Y) as Y
GROUP BY obsid_distinct)
You can try something like this:
select 'SELECT obsid_distinct, a'||(day_of_calendar (format '999')(char(3)))||'.Y
tbllogregrscore'||(day_of_calendar (format '999')(char(3)))||' a'||(day_of_calendar (format '999') (char(3)))||
' UNION ALL '
order by day_of_calendar