Linear regressions in EQL

Using the syntax described in this topic, you can produce linear regressions in EQL.

Using the following data set:

ID	X	Y
1	60	3.1
2	61	3.6
3	62	3.8
4	63	4
5	65	4.1

The following simple formulation:

y = A + Bx

Can be expressed in EQL as:

RETURN Regression AS 
SELECT
    COUNT(ID) AS N,
    SUM(X) AS sumX,
    SUM(Y) AS sumY,
    SUM(X*Y) AS sumXY,
    SUM(X*X) AS sumX2,
    ((N*sumXY)-(sumX*sumY)) /
     ((N*sumX2)-(sumX*sumX)) AS B,
    (sumY-(B*sumX))/N AS A
FROM DataState
GROUP

With the result:

N	sumX	sumY	sumXY	sumX2	B	A
5	311.000000	18.600000	1159.700000	19359.000000	0.187838	-7.963514

Using the regression results

For y = A + Bx:

DEFINE Regression AS 
SELECT
    COUNT(ID) AS N,
    SUM(X) AS sumX,
    SUM(Y) AS sumY,
    SUM(X*Y) AS sumXY,
    SUM(X*X) AS sumX2,
    ((N*sumXY)-(sumX*sumY)) /
    ((N*sumX2)-(sumX*sumX)) AS B,
    (sumY-(B*sumX))/N AS A
FROM DataState
GROUP

RETURN Results AS 
SELECT
    Y AS Y,  X AS X,   Regression[].A + Regression[].B * X AS Projection
...

As a final step in the example above, you would need to PAGE or GROUP what could be a very large number of results.