SQL/MX Data Mining Guide

Preparing the Data
HP NonStop SQL/MX Data Mining Guide—523737-001
2-7
Aligning the Data
is crucial for building models to predict events that occur at different times for each
customer.
Example of Aligning Data
This statement creates an SQL/MX table named Close_Temp that contains the
account number, the month the account is considered closed (if not closed, an arbitrary
month), and an indicator of whether or not the customer left:
SET SCHEMA mining.whse;
CREATE TABLE Close_Temp
( account NUMERIC (7) UNSIGNED
NO DEFAULT
NOT NULL
HEADING 'Account Number'
,close_month DATE
NO DEFAULT
NOT NULL
HEADING 'Close Month'
,cust_left CHAR(1)
NO DEFAULT
,PRIMARY KEY (account) );
In this query, the source data for the column named Close_Month is defined to be the
month the customer left—either by closing their account or by maintaining a zero
balance for three months. If the customer did not leave, the month is arbitrarily defined
to be a month in the middle of their account history.
INSERT INTO close_temp
(SELECT p.account,
CASE
WHEN p.close_month2 IS NOT NULL THEN p.close_month2
WHEN p.close_month1 IS NOT NULL THEN p.close_month1
ELSE p.open_month + ((DATE '1999-12-01' - p.open_month)/2)
- INTERVAL '16' DAY
END,
CASE
WHEN p.close_month2 IS NOT NULL THEN 'Y'
WHEN p.close_month1 IS NOT NULL THEN 'Y'
ELSE 'N'
END
FROM
(SELECT t.account, MAX(t.close_month1),
MAX(t.close_month2), MIN(t.year_month)
FROM
(SELECT m.account ,m.year_month,
CASE WHEN m.status = 'Closed'
AND OFFSET(m.status,1) = 'Open'
AND account = OFFSET(account,1)
THEN m.year_month
END,
CASE WHEN ROWS SINCE INCLUSIVE(balance <> 0) = 3.0
AND account = OFFSET(account,2)