17.10 - DecisionForestPredictの例: 列名の指定 - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Advanced SQL Engine分析関数

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
2021年7月
Content Type
プログラミング リファレンス
Publication ID
B035-1206-171K-JPN
Language
日本語 (日本)

入力

  • 入力テーブル: housing_test、14の変数の54の結果があります。
  • モデル: rft_model、Teradata Vantage™ Machine Learning Engine分析関数リファレンス、B700-4003の「DecisionForestの例: TreeType ('classification')およびOutOfBag ('false')」によって出力される
入力テーブル列の説明
説明
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                8.88888888888889E-001   8.88888888888889E-001
 classic                                  16  classic                8.88888888888889E-001   8.88888888888889E-001
 classic                                  25  classic                1.00000000000000E 000   1.00000000000000E 000
 eclectic                                 38  eclectic               7.77777777777778E-001   7.77777777777778E-001
 eclectic                                 53  eclectic               7.77777777777778E-001   7.77777777777778E-001
 bungalow                                104  eclectic               7.77777777777778E-001   7.77777777777778E-001
  classic                                 111  classic                1.00000000000000E 000   1.00000000000000E 000
  eclectic                                117  eclectic               1.00000000000000E 000   1.00000000000000E 000
  classic                                 132  classic                8.88888888888889E-001   8.88888888888889E-001
  classic                                 140  classic                8.88888888888889E-001   8.88888888888889E-001
  classic                                 142  classic                8.88888888888889E-001   8.88888888888889E-001
  eclectic                                157  eclectic               1.00000000000000E 000   1.00000000000000E 000
  eclectic                                161  eclectic               1.00000000000000E 000   1.00000000000000E 000
  bungalow                                162  bungalow               5.55555555555556E-001   5.55555555555556E-001
  eclectic                                176  eclectic               1.00000000000000E 000   1.00000000000000E 000
  eclectic                                177  eclectic               1.00000000000000E 000   1.00000000000000E 000
  classic                                 195  classic                1.00000000000000E 000   1.00000000000000E 000
  classic                                 198  classic                8.88888888888889E-001   8.88888888888889E-001
  eclectic                                224  eclectic               1.00000000000000E 000   1.00000000000000E 000
  classic                                 234  classic                1.00000000000000E 000   1.00000000000000E 000
  classic                                 237  classic                8.88888888888889E-001   8.88888888888889E-001
  classic                                 239  classic                1.00000000000000E 000   1.00000000000000E 000
  classic                                 249  classic                1.00000000000000E 000   1.00000000000000E 000
  classic                                 251  classic                8.88888888888889E-001   8.88888888888889E-001
  eclectic                                254  eclectic               8.88888888888889E-001   8.88888888888889E-001
  eclectic                                255  eclectic               1.00000000000000E 000   1.00000000000000E 000
  classic                                 260  classic                1.00000000000000E 000   1.00000000000000E 000
  eclectic                                274  eclectic               8.88888888888889E-001   8.88888888888889E-001
  classic                                 294  classic                1.00000000000000E 000   1.00000000000000E 000
  eclectic                                301  eclectic               1.00000000000000E 000   1.00000000000000E 000
  eclectic                                306  eclectic               1.00000000000000E 000   1.00000000000000E 000
  eclectic                                317  eclectic               7.77777777777778E-001   7.77777777777778E-001
  bungalow                                329  bungalow               8.88888888888889E-001   8.88888888888889E-001
  bungalow                                339  bungalow               5.55555555555556E-001   5.55555555555556E-001
  eclectic                                340  eclectic               1.00000000000000E 000   1.00000000000000E 000
  eclectic                                353  eclectic               1.00000000000000E 000   1.00000000000000E 000
  eclectic                                355  eclectic               8.88888888888889E-001   8.88888888888889E-001
  eclectic                                364  eclectic               1.00000000000000E 000   1.00000000000000E 000
  bungalow                                367  bungalow               7.77777777777778E-001   7.77777777777778E-001
  bungalow                                377  bungalow               7.77777777777778E-001   7.77777777777778E-001
  eclectic                                401  eclectic               8.88888888888889E-001   8.88888888888889E-001
  eclectic                                403  eclectic               1.00000000000000E 000   1.00000000000000E 000
  eclectic                                408  eclectic               1.00000000000000E 000   1.00000000000000E 000
  eclectic                                411  eclectic               1.00000000000000E 000   1.00000000000000E 000
  eclectic                                440  eclectic               8.88888888888889E-001   8.88888888888889E-001
  eclectic                                441  eclectic               1.00000000000000E 000   1.00000000000000E 000
  eclectic                                443  eclectic               1.00000000000000E 000   1.00000000000000E 000
  classic                                 459  classic                8.88888888888889E-001   8.88888888888889E-001
  classic                                 463  classic                7.77777777777778E-001   7.77777777777778E-001
  eclectic                                469  eclectic               7.77777777777778E-001   7.77777777777778E-001
  eclectic                                472  eclectic               1.00000000000000E 000   1.00000000000000E 000
  bungalow                                527  bungalow               7.77777777777778E-001   7.77777777777778E-001
  bungalow                                530  eclectic               6.66666666666667E-001   6.66666666666667E-001
  eclectic                                540  eclectic               8.88888888888889E-001   8.88888888888889E-001

予測精度

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

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