SQL/MX Data Mining Guide

Creating the Data Mining View
HP NonStop SQL/MX Data Mining Guide—523737-001
3-4
Pivoting the Data
END AS balance_close_3
,cust_left
FROM acct_history a NATURAL JOIN close_temp m
SEQUENCE BY account, year_month) AS t, customers c
WHERE t.balance_close_1 IS NOT NULL AND
t.balance_close_2 IS NOT NULL AND
t.balance_close_3 IS NOT NULL AND
c.account = t.account);
Sequence functions are used in the preceding query to create a derived table with the
various balances for each customer. This derived table has one row per customer that
consists of a single copy of the relevant data.
Here are the contents of the Miningview table after the preceding row insertion:
This table continues the mining view table.
Account
Number
Marital
Status
Home Income Gender Age Number
Children
...
1000000 Married Own 175500.00 M 45 3
1234567 Single Own 65000.00 F 34 0
2300000 Divorced Own 137000.00 M 42 2
2400000 Widow Own 28000.00 F 65 0
2500000 Divorced Rent 32000.00 M 23 0
2900000 Divorced Rent 136000.00 F 50 0
3200000 Divorced Rent 138000.00 M 40 1
3900000 Divorced Own 75000.00 M 40 2
4098124 Divorced Own 44000.00 M 44 2
4300000 Married Own 300000.00 F 29 2
4400000 Single Own 300000.00 F 29 0
4500000 Married Own 300000.00 F 29 1
4600000 Single Own 300000.00 M 48 0
4700000 Widow Own 300000.00 M 68 0
Account
Number
... Year_
Month
Close
Month
Balance
Close_1
Balance
Close_2
Balance
Close_3
Cust
Left
1000000 1999-03-01 1999-03-01 5500.00 3500.00 1200.00 N
1234567 1999-12-01 1999-12-01 500.00 1200.00 2870.00 Y
2300000 1999-11-01 1999-11-01 .00 .00 .00 Y
2400000 1998-12-01 1998-12-01 .00 .00 .00 Y
2500000 1999-10-01 1999-10-01 .00 .00 .00 Y
2900000 1999-06-01 1999-06-01 2356.80 1134.00 9432.78 N
3200000 1999-05-01 1999-05-01 .00 .00 .00 Y