Database

## Calcultating the mean value of Ys in the score table from multiple score tables for each ObsID

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 tbllogregraverage5AS (SELECT a.obsid_distinct, a.YFROMtbllogregrscore1 aUNION ALLSELECT b.obsid_distinct, b.YFROMtbllogregrscore2 bUNION ALLSELECT c.obsid_distinct, c.YFROMtbllogregrscore3 cUNION ALLSELECT d.obsid_distinct, d.YFROMtbllogregrscore4 dUNION ALLSELECT e.obsid_distinct, e.YFROMtbllogregrscore5 e)with data;drop table logregrmean5;create multiset table logregrmean5AS(SELECT obsid_distinct, AVG(Y) as YFROM tbllogregraverage5GROUP BY obsid_distinct)with data;`

But as the number increases from 5 to, say 30, this code gets lengthy. Is there a way I can shorten this code and improve efficiency?

1 REPLY 1

## Re: Calcultating the mean value of Ys in the score table from multiple score tables for each ObsID

You can try something like this:

`select 'SELECT obsid_distinct, a'||(day_of_calendar (format '999')(char(3)))||'.YFROMtbllogregrscore'||(day_of_calendar (format '999')(char(3)))||' a'||(day_of_calendar (format '999') (char(3)))||' UNION ALL 'from sys_calendar.calendarwhere day_of_calendar<=30order by day_of_calendar`