Calculating univariate linear regression and correlation is simple in SQL.
It can be done like so:
select avg(y) - avg(x) * ((count(*) * sum(x*y)) - (sum(x)*sum(y)))/ ((count(*) * sum(x*x)) - (Sum(x)*Sum(x))) as b0, --intercept ((count(*) * sum(x*y)) - (sum(x)*sum(y)))/ ((count(*) * sum(x*x)) - (sum(x)*sum(x))) AS b1, (Avg(x * y) - Avg(x) * Avg(y)) / (StDevP(x) * StDevP(y)) AS Correlation from SomeTable
In my scenario, we already have a nominal variable with about 100 values. However, in each of those groups we would like to differentiate the population even further.
Let's say one has a table of 500 numeric continuous potential predictor variables and one target response variable.
For business reasons, we would like to keep using that nominal category variable but apply a simple linear regression after that. Of the 500 additional variables, there are ones that are good and most that are junk for predicting the target variable. From doing regression analysis on a sample, I know which ones overall that lasso would pick in addition to the nominal category. For simplicity, we would like to process this in SQL rather than have a job put chunks through R, Python etc.
What is the best way to choose which simple linear regression models to compose in the ensemble model? (The method of combining being taking the average of all chosen univariate regression models.)
Correlation feature selection chooses predictor variables that are highly correlated with the classification, yet uncorrelated to each other.
So one could pick the linear models based to be included in the ensemble by choosing those models in order of best correlation which are not correlated with previously chosen models. However, this has the complexity of computing a correlation matrix.
More simply one could do a step-wise forward selection and chose those models which improve the ensemble average.
Right now this is just a thought experiment, but I am wondering if someone else has researched this more fully?
Are there any systems that work like this?
What are the massive short-comings?
Obviously, Spark and the like are better, but under considered is a plain SQL solution.