Linear regression

Configure the component

PAI -name linearregression
    -project algo_public
    -DinputTableName=lm_test_input
    -DfeatureColNames=x
    -DlabelColName=y
    -DmodelName=lm_test_input_model_out;

Example

This example walks through the full workflow: create training data, train a linear regression model, run predictions, and inspect both output tables.

Step 1: Create the training data

DROP TABLE IF EXISTS lm_test_input;
CREATE TABLE lm_test_input AS
SELECT * FROM
(
  SELECT 10 AS y, 1.84 AS x1, 1 AS x2, '0:1.84 1:1' AS sparsecol1
    UNION ALL
  SELECT 20 AS y, 2.13 AS x1, 0 AS x2, '0:2.13' AS sparsecol1
    UNION ALL
  SELECT 30 AS y, 3.89 AS x1, 0 AS x2, '0:3.89' AS sparsecol1
    UNION ALL
  SELECT 40 AS y, 4.19 AS x1, 0 AS x2, '0:4.19' AS sparsecol1
    UNION ALL
  SELECT 50 AS y, 5.76 AS x1, 0 AS x2, '0:5.76' AS sparsecol1
    UNION ALL
  SELECT 60 AS y, 6.68 AS x1, 2 AS x2, '0:6.68 1:2' AS sparsecol1
    UNION ALL
  SELECT 70 AS y, 7.58 AS x1, 0 AS x2, '0:7.58' AS sparsecol1
    UNION ALL
  SELECT 80 AS y, 8.01 AS x1, 0 AS x2, '0:8.01' AS sparsecol1
    UNION ALL
  SELECT 90 AS y, 9.02 AS x1, 3 AS x2, '0:9.02 1:3' AS sparsecol1
    UNION ALL
  SELECT 100 AS y, 10.56 AS x1, 0 AS x2, '0:10.56' AS sparsecol1
) tmp;

Step 2: Train the model

The following command trains the model with model evaluation and coefficient estimation enabled. Output is written to lm_test_input_conf_out.

PAI -name linearregression
    -project algo_public
    -DinputTableName=lm_test_input
    -DlabelColName=y
    -DfeatureColNames=x1,x2
    -DmodelName=lm_test_input_model_out
    -DoutputTableName=lm_test_input_conf_out
    -DenableCoefficientEstimate=true
    -DenableFitGoodness=true
    -Dlifecycle=1;

Step 3: Run predictions

Pass the trained model and the original input table to the prediction component. The output includes both actual and predicted values.

pai -name prediction
    -project algo_public
    -DmodelName=lm_test_input_model_out
    -DinputTableName=lm_test_input
    -DoutputTableName=lm_test_input_predict_out
    -DappendColNames=y;

Step 4: View the model evaluation table

Query lm_test_input_conf_out to check goodness-of-fit metrics and regression coefficients.

+----------------------+---------------------+---------------------+--------+--------------------------------------------+-------------+
| colname              | value               | tscore              | pvalue | confidenceinterval                         | p           |
+----------------------+---------------------+---------------------+--------+--------------------------------------------+-------------+
| Intercept            | -6.42378496687763   | -2.2725755951390028  | 0.06   | {"2.5%": -11.964027, "97.5%": -0.883543}  | coefficient |
| x1                   | 10.260063429838898  | 23.270944360826963  | 0.0    | {"2.5%": 9.395908, "97.5%": 11.124219}    | coefficient |
| x2                   | 0.35374498323846265 | 0.2949247320997519  | 0.81   | {"2.5%": -1.997160, "97.5%": 2.704650}    | coefficient |
| rsquared             | 0.9879675667384592  | NULL                | NULL   | NULL                                       | goodness    |
| adjusted_rsquared    | 0.9845297286637332  | NULL                | NULL   | NULL                                       | goodness    |
| aic                  | 59.331109494251805  | NULL                | NULL   | NULL                                       | goodness    |
| degree_of_freedom    | 7.0                 | NULL                | NULL   | NULL                                       | goodness    |
| standardErr_residual | 3.765777749448906   | NULL                | NULL   | NULL                                       | goodness    |
| deviance             | 99.26757440771128   | NULL                | NULL   | NULL                                       | goodness    |
+----------------------+---------------------+---------------------+--------+--------------------------------------------+-------------+

The R-Squared value of 0.988 indicates an excellent fit. The P-value for x2 (0.81) suggests it has low statistical significance in this dataset.

Step 5: View the prediction results

+-----+-------------------+---------------------+---------------------------+
| y   | prediction_result | prediction_score    | prediction_detail         |
+-----+-------------------+---------------------+---------------------------+
| 10  | NULL              | 12.808476727264404  | {"y": 12.8084767272644}   |
| 20  | NULL              | 15.43015013867922   | {"y": 15.43015013867922}  |
| 30  | NULL              | 33.48786177519568   | {"y": 33.48786177519568}  |
| 40  | NULL              | 36.565880804147355  | {"y": 36.56588080414735}  |
| 50  | NULL              | 52.674180388994415  | {"y": 52.67418038899442}  |
| 60  | NULL              | 62.82092871092313   | {"y": 62.82092871092313}  |
| 70  | NULL              | 71.34749583130122   | {"y": 71.34749583130122}  |
| 80  | NULL              | 75.75932310613193   | {"y": 75.75932310613193}  |
| 90  | NULL              | 87.1832221199846    | {"y": 87.18322211998461}  |
| 100 | NULL              | 101.92248485222113  | {"y": 101.9224848522211}  |
+-----+-------------------+---------------------+---------------------------+

The prediction_score column contains the model's predicted value for each row. Connect these results to downstream components for further analysis or reporting.

What's next

• To improve model accuracy, set regularizedType to l1 or l2 and tune regularizedLevel to reduce overfitting. Start with l2 if your features are correlated; use l1 if you want the model to select a sparse subset of features.

• To run predictions on new data, pass lm_test_input_model_out to the prediction component with a different input table.

• To compare model versions, enable enableFitGoodness and compare R-Squared and AIC values across runs.