入力
- 入力テーブル: 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 | 家の様式(応答変数) |
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 |
..。 | ..。 | ..。 | ..。 | ..。 | ..。 | ..。 | ..。 | ..。 | ..。 | ..。 | ..。 | ..。 | ..。 |
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 |