Tuesday, September 04, 2012

3 most useful little SQL functions for data manipulation

I have found the following three little SQL functions useful for data manipulation. The are almost always used in my data mining projects in these years. They are not fancy, but very convenient.

1. Mod function.
Mod(m,n) in Oracle returns the remainder of m divided by n. For example, mod(13,7)=6 because 6 is the remainder of 13 divided by 7. If m and n are positive integers, mod(m,n) is always within the range of 0 and n-1. We can use Mod function for random sampling. For SQL Server, Mod function is operator m%n.

select record_id from tbl_test2 order by record_id;
 RECORD_ID
----------
         1
         2
         3
         4
         5
         6
         7
The following query picks every other record id.
select record_id from tbl_test2 where mod(record_id,2)=1 order by record_id;
 RECORD_ID
----------
         1
         3
         5
         7
The following query picks every third record id.
select record_id from tbl_test2 where mod(record_id,3)=1 order by record_id;
 RECORD_ID
----------
         1
         4
         7
We can combine mod and dbms_random.random functions to perform random sample. See previous post "Random Sampling using SQL".
2. Nvl function
Oracle nvl(x,y) function replaces null value in x with y. The following query replaces null value with 'X';

select var, nvl(a.var, 'X')  from tbl_test3;
VAR                              NVL(A.VAR,'X')
-------------------------------- --------------------------------
A                                A
B                                B
                                 X
C                                C
3. Decode function
Decode function maps values of a variable to other values.  In a number of projects, I used decode function to convert the categories into weights (derived from logistic regression models). The following example assigns a weight of -.420916 to A, -.028324 to B and so on. A default weight of 0 is assigned to all other categories that are not listed.
decode(var,
'A ',-.420916,
'B',-.028324,
'C',-.186108,
'D',-1.165688,
'E',-.016754,
'F',-.153281,
'G',-.010159,
'H',-1.136179,
0)

Sunday, September 02, 2012

Dealing with missing values in data mining

We have found that when analyzing large amount of data such as financial transactions, medical claims,  cell phone calls, and credit information, missing values are common. Simply ignoring data points with missing values is recommended in most cases. There are a number of ways to handle missing values for categorical and continuous variables.

The following examples illustrate how to replace missing values in categorical variables.
Original data:
Record_ID   Variable_X
 1                     A
 2                     B
 3                     A
........................
95                  Missing

Method 1. Replace missing value with a special value (e.g. X)
Record_ID   Variable_X
 1                     A
 2                     B
 3                     A
........................
95                   X
In Oracle, this can be done easily using function nvl(). The following is the SQL scripts to perform the replacement.
select nvl(Variable_X,'X')  as Variable_X, ..... from original_table;
Method 2. Replace missing value with the most frequent value.
Record_ID   Variable_X
 1                     A
 2                     B
 3                     A
........................
95                   A
The following is the SQL scripts to perform the replacement.
select nvl(Variable_X,'A')  as Variable_X, ..... from original_table;

The following examples illustrate how to replace missing values in continuous variables.
Original data:
Record_ID   Variable_X
 1                     1.3
 2                     0.5
 3                     2.9
........................
95                  Missing

Method 3. Replace missing value with mean or median value.
Record_ID   Variable_X
 1                     1.3
 2                     0.5
 3                     2.9
........................
95                  1.56
The following is the SQL scripts to perform the replacement.
select  nvl(Variable_X, 1.56) as Variable_X, ..... from original_table;

Method 4. We first convert continuous variable into categorical variable. This is also called binning. I will discuss binning in another post. We define missing as a special category.
The following is the SQL scripts to perform the above logic.
case when Variable_X <=1.2 then 'A'
        when Variable_X <=1.8 then 'B'
        when Variable_X  > 1.8 then 'C'
       else 'Z' end  Variable_X_BIN

New Variable Variable_X_BIN.
Record_ID   Variable_X  --> Variable_X_BIN
 1                     1.3           ---> B
 2                     0.5           ---> A
 3                     2.9           --->B
........................
95                  missing      ---->Z

There are more sophisticated methods to impute missing values using multiple variable models. But they are rarely used in real world applications.

Saturday, September 01, 2012

In-Database Data Mining

