Simple Log Service:Regression analysis functions

Query statement

 * |   select group_id,
        linear_model(
            array_agg(array[x1, x2]),
            array_agg(y)
        ) as model
    from log
    group by group_id

Query and analysis results

The coefficients parameter in the query and analysis results indicates the coefficient of the linear regression model that is trained based on the input data.

In data forecasting, the return value of the linear_model function is used as an input parameter of the linear_model_predict function.

{
  "coefficients": [
    0.8350068912618618,
    -0.741283054726383,
    19.17405856472653
  ],
  "isBuilt": true,
  "isBuildSuccessful": true,
  "sampleCount": 10,
  "xCount": 2,
  "wSum": 10.0,
  "ySumSquare": 3930.0,
  "ySum": 188.0,
  "xXSumProducts": [
    [
      385.0,
      367.0
    ],
    [
      367.0,
      475.0
    ]
  ],
  "xYSumProducts": [
    1104.0,
    1239.0
  ],
  "xSums": [
    55.0,
    67.0
  ],
  "xMeans": [
    5.5,
    6.7
  ],
  "xStdDevs": [
    2.8722813232690143,
    1.6155494421403511
  ],
  "xVariances": [
    8.25,
    2.6099999999999994
  ],
  "yMean": 18.8,
  "yStdDev": 6.289674077406551,
  "yVariance": 39.559999999999945,
  "xCorrelations": [
    [
      1.0,
      -0.03232540919176149
    ],
    [
      -0.03232540919176149,
      1.0
    ]
  ],
  "xYCorrelations": [
    0.3874743195572169,
    -0.202730375711539
  ],
  "regularized": true,
  "regularWeight": 1.0E-6
}

Query statement

* | with group_models as
(
    select group_id,
        linear_model(
            array_agg(array[x1, x2]),
            array_agg(y)
        ) as model
    from log
    group by group_id
)

select d.group_id,
    d.y,
    linear_model_predict(m.model, array[x1, x2]) as predicted_y
from group_models as m
    join log as d
    on m.group_id = d.group_id

Query and analysis results

The values of the predicted_y parameter are calculated based on the independent variable.

Query statement

  * | select group_id,
        recent_regression(
          y, array[x1, x2, 1.0], -- The dependent and independent variable samples.
          time_offset, -- The point in time of the sample.
          -4,          -- The start time of the time range for the current sample batch.
          0,           -- The end time of the time range for the current sample batch.
          1,           -- The time interval.
          0.999        -- The exponential decay base.
        ) as reg_model
    from log
    where time_offset >= -4 and time_offset <= 0
    group by group_id

Query and analysis results

The coefficients parameter in the query and analysis results indicates the coefficient of the linear regression model that is trained based on the input data.

In data forecasting, the return value of the recent_regression function is used as an input parameter of the recent_regression_predict function.

{
  "sampleCount": 5,
  "xCount": 3,
  "timeUnit": 1.0,
  "beginTimePeriod": -4.0,
  "endTimePeriod": 0.0,
  "unitDampingWeight": 0.999,
  "wSum": 4.990009995001,
  "ySumSquare": 1644.6974283836598,
  "ySum": 83.76770287757991,
  "xXSumSquares": [
    [
      54.830206884025,
      70.82220388003,
      14.960044976005001
    ],
    [
      70.82220388003,
      173.70327985603598,
      25.955043976006
    ],
    [
      14.960044976005001,
      25.955043976006,
      4.990009995001
    ]
  ],
  "xYSumProducts": [
    245.21187055562675,
    402.5070758759011,
    83.76770287757991
  ],
  "xSums": [
    14.960044976005001,
    25.955043976006,
    4.990009995001
  ],
  "xMeans": [
    2.997999000200801,
    5.201401199999158,
    1.0
  ],
  "xStdDevs": [
    1.4142126422148122,
    2.7848935986573244,
    0.0
  ],
  "xVariances": [
    1.9999973974002003,
    7.755632355842543,
    0.0
  ],
  "yMean": 16.78708118049834,
  "yStdDev": 6.913170639821401,
  "yVariance": 47.79192829528864,
  "xCorrelations": [
    [
      1.0,
      -0.35572473794248516,
      0.0
    ],
    [
      -0.35572473794248516,
      1.0,
      0.0
    ],
    [
      0.0,
      0.0,
      1.0
    ]
  ],
  "xYCorrelations": [
    -0.12142097167729436,
    -0.34560624507434407,
    0.0
  ],
  "coefficients": [
    -1.3675797278475395,
    -1.104969989478544,
    0.0,
    26.634476066516903
  ],
  "isBuilt": true,
  "isBuildSuccessful": true
}

