Input
Input tables are from DecisionTree Example: Create Model:
- AttributeTable: iris_attribute_test
- ModelTable: iris_attribute_output
SQL Call
CREATE MULTISET TABLE singletree_predict AS ( SELECT * FROM DecisionTreePredict_MLE ( ON iris_attribute_test AS AttributeTable PARTITION BY pid ON iris_attribute_output as Model DIMENSION USING AttrTableGroupbyColumns ('attribute') AttrTablePidColumns ('pid') AttrTableValColumn ('attrvalue') ) AS dt ) WITH DATA;
Output
SELECT * FROM singletree_predict ORDER BY pid;
pid pred_label --- ---------- 5 1 10 1 15 1 20 1 25 1 30 1 35 1 40 1 45 1 50 1 55 2 60 2 65 2 70 2 75 2 80 2 85 2 90 2 95 2 100 2 105 3 110 3 115 3 120 2 125 3 130 2 135 2 140 3 145 3 150 3
Prediction Accuracy
The following SQL code calculates and displays the prediction accuracy.
DROP TABLE st_predict_accuracy;
CREATE MULTISET TABLE st_predict_accuracy AS ( SELECT pid, CAST(pred_label AS INTEGER) AS pred_label, species FROM singletree_predict, iris_test WHERE id = pid ) WITH DATA;
SELECT ( SELECT COUNT(pid) FROM st_predict_accuracy WHERE pred_label = species)/( 1.00 * (SELECT COUNT(pid) FROM st_predict_accuracy ) ) AS prediction_accuracy;
prediction_accuracy ------------------- 0.90
Download a zip file of all examples and a SQL script file that creates their input tables from the attachment in the left sidebar.