16.20 - DecisionForestPredictの例 - Teradata Vantage NewSQL Engine

Teradata Vantage™ - NewSQL Engineの分析関数

prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
プログラミング リファレンス
featnum
B035-1206-162K-JPN

入力

  • 入力テーブル: housing_test、14の変数の54の結果があります。
  • モデル: rft_model、ML Engine DecisionForest関数の例1で出力されます。
入力テーブル列の説明
説明
sn 販売番号(結果の固有識別子)
price 米国ドルでの販売価(数値)
lotsize 平方フィートでのロットサイズ(数値)
bedrooms 寝室数(数値)
bathrms バスルーム一式の数(数値)
stories 階数、地下を除く(数値)
driveway 家に私道があるかどうか—yesまたはno(カテゴリ別)
recroom 家にレクリエーション ルームがあるかどうか—yesまたはno(カテゴリ別)
fullbase 家に完全装備の地階があるかどうか—yesまたはno(カテゴリ別)
gashw 家の給湯がガスかどうか—yesまたはno(カテゴリ別)
airco 家が集中型空調を備えているかどうか—yesまたはno(カテゴリ別)
garagepl ガレージの数(数値)
prefarea 家の周囲が好ましい環境かどうか—yesまたはno(カテゴリ別)
homestyle 家の様式(応答変数)
housing_test
sn price lotsize bedrooms bathrms stories driveway recroom fullbase gashw airco garagepl prefarea homestyle
13 27000 1700 3 1 2 yes no no no no 0 no Classic
16 37900 3185 2 1 1 yes no no no yes 0 no Classic
25 42000 4960 2 1 1 yes no no no no 0 no Classic
38 67000 5170 3 1 4 yes no no no yes 0 no Eclectic
53 68000 9166 2 1 1 yes no yes no yes 2 no Eclectic
104 132000 3500 4 2 2 yes no no yes no 2 no bungalow
111 43000 5076 3 1 1 no no no no no 0 no Classic
117 93000 3760 3 1 2 yes no no yes no 2 no Eclectic
132 44500 3850 3 1 2 yes no no no no 0 no Classic
140 43000 3750 3 1 2 yes no no no no 0 no Classic
142 40000 2650 3 1 2 yes no yes no no 1 no Classic
157 60000 2953 3 1 2 yes no yes no yes 0 no Eclectic
..。 ..。 ..。 ..。 ..。 ..。 ..。 ..。 ..。 ..。 ..。 ..。 ..。 ..。
rtf_model
worker_ip task_index tree_num CAST(tree AS VARCHAR(50))
xx.xx.xx.xx 0 0 {"responseCounts_":{"Eclectic":148,"bungalow":30,"
xx.xx.xx.xx 0 1 {"responseCounts_":{"Eclectic":158,"bungalow":26,"
xx.xx.xx.xx 0 2 {"responseCounts_":{"Eclectic":120,"bungalow":38,"
xx.xx.xx.xx 0 3 {"responseCounts_":{"Eclectic":166,"bungalow":29,"
xx.xx.xx.xx 0 4 {"responseCounts_":{"Eclectic":138,"bungalow":32,"
xx.xx.xx.xx 0 5 {"responseCounts_":{"Eclectic":158,"bungalow":34,"
xx.xx.xx.xx 0 6 {"responseCounts_":{"Eclectic":168,"bungalow":32,"
xx.xx.xx.xx 0 7 {"responseCounts_":{"Eclectic":145,"bungalow":40,"
xx.xx.xx.xx 0 8 {"responseCounts_":{"Eclectic":150,"bungalow":34,"
xx.xx.xx.xx 0 9 {"responseCounts_":{"Eclectic":156,"bungalow":42,"
xx.xx.xx.xx 0 10 {"responseCounts_":{"Eclectic":148,"bungalow":18,"
xx.xx.xx.xx 0 11 {"responseCounts_":{"Eclectic":147,"bungalow":20,"
xx.xx.xx.xx 0 12 {"responseCounts_":{"Eclectic":150,"bungalow":31,"
xx.xx.xx.xx 0 13 {"responseCounts_":{"Eclectic":135,"bungalow":32,"
xx.xx.xx.xx 0 14 {"responseCounts_":{"Eclectic":139,"bungalow":24,"
xx.xx.xx.xx 0 15 {"responseCounts_":{"Eclectic":146,"bungalow":27,"
xx.xx.xx.xx 0 16 {"responseCounts_":{"Eclectic":152,"bungalow":23,"
xx.xx.xx.xx 0 17 {"responseCounts_":{"Eclectic":135,"bungalow":23,"
xx.xx.xx.xx 0 18 {"responseCounts_":{"Eclectic":148,"bungalow":29,"
xx.xx.xx.xx 0 19 {"responseCounts_":{"Eclectic":166,"bungalow":33,"
xx.xx.xx.xx 0 20 {"responseCounts_":{"Eclectic":142,"bungalow":28,"
xx.xx.xx.xx 0 21 {"responseCounts_":{"Eclectic":172,"bungalow":27,"
xx.xx.xx.xx 0 22 {"responseCounts_":{"Eclectic":147,"bungalow":37,"
xx.xx.xx.xx 0 23 {"responseCounts_":{"Eclectic":158,"bungalow":31,"
xx.xx.xx.xx 0 24 {"responseCounts_":{"Eclectic":158,"bungalow":33,"
xx.xx.xx.xx 1 0 {"responseCounts_":{"Eclectic":140,"bungalow":44,"
xx.xx.xx.xx 1 1 {"responseCounts_":{"Eclectic":161,"bungalow":28,"
xx.xx.xx.xx 1 2 {"responseCounts_":{"Eclectic":131,"bungalow":25,"
xx.xx.xx.xx 1 3 {"responseCounts_":{"Eclectic":167,"bungalow":28,"
xx.xx.xx.xx 1 4 {"responseCounts_":{"Eclectic":150,"bungalow":19,"
xx.xx.xx.xx 1 5 {"responseCounts_":{"Eclectic":158,"bungalow":24,"
xx.xx.xx.xx 1 6 {"responseCounts_":{"Eclectic":177,"bungalow":32,"
xx.xx.xx.xx 1 7 {"responseCounts_":{"Eclectic":156,"bungalow":24,"
xx.xx.xx.xx 1 8 {"responseCounts_":{"Eclectic":156,"bungalow":37,"
xx.xx.xx.xx 1 9 {"responseCounts_":{"Eclectic":165,"bungalow":24,"
xx.xx.xx.xx 1 10 {"responseCounts_":{"Eclectic":135,"bungalow":29,"
xx.xx.xx.xx 1 11 {"responseCounts_":{"Eclectic":140,"bungalow":20,"
xx.xx.xx.xx 1 12 {"responseCounts_":{"Eclectic":156,"bungalow":24,"
xx.xx.xx.xx 1 13 {"responseCounts_":{"Eclectic":147,"bungalow":34,"
xx.xx.xx.xx 1 14 {"responseCounts_":{"Eclectic":151,"bungalow":22,"
xx.xx.xx.xx 1 15 {"responseCounts_":{"Eclectic":161,"bungalow":18,"
xx.xx.xx.xx 1 16 {"responseCounts_":{"Eclectic":156,"bungalow":19,"
xx.xx.xx.xx 1 17 {"responseCounts_":{"Eclectic":126,"bungalow":29,"
xx.xx.xx.xx 1 18 {"responseCounts_":{"Eclectic":148,"bungalow":26,"
xx.xx.xx.xx 1 19 {"responseCounts_":{"Eclectic":177,"bungalow":21,"
xx.xx.xx.xx 1 20 {"responseCounts_":{"Eclectic":137,"bungalow":31,"
xx.xx.xx.xx 1 21 {"responseCounts_":{"Eclectic":171,"bungalow":28,"
xx.xx.xx.xx 1 22 {"responseCounts_":{"Eclectic":146,"bungalow":30,"
xx.xx.xx.xx 1 23 {"responseCounts_":{"Eclectic":149,"bungalow":21,"
xx.xx.xx.xx 1 24 {"responseCounts_":{"Eclectic":158,"bungalow":18,"

SQL呼び出し

Accumulate引数を使用してhomestyle変数を渡し、結果ごとに実際の応答と予想される応答を容易に比較します。

CREATE MULTISET TABLE rf_housing_predict AS (
  SELECT * FROM DecisionForestPredict (
    ON housing_test PARTITION BY ANY
    ON rft_model AS Model DIMENSION
    USING
    NumericInputs ('price ','lotsize ','bedrooms ','bathrms ',
                   'stories ','garagepl')
    CategoricalInputs ('driveway ','recroom ','fullbase ','gashw ',
                       'airco ','prefarea')
    IdColumn ('sn')
    Accumulate ('homestyle')
    Detailed ('false')
  ) AS dt
) WITH DATA;

出力

このクエリーは、以下のテーブルを返します。

SELECT * FROM rf_housing_predict ORDER BY 2;
homestyle sn prediction confidence_lower confidence_upper
Classic 13 Classic 0.6 0.6
Classic 16 Classic 0.56 0.56
Classic 25 Classic 0.54 0.54
Eclectic 38 Eclectic 0.7 0.7
Eclectic 53 Eclectic 0.54 0.54
bungalow 104 bungalow 0.36 0.36
Classic 111 Classic 0.54 0.54
Eclectic 117 Eclectic 0.46 0.46
Classic 132 Classic 0.54 0.54
Classic 140 Classic 0.52 0.52
Classic 142 Eclectic 0.5 0.5
Eclectic 157 Eclectic 0.64 0.64
Eclectic 161 Eclectic 0.74 0.74
bungalow 162 Eclectic 0.46 0.46
Eclectic 176 Eclectic 0.48 0.48
Eclectic 177 Eclectic 0.56 0.56
Classic 195 Classic 0.76 0.76
Classic 198 Classic 0.48 0.48
Eclectic 224 Eclectic 0.56 0.56
Classic 234 Classic 0.64 0.64
Classic 237 Classic 0.48 0.48
Classic 239 Classic 0.52 0.52
Classic 249 Classic 0.7 0.7
Classic 251 Classic 0.6 0.6
Eclectic 254 Eclectic 0.66 0.66
Eclectic 255 Eclectic 0.6 0.6
Classic 260 Eclectic 0.5 0.5
Eclectic 274 Eclectic 0.66 0.66
Classic 294 Classic 0.62 0.62
Eclectic 301 Classic 0.56 0.56
Eclectic 306 Eclectic 0.7 0.7
Eclectic 317 Eclectic 0.5 0.5
bungalow 329 Eclectic 0.52 0.52
bungalow 339 bungalow 0.56 0.56
Eclectic 340 Eclectic 0.54 0.54
Eclectic 353 Eclectic 0.44 0.44
Eclectic 355 Classic 0.4 0.4
Eclectic 364 Eclectic 0.54 0.54
bungalow 367 bungalow 0.52 0.52
bungalow 377 Eclectic 0.46 0.46
Eclectic 401 Eclectic 0.56 0.56
Eclectic 403 Eclectic 0.56 0.56
Eclectic 408 Eclectic 0.56 0.56
Eclectic 411 Eclectic 0.54 0.54
Eclectic 440 Eclectic 0.66 0.66
Eclectic 441 Classic 0.5 0.5
Eclectic 443 Classic 0.52 0.52
Classic 459 Classic 0.74 0.74
Classic 463 Eclectic 0.56 0.56
Eclectic 469 Eclectic 0.62 0.62
Eclectic 472 Eclectic 0.54 0.54
bungalow 527 Eclectic 0.52 0.52
bungalow 530 Eclectic 0.58 0.58
Eclectic 540 Eclectic 0.42 0.42

予測精度

このクエリーは予測精度を返します。

SELECT (SELECT count(sn) FROM rf_housing_predict
  WHERE homestyle = prediction) / (SELECT count(sn)
    FROM rf_housing_predict) AS PA;
pa
0.77777777777777777778