Association models can be built on native transactional data or on nested data. Example 3-8 shows how to define a nested column for market basket analysis.
The following SQL statement transforms this data to a column of type DM_NESTED_NUMERICALS in a view called SALES_TRANS_CUST_NESTED. This view can be used as a case table for mining.
CREATE VIEW sales_trans_cust_nested AS
SELECT trans_id,
CAST(COLLECT(DM_NESTED_NUMERICAL(
prod_name, 1))
AS DM_NESTED_NUMERICALS) custprods
FROM sales_trans_cust
GROUP BY trans_id;
This query returns two rows from the transformed data.
SELECT * FROM sales_trans_cust_nested
WHERE trans_id < 101000
AND trans_id > 100997;
TRANS_ID CUSTPRODS(ATTRIBUTE_NAME, VALUE)
------- ------------------------------------------------
100998 DM_NESTED_NUMERICALS
(DM_NESTED_NUMERICAL('O/S Documentation Set - English', 1)
100999 DM_NESTED_NUMERICALS
(DM_NESTED_NUMERICAL('CD-RW, High Speed Pack of 5', 1),
DM_NESTED_NUMERICAL('External 8X CD-ROM', 1),
DM_NESTED_NUMERICAL('SIMM- 16MB PCMCIAII card', 1))
See Also:
Example 3-8 Convert to a Nested Column
The view SALES_TRANS_CUST provides a list of transaction IDs to identify each market basket and a list of the products in each basket.
describe sales_trans_cust Name Null? Type ----------------------------------------------------- -------- ---------------- TRANS_ID NOT NULL NUMBER PROD_NAME NOT NULL VARCHAR2(50) QUANTITY NUMBER