In previous post "Data Mining Components", we talk about four components or layers in a using data mining to solve business problems, e.g., fraud prevention, new customer life time value prediction, online ads click through rate, etc. Two abstract components are business problems and data mining algorithms. By abstract, we mean their existence is largely mental. They do not have physical forms and can not be purchased. Two physical components are data mining software and data management tools. Data mining software are the implementation of data mining algorithms. Data management tools could be relational databases or plain files.   It is important to realize that items within each layer are sometimes exchangeable. We can select the combination of data mining models, tools and databases that suit our needs.  In these years, we have found that performing data mining within database has its unique advantages.  In this in-database data mining scenario,  two physical components, i.e., data mining algorithm implementation and data management, are collapsed into a single component, Oracle database. For example, we have used Oracle Data Mining to build debit card and check deposit fraud  models. The in-database solution provides substantially increased security, productivity, manageability and scalability. This should not be a surprise because a database is designed to handle large data using SQL as a standard language in an enterprise environment. If we consider data mining as just more sophisticated SQL queries, nothing more, nothing less, a lot of difficulties in data mining practices will disappear naturally. 
       In-Database Data Mining 

Data Mining Components

We identify four components or layers in a data mining engagement as shown in the figure below. Two abstract layers, business problem and data mining algorithms, are on the top. And two physical layers, data mining tools and data management, are at the bottom.

Business problems that we want to solve is one of the most important abstract layers. It could be predicting fraud (bank card, check, medical claims), new customer life time value at point of sales, online ads click rate, credit worthiness, customer segmentation. We can address the above business problems using various predictive models such as logistic regressions, neural nets, support vector machines, K-means clustering.

Data mining tools layer contains commercial or open source software such as SAS, Splus, R, Weca, SPSS, Statsoft, Oracle Data Mining. Common data mining algorithms can be found in almost all of the software mentioned above. Data management/storage layer are relational databases such as Oracle, SQL server, MySQL, or simply files such as SAS files or text files.

We can predict if a new customer will pay his car loan using logistic regression model implemented in SAS and store the data in Oracle. Or we can solve the same problem using decision tree models implemented in R and store data in SQL server. It is important to realize that items within each layer are sometimes exchangeable. We can solve any business problems with varieties of data mining algorithms implemented by commercial or open source tools and store data in any databases.  Thus it is a misconcept that neural nets are best in predicting credit card fraud. An experienced data miner can build a decision tree model to predict credit card fraud that performs equally well as a neural net does. We can select the combination of data mining models, tools and databases that suit our needs.
Four Components or Layers in Data Mining


Friday, August 31, 2012

Random Sampling in Oracle using Sample Clause

In an earlier post about "Random Sampling using SQL", we use functions mode and dbms_random.random to randomly sample 30% of records.  This can also be done easily with oracle sample clause as the following:
select * from TBL_TEST sample(30);
Sample clause is also a great way to test the performance of a query before we run it on large tables.

Thursday, August 30, 2012

Select top percent in Oracle

Oracle analytic function cume_dist() calculates the cumulative percentage for a variable.

The following query selects the top 5% records based on variable num.
with tbl as
(
select a.*, cume_dist() over(order by num desc) cumulative_pcnt from tbl_test a
)
select * from tbl where cumulative_pcnt <=0 .05;

The following query selects the bottom 5% records based on variable num.
with tbl as
(
select a.*, cume_dist() over(order by num ) cumulative_pcnt from tbl_test a
)
select * from tbl where cumulative_pcnt <=0 .05;

The following query selects the top 5% records based on variable num by group.
with tbl as
(
select a.*, cume_dist() over(partition by grp order by num desc) cumulative_pcnt from tbl_test a
)
select * from tbl where cumulative_pcnt <=0 .05;


Wednesday, August 29, 2012

Random Sampling using SQL

The following SQL scripts create a view by taking 30% random samples from a table.

Step 1 . Create random numbers within range of 0 and 99 (100 bins).
create table tbl_app_id_w_rnd as select app_id, 
mod(abs(dbms_random.random()),100) rnd from CELL_PHONE_SERVICE_APPS;

Step 2. Create a view that contains 30% of the records.
create view v_data_testing
as select a.* from CELL_PHONE_SERVICE_APPS a,
tbl_app_id_w_rnd b where a.app_id=b.app_id and  rnd < 30;

Please see a newer post. dbms_random.value is a better function than dbms_random.random.

Build K-Means Clustering Models Using PL/SQL

As mentioned in an earlier post,  we can build predictive models within Oracle using PL/SQL. One of such models is K-Means clustering which does not require a target variable. K-Means clustering iteratively

