SQL/MX Data Mining Guide

Preparing the Data
HP NonStop SQL/MX Data Mining Guide—523737-001
2-4
Transposition
SELECT attr, c1, COUNT(*) FROM customers
TRANSPOSE ('GENDER', gender),
('HOME', home),
('MARITAL_STATUS', marital_status)
AS (attr, c1)
GROUP BY attr, c1
ORDER BY attr, c1;
ATTR C1 (EXPR)
-------------- -------- --------------------
GENDER F 20
GENDER M 22
HOME Own 33
HOME Rent 9
MARITAL_STATUS Divorced 12
MARITAL_STATUS Married 9
MARITAL_STATUS Single 15
MARITAL_STATUS Widow 6
--- 8 row(s) selected.
Because this query produces counts for three different attributes, use the ATTR
column to distinguish from which attribute the values are drawn. The C1 column
contains the values for these character attributes.
Example of Computing Counts for Character and Numeric Discrete
Attributes
This query also shows the transpose clause and illustrates how profiling can be
achieved. The column C2 has been added to the statement because Number_Children
has numeric data type.
SELECT attr, c1, c2, COUNT(*) FROM customers
TRANSPOSE ('GENDER', gender, null),
('HOME', home, null),
('MARITAL_STATUS', marital_status, null),
('NUMBER_CHILDREN', null, number_children)
AS (attr, c1, c2)
GROUP BY attr, c1, c2
ORDER BY attr, c1, c2;
ATTR C1 C2 (EXPR)
--------------- -------- ------ --------------------
GENDER F ? 20
GENDER M ? 22
HOME Own ? 33
HOME Rent ? 9
MARITAL_STATUS Divorced ? 12
MARITAL_STATUS Married ? 9
MARITAL_STATUS Single ? 15
MARITAL_STATUS Widow ? 6
NUMBER_CHILDREN ? 0 25
NUMBER_CHILDREN ? 1 4