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

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

Teradata Vantage™ - Advanced SQL Engine分析関数

Product

Advanced SQL Engine

Teradata Database

Release Number

17.10

Published

2021年7月

Language

日本語

Last Update

2021-09-23

dita:mapPath

ja-JP/wnd1589838592459.ditamap

dita:ditavalPath

ja-JP/ayr1485454803741.ditaval

dita:id

B035-1206

Product Category

Software

Teradata Vantage

入力

入力テーブル: 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