Query statement

* | with model1 as
(
    select group_id,
        recent_regression(
          y, array[x1, x2, 1.0], -- The dependent and independent variable samples.
          time_offset, -- The point in time of the sample.
          -4,          -- The start time of the time range for the current sample batch.
          0,           -- The end time of the time range for the current sample batch.
          1,           -- The time interval.
          0.999        -- The exponential decay base.
        ) as reg_model
    from log
    where time_offset >= -4 and time_offset <= 0
    group by group_id
),

model2 as
(
    select group_id,
        recent_regression(y, array[x1, x2, 1.0], time_offset, -9, -5, 1, 0.999) as reg_model
    from log
    where time_offset >= -9 and time_offset <= -5
    group by group_id
)

select m1.group_id,
    merge_recent_regression(m1.reg_model, m2.reg_model) as reg_model
from model1 as m1
    join model2 as m2
        on m1.group_id = m2.group_id

Query and analysis results

The coefficients parameter in the query and analysis results indicates the coefficient of the linear regression model that is trained based on the input data.

In data forecasting, the return value of the merge_recent_regression function is used as an input parameter of the recent_regression_predict function.

{
  "sampleCount": 10,
  "xCount": 3,
  "timeUnit": 1.0,
  "beginTimePeriod": -9.0,
  "endTimePeriod": 0.0,
  "unitDampingWeight": 0.999,
  "wSum": 9.955119790251791,
  "ySumSquare": 4159.2626495224,
  "ySum": 193.9139516502596,
  "xXSumSquares": [
    [
      382.3684973894312,
      268.46629177582946,
      54.67098815430803
    ],
    [
      268.46629177582946,
      358.44803436913094,
      51.78255011892536
    ],
    [
      54.67098815430803,
      51.78255011892536,
      9.955119790251791
    ]
  ],
  "xYSumProducts": [
    1132.090921413269,
    919.4071924317548,
    193.9139516502596
  ],
  "xSums": [
    54.67098815430803,
    51.78255011892536,
    9.955119790251791
  ],
  "xMeans": [
    5.4917458861562585,
    5.201599901352432,
    1.0
  ],
  "xStdDevs": [
    2.8722740635191735,
    2.991614845817865,
    0.0
  ],
  "xVariances": [
    8.249958295964944,
    8.949759385717847,
    0.0
  ],
  "yMean": 19.478816502051856,
  "yStdDev": 6.1949232381571,
  "yVariance": 38.37707392665885,
  "xCorrelations": [
    [
      1.0,
      -0.1859947674356197,
      0.0
    ],
    [
      -0.1859947674356197,
      1.0,
      0.0
    ],
    [
      0.0,
      0.0,
      1.0
    ]
  ],
  "xYCorrelations": [
    0.3791693893070564,
    -0.4837793996174176,
    0.0
  ],
  "coefficients": [
    0.6460732812209116,
    -0.8864195347835274,
    0.0,
    20.541545982438304
  ],
  "isBuilt": true,
  "isBuildSuccessful": true
}

Query statement

* | with model1 as
(
    select group_id,
        recent_regression(
          y, array[x1, x2, 1.0], -- The dependent and independent variable samples.
          time_offset, -- The point in time of the sample.
          -4,          -- The start time of the time range for the current sample batch.
          0,           -- The end time of the time range for the current sample batch.
          1,           -- The time interval.
          0.999        -- The exponential decay base.
        ) as reg_model
    from log
    where time_offset >= -4 and time_offset <= 0
    group by group_id
),

model2 as
(
    select group_id,
        recent_regression(y, array[x1, x2, 1.0], time_offset, -9, -5, 1, 0.999) as reg_model
    from log
    where time_offset >= -9 and time_offset <= -5
    group by group_id
),

model as
(
    select m1.group_id,
        merge_recent_regression(m1.reg_model, m2.reg_model) as reg_model
    from model1 as m1
        join model2 as m2
            on m1.group_id = m2.group_id
),

new_data as
(
    select 'A' as group_id, 1 as obs_id, 3.0 as x1, 5.0 as x2, 1.0 as x3 union all
    select 'A' as group_id, 2 as obs_id, 7.0 as x1, 8.0 as x2, 1.0 as x3
)

select m.group_id,
    n.obs_id,
    recent_regression_predict(m.reg_model, array[n.x1, n.x2, 1.0]) as predicted_value
from model as m
    join new_data as n
        on m.group_id = n.group_id
order by m.group_id, n.obs_id

Query and analysis results

The value of the predicted_value parameter indicates the forecast value.