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

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 tbllogregraverage5
AS (

SELECT a.obsid_distinct, a.Y
FROM
tbllogregrscore1 a
UNION ALL
SELECT b.obsid_distinct, b.Y
FROM
tbllogregrscore2 b
UNION ALL
SELECT c.obsid_distinct, c.Y
FROM
tbllogregrscore3 c
UNION ALL
SELECT d.obsid_distinct, d.Y
FROM
tbllogregrscore4 d
UNION ALL
SELECT e.obsid_distinct, e.Y
FROM
tbllogregrscore5 e
)
with data;

drop table logregrmean5;
create multiset table logregrmean5
AS
(SELECT obsid_distinct, AVG(Y) as Y
FROM tbllogregraverage5
GROUP 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
Teradata Employee

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)))||'.Y
FROM
tbllogregrscore'||(day_of_calendar (format '999')(char(3)))||' a'||(day_of_calendar (format '999') (char(3)))||
' UNION ALL '
from sys_calendar.calendar
where day_of_calendar<=30
order by day_of_calendar