Step 1 . Define K-Means clustering parameters
  CREATE TABLE km_settings (
   setting_name  VARCHAR2(30),   setting_value VARCHAR2(30));
BEGIN      
   INSERT INTO km_settings (setting_name, setting_value) VALUES
   (dbms_data_mining.kmns_distance, dbms_data_mining.kmns_euclidean);
   INSERT INTO km_settings (setting_name, setting_value) VALUES
   (dbms_data_mining.prep_auto, dbms_data_mining.prep_auto_on);
  INSERT INTO km_settings (setting_name, setting_value) VALUES  (dbms_data_mining.clus_num_clusters, '7');
END;
/
Step 2. Build a K-Means Clustering model
define m_name='km_0425a'
define input_tbl='v_data_training_4_cls'
define rec_id='APP_ID'
BEGIN
  DBMS_DATA_MINING.CREATE_MODEL(
    model_name          => '&m_name',
    mining_function     => dbms_data_mining.clustering,
    data_table_name     => '&input_tbl',
    case_id_column_name => '&rec_id',
    settings_table_name => 'km_settings');
END;
/
Again once the model is built, it is a mining object. We can use Cluster_id() function to calculate cluster members for new data.
select app_id, cluster_id(km_0425a using *) cls from CELL_PHONE_SERVICE_APPS_NEW

Tuesday, August 28, 2012

Random number generators


