Simple Log Service:回帰分析関数

クエリ文

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

によるグループ

クエリおよび分析の結果

クエリおよび分析結果の係数パラメーターは、入力データに基づいてトレーニングされた線形回帰モデルの係数を示します。

データ予測では、linear_model関数の戻り値がlinear_model_predict関数の入力パラメーターとして使用されます。

{
  "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
}

クエリ文

* | 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

クエリおよび分析の結果

predicted_yパラメータの値は、独立変数に基づいて計算される。

クエリ文

  * | 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

によるグループ

クエリおよび分析の結果

クエリおよび分析結果の係数パラメーターは、入力データに基づいてトレーニングされた線形回帰モデルの係数を示します。

データ予測では、recent_regression_predict関数の入力パラメーターとしてrecent_regression_predict関数の戻り値が使用されます。

{
  "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
}

クエリ文

* | 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

クエリおよび分析の結果

クエリおよび分析結果の係数パラメーターは、入力データに基づいてトレーニングされた線形回帰モデルの係数を示します。

データ予測では、merge_recent_regression_predict関数の入力パラメーターとして、merge_recent_regent_regression_regrection関数の戻り値が使用されます。

{
  "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
}

クエリ文

* | 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

クエリおよび分析の結果

predicted_valueパラメーターの値は、予測値を示します。