SQL/MX Data Mining Guide

Creating the Data Mining View
HP NonStop SQL/MX Data Mining Guide—523737-001
3-2
Creating the Single Table
Creating the Single Table
After computing derived attributes and storing these attributes in auxiliary tables, you
create the mining view by combining all the information into a single table with one row
for each entity. Continuing with the credit card example, the mining view contains the
information in the Customers table along with the auxiliary customer data. In addition,
information in the Account History and related tables is also used.
Typically, after the mining view is computed and inserted into a single database table,
the data is extracted and loaded into a mining tool for the model building step. The
mining data would be extracted via ODBC/MX or the Genus Mining Integrator for
NonStop SQL.
Example of Creating the View
The derived attributes consisting of the three balances for the three months prior to a
customer leaving are specified in the following SQL/MX CREATE TABLE statement.
This view aligns the data around the month of a particular event—account attrition.
SET SCHEMA mining.whse;
CREATE TABLE mineview
( account NUMERIC (7) UNSIGNED
NO DEFAULT
NOT NULL
HEADING 'Account Number'
,marital_status CHARACTER (8)
DEFAULT NULL
HEADING 'Marital Status'
,home CHARACTER (4)
DEFAULT NULL
HEADING 'Home'
,income NUMERIC (8, 2) UNSIGNED
DEFAULT NULL
HEADING 'Income'
,gender CHAR(1)
DEFAULT NULL
,age NUMERIC (3)
DEFAULT NULL
HEADING 'Age'
,number_children NUMERIC (2)
DEFAULT NULL
HEADING 'Number of Children'
,year_month DATE
NO DEFAULT
NOT NULL
,close_month DATE
NO DEFAULT
NOT NULL
,balance_close_1 NUMERIC (9,2)
NO DEFAULT
NOT NULL
,balance_close_2 NUMERIC (9,2)
NO DEFAULT