There are many random generators that can be used for random sampling  such as rand() in SAS, dbms_random.rand() in Oracle, rand() in Excel spreadsheet. I have helped implement a Winchmann- Hill random generator using PL/SQL. Here is a link to WH algorithm (http://www2.imperial.ac.uk/~hohs/S9/2007-2008/wichmannhill.pdf).

The reason to implement Winchmann-Hill random generator using PL/SQL was that we wanted to replicate the sequence of random numbers precisely given the same random seed. We did not want to use SAS or Oracle random functions because we do not know how the details of their algorithms. We want to build WH random generators that work consistently across platforms, i.e., SAS and Oracle database. This is easier said than done. One of the lessons we learnt was that SAS integer can only have 17 digits while Oracle integer support 38 digits. We have to make sure that the random seed is not too large.

Monday, August 27, 2012

Predictive model scoring in Oracle


As described in an earlier post, "Build Predictive Models Using PL/SQL", we can build models within Oracle using function 
DBMS_DATA_MINING.CREATE_MODEL. The models are called mining objects.

The ultimate purpose of building a predictive mode is to apply it to new data. This can be done using SQL function prediction_probability(). 
We call this function in our SQL scripts. It will return the probability of being fraud, delinquency, clicking an online ads, or whatever the model is predicting.

select ACCOUNT_NUM, Transaction_ID, prediction_probability(fraud_model,  1 using *) score from tbl_transaction_data;

This is probably the most efficient way to deploy a model into production. (Model deployment is traditionally a major headache.) Again, data never leave the database and no standalone analytics server is needed.

Build Predictive Models Using PL/SQL


Many data analytics professionals/data miners are not aware that predictive modeling can be done within Oracle database. I probably have built more than one hundred predictive models this way in recent years. One of the benefits is that data never leave the database. To do this, we take two steps.

1. Define model parameters. We create a table to store parameters including model types, configurations, etc. This is a one time effort. The parameter table can be reused.
CREATE TABLE glm_settings (
  setting_name  VARCHAR2(30),
  setting_value VARCHAR2(30));
-- The default classification algorithm is Naive Bayes. So override
-- this choice to GLM logistic regression using a settings table.
--
BEGIN
-- Populate settings table
  INSERT INTO glm_settings (setting_name, setting_value) VALUES
    (dbms_data_mining.algo_name, dbms_data_mining.algo_generalized_linear_model);
 -- INSERT INTO  glm_settings (setting_name, setting_value) VALUES
 -- (dbms_data_mining.prep_auto, dbms_data_mining.prep_auto_on);
  -- Examples of other possible overrides are:
  --(dbms_data_mining.glms_ridge_regression, dbms_data_mining.glms_ridge_reg_enable);
  COMMIT;
END;
/
2. Run DBMS_DATA_MINING.CREATE_MODEL function to build a model. A model is called a mining object which is simply a database object type like table, view, index.
-- Build a new GLM Model
BEGIN
  DBMS_DATA_MINING.CREATE_MODEL(
    model_name          => 'glm_model_1',
    mining_function     => dbms_data_mining.classification,
    data_table_name     => 'tbl_training_set',
    case_id_column_name => 'record_id',
    target_column_name  => 'is_fraud',
    settings_table_name => 'glm_settings');
END;
/
We can see what models we have built in the database using the following query.
select object_name, object_type, created from all_objects where object_type like '%MINING%' order by created;
OBJECT_NAME OBJECT_TYPE ---------------------------------------- -------------- GLM_CTR MINING MODEL GLM0322A MINING MODEL GLM0322ORG MINING MODEL GLM0322DT MINING MODEL GLM0322EXP MINING MODEL GLM0322V2 MINING MODEL GLM0322V3 MINING MODEL GLM0322V4 MINING MODEL GLM0322V5 MINING MODEL

Calculate correlation matrix using SQL


Any table can be converted into the a simple table with only three columns:  rec_id (record id), var_id (column_id), val (value). For example,
Original Table
Record_ID   Variable_1 Variable_2 Variable3
1                      1.2             2.2                 3.3
2                     2.0             1.5                  4.0
The above table can be converted into the following simple table.
Rec_id  var_id    val
1             1         1.2
1             2         2.2
1             3         3.3
2             1         2.0
2             2         1.5
2             3         4.0

Once the data are stored in the simple, three column table,  the top-right correlation matrix can be easily calculated using the following query. 

select a.var_id var_id1, b.var_id var_id2, corr(a.val, b.val) correl
from tbl_simple  a, tbl_simple b where a.rec_id=b.rec_id 
and a.var_id<=b.var_id group by a.var_id, b.var_id ;

This method can handle unlimited number of records and variables. To improve the performance, we should create index on rec_id and var_id. Also see my yesterday's post about "Calculate correlation coefficient between two variables using SQL".

Sunday, August 26, 2012

Calculate correlation coefficient between two variables using SQL


Oracle function corr()  returns the correlation coefficient between two variables. The following scripts show two ways of calculating correlation coefficient.

1. Calculate correlation coefficient  using corr() function.
select corr(x,y) from TBL_XY;
.214418947
2. Calculate correlation coefficient directly without using corr() function. The equation is based on  Pearson's coefficient (http://en.wikipedia.org/wiki/Pearson_product-moment_correlation_coefficient). The results are the same.
with tbl_mean as(
select avg(x) mean_x, avg(y) mean_y from tbl_xy
),
tbl_vec_m_corrected as
(
select x-mean_x mean_x_corrected, y-mean_y mean_y_corrected
from tbl_xy, tbl_mean
)
select sum(mean_x_corrected*mean_y_corrected)/(
sqrt(sum(mean_x_corrected*mean_x_corrected)) *sqrt(sum(mean_y_corrected*mean_y_corrected)))
 as corr_coef
from tbl_vec_m_corrected ;
.214418947

Create new tables in Oracle and SQL Server


In Oracle, we can use  "create table as select" as shown below.
1. Copy table into a new table.
create table tbl_x as select * from tbl_test;
2.Create a  new empty table with the same structure.
create table tbl_y as select * from tbl_test where 2=1;

In SQL Server,  we can use select into as shown below.
1. Copy table into a new table.
select * into  tbl_x   from  tbl_test ;
2. Create a  new empty table with the same structure.
select * into  tbl_y   from tbl_test  where 2=1;

Friday, August 24, 2012

Calculate the similarity between two strings in Oracle


Oracle function utl_match.edit_distance_similarity (s1 IN VARCHAR2, s2 IN VARCHAR2) returns 100 for perfect match between s1 and s2, 0 for no similarity. It is case sensitive. I have used this functions to match the merchant names in debit card authorizations and claims. I noticed that this function behave slightly differently in 10g and 11g. In 10g, it is possible that it return number larger than 100 when null string is involved and I had to deal with them carefully. This function is particularly useful when performing fuzzing matching between two strings such as merchant names in two tables.

select utl_match.edit_distance_similarity('Hello', 'Hello') dis from dual;
100
/*case sensitive */
select utl_match.edit_distance_similarity('Hello', 'hello') dis from dual;
 80
/*case sensitive */
select utl_match.edit_distance_similarity('Hello', 'HELLO') dis from dual;
 20
select utl_match.edit_distance_similarity('Hello', 'HLO') dis from dual;
 20
select utl_match.edit_distance_similarity('Hello', 'X') dis from dual;
0
select utl_match.edit_distance_similarity('Hello', ' ') dis from dual;
0
select utl_match.edit_distance_similarity('Hello', NULL) dis from dual;

0

Calculate Median value in Oracle

There are three functions that calculate median values, median(), percentile_count() and percentile_disc.
1. The data. 
select num from  TBL_TEST order by num;
       NUM
----------
         1
         1
         2
         2
         2
         3
         4
         4
         5

2. Calculate the median or 50th percentile.
select median(num) f_median,
   PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY num)  f_percentile_cont,
   PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY num) f_percentile_disc
