The data mining functions support a USING
clause that specifies which attributes to use for scoring. You can specify some or all of the attributes in the selection and you can specify expressions. The following examples all use the PREDICTION
function to find the customers who are likely to use an affinity card, but each example uses a different set of predictors.
The query in Example 6-1 uses all the predictors.
The query in Example 6-2 uses only gender, marital status, occupation, and income as predictors.
The query in Example 6-3 uses three attributes and an expression as predictors. The prediction is based on gender, marital status, occupation, and the assumption that all customers are in the highest income bracket.
SELECT cust_gender, COUNT(*) AS cnt, ROUND(AVG(age)) AS avg_age FROM mining_data_apply_v WHERE PREDICTION(dt_sh_clas_sample USING cust_gender, cust_marital_status, occupation, 'L: 300,000 and above' AS cust_income_level) = 1 GROUP BY cust_gender ORDER BY cust_gender; C CNT AVG_AGE - ---------- ---------- F 30 38 M 186 43
Example 6-1 Using All Predictors
SELECT cust_gender, COUNT(*) AS cnt, ROUND(AVG(age)) AS avg_age FROM mining_data_apply_v WHERE PREDICTION(dt_sh_clas_sample USING *) = 1 GROUP BY cust_gender ORDER BY cust_gender; C CNT AVG_AGE - ---------- ---------- F 25 38 M 213 43
Example 6-2 Using Some Predictors
SELECT cust_gender, COUNT(*) AS cnt, ROUND(AVG(age)) AS avg_age FROM mining_data_apply_v WHERE PREDICTION(dt_sh_clas_sample USING cust_gender,cust_marital_status, occupation, cust_income_level) = 1 GROUP BY cust_gender ORDER BY cust_gender; C CNT AVG_AGE - ---------- ---------- F 30 38 M 186 43