The attribute tables, iris_attribute_train and iris_attribute_test, are created from the raw train and test data, iris_train and iris_test. The recommended way to create the attribute tables is to use the Unpivot function:
CREATE DIMENSION TABLE singletree1 AS SELECT * FROM Unpivot ( ON iris_train Unpivot ('sepal_length', 'sepal_width', 'petal_length', 'petal_width') AttributeColumn ('attribute') ValueColumn ('value') InputTypes ('false') Accumulate ('id') ) ORDER BY attribute, id; CREATE DIMENSION TABLE iris_attribute_train AS (SELECT id AS pid, attribute, value AS attrvalue FROM singletree1);
CREATE DIMENSION TABLE singletree2 AS SELECT * FROM Unpivot ( ON iris_test Unpivot ('sepal_length', 'sepal_width', 'petal_length', 'petal_width') AttributeColumn ('attribute') ValueColumn ('value') InputTypes ('false') Accumulate ('id') ) ORDER BY attribute, id; CREATE DIMENSION TABLE iris_attribute_test AS (SELECT id AS pid, attribute, value AS attrvalue FROM singletree2);
This query returns the following table:
SELECT * FROM iris_attribute_train ORDER BY pid, attribute;
pid | attribute | attrvalue |
---|---|---|
1 | petal_length | 1.4 |
1 | petal_width | 0.2 |
1 | sepal_length | 5.1 |
1 | sepal_width | 3.5 |
2 | petal_length | 1.4 |
2 | petal_width | 0.2 |
2 | sepal_length | 4.9 |
2 | sepal_width | 3 |
3 | petal_length | 1.3 |
3 | petal_width | 0.2 |
3 | sepal_length | 4.7 |
3 | sepal_width | 3.2 |
... | ... | ... |
This query returns the following table:
SELECT * FROM iris_attribute_test ORDER BY pid, attribute;
pid | attribute | attrvalue |
---|---|---|
5 | petal_length | 1.4 |
5 | petal_width | 0.2 |
5 | sepal_length | 5 |
5 | sepal_width | 3.6 |
10 | petal_length | 1.5 |
10 | petal_width | 0.1 |
10 | sepal_length | 4.9 |
10 | sepal_width | 3.1 |
15 | petal_length | 1.2 |
15 | petal_width | 0.2 |
15 | sepal_length | 5.8 |
15 | sepal_width | 4 |
... | ... | ... |