SQL/MX Data Mining Guide

Creating the Data Mining View
HP NonStop SQL/MX Data Mining Guide—523737-001
3-3
Pivoting the Data
NOT NULL
,balance_close_3 NUMERIC (9,2)
NO DEFAULT
NOT NULL
,cust_left CHAR(1)
NO DEFAULT
,PRIMARY KEY (account) );
Pivoting the Data
All the data in the Customer, Account History, and auxiliary tables must be collapsed to
a single row for each customer. Collapsing the data is accomplished by pivoting the
data. Data is purged from separate rows for each customer into different columns of a
single customer row. For example, the balance one month prior to account closure can
be placed in column BALANCE_CLOSE_1, the balance two months prior to account
closure in column BALANCE_CLOSE_2, and the balance three months prior to
account closure in column BALANCE_CLOSE_3.
To accomplish this pivoting operation, use the OFFSET sequence function to collect
data from various months and place the results in a single row.
Example Using OFFSET Sequence Function
This query populates the mining view:
INSERT INTO miningview
(SELECT t.account
,c.marital_status
,c.home
,c.income
,c.gender
,c.age
,c.number_children
,t.year_month
,t.close_month
,t.balance_close_1
,t.balance_close_2
,t.balance_close_3
,t.cust_left
FROM
(SELECT account
,year_month
,close_month
,CASE WHEN year_month = close_month THEN balance
END AS balance_close_1
,CASE WHEN year_month = close_month
AND account = OFFSET(account,1)
THEN OFFSET(balance, 1)
END AS balance_close_2
,CASE WHEN year_month = close_month
AND account = OFFSET(account,2)
THEN OFFSET(balance,2)