from TBL_TEST ;
  F_MEDIAN F_PERCENTILE_CONT F_PERCENTILE_DISC
---------- ----------------- -----------------
         2                 2                 2

Wednesday, August 15, 2012

Calculate Z-Score using SQL in Oracle

We can normalize data using Z-score, Z-score=(data value-mean)/standard deviation.
1. Data.
select num from  TBL_TEST order by num;
       NUM
----------
         1
         1
         2
         2
         2
         3
         4
         4
         5
2. Calculate Z-score.
with tbl_mean_std as
(
select avg(num) m, stddev(num) std from TBL_TEST
)
select num, (num-m)/std as z_score from TBL_TEST , tbl_mean_std  order by num;

       NUM    Z_SCORE
---------- ----------
         1 -1.1785113
         1 -1.1785113
         2 -.47140452
         2 -.47140452
         2 -.47140452
         3  .23570226
         4 .942809042
         4 .942809042
         5 1.64991582

Calculate cumulative percentage in Oracle

Using Cume_dist(), it is easy to calculate the cumulative percentage of a number.

1. The data.
select num from  TBL_TEST order by num;
       NUM
----------
         1
         1
         2
         2
         2
         3
         4
         4
         5
2. Calculate cumulative percentage using cums_dist()
select num, cume_dist() over(order by num) as cum_percent from TBL_TEST order by num;
       NUM CUM_PERCENT
---------- -----------
         1  .222222222
         1  .222222222
         2  .555555556
         2  .555555556
         2  .555555556
         3  .666666667
         4  .888888889
         4  .888888889
         5           1

Rank in Oracle: rank() and dense_rank()


Unlike row_number() function, rank() and dense_rank() may produce duplicate ranks.

1. The data
select num from  TBL_TEST order by num;
       NUM
----------
         1
         1
         2
         2
         2
         3
         4
         4
         5
2.  rank() may have gaps. 
select num, rank() over(order by num) as rnk from  TBL_TEST  order by num;
       NUM        RNK
---------- ----------
         1          1
         1          1
         2          3
         2          3
         2          3
         3          6
         4          7
         4          7
         5          9
3. dense_ran() does not have gap in ranks.
select num, dense_rank() over(order by num) as rnk from  TBL_TEST  order by num;
       NUM        RNK
---------- ----------
         1          1
         1          1
         2          2
         2          2
         2          2
         3          3
         4          4
         4          4
         5          5

Thursday, August 02, 2012

Rank in Oracle

There are several ways to rank records based on certain variables. One of the is to use row_number

select * from TBL_TEST order by grp, num;
GRP               NUM
---------- ----------
A                   1
A                   2
A                   2
A                   3
A                   4
B                   1
B                   2
B                   4
B                   5

Row_number() generates unique sequece numbers without gap. Ties are given different numbers. I use row_number() to generate unique id.

Example 1. generate rank separately for GRP
with tbl as(
select a.grp, a.num, row_number() over(partition by GRP order by num) rnk from TBL_TEST a)
select * from tbl order by grp, rnk ;
GRP               NUM        RNK
---------- ---------- ----------
A                   1          1
A                   2          2
A                   2          3
A                   3          4
A                   4          5
B                   1          1
B                   2          2
B                   4          3
B                   5          4

Example 2. Generate rank without partition by GRP
with tbl as(
select a.grp, a.num, row_number() over(order by grp, num) rnk from TBL_TEST a)
select * from tbl order by grp, rnk ;
GRP               NUM        RNK
---------- ---------- ----------
A                   1          1
A                   2          2
A                   2          3
A                   3          4
A                   4          5
B                   1          6
B                   2          7
B                   4          8
B                   5          9

I will discuss rank functions including dense_rank(), ran() in another post.