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

How to tokenize text in Oracle

The following procedure splits text column query into keywords,e.g., "Hello World" into two words "Hello" and "World".


Step 1. Create index of type ctxsys.contex.
create index t_query_idx on t_query(query) indextype is ctxsys.context;


Step 2.
set serveroutput on;
declare
the_tokens ctx_doc.token_tab;
begin
for i in (select row_p_key from t_query order by row_p_key) loop
ctx_doc.tokens('t_query_idx', i.row_p_key, the_tokens);
for ii in 1..the_tokens.count loop
insert into tbl_query_token select i.row_p_key,the_tokens(ii).offset, the_tokens(ii).token from dual;
end loop;
commit;
end loop;
end;
/

Five Ways of Creating Unique Record Identifier For Oracle Tables

I have used the following five ways to create a unique record identifier for an Oracle table using SQL.
1. Simply use Oracle pseudocolumn rowid
Oracle pseudocolumn rowid comes with every table. It looks like the following.
SQL> select rowid from table_test where rownum <3;
ROWID
------------------
AAAYLPAAVAAEvLTAAA
AAAYLPAAVAAEvLTAAB
2. Create a  unique record id using rownum
create table table_test_new as select rownum as uniq_id, a.* from table_test a;
3. Create a unique record id using row_number()
create table table_test_new2 as select row_number() over(order by LOG_DATE)  uniq_id, a.* from  table_test a;
4. Create a unique record id using sequence.
create sequence rec_id_seq;
create table table_test_new3 as select rec_id_seq.nextval uniq_id, a.* from  table_test a;
5. Create a unique record id using sys_guid()
According to Oracle document, SYS_GUID generates and returns a globally unique identifier (RAW value) made up of 16 bytes.
create table table_test_new4 as select sys_guid() uniq_id, a.* from  table_test a;
What are other ways to create a unique record id that I am missing?

Floating Point Representation: the Loss of Precision

On SAS PC, 11169568236203649 is actually represented as 11169568236203648 due to the limitation of  64-bit IEEE floating point representation. 


In most of the applications, there is practically no difference between  11169568236203649  and 11169568236203648. Unfortunately, every digit matters in the case of function mod. 

  

It is not a bug in SAS mod function.

My original suspicion was an issue with SAS mod function itself. It is actually because the number 11,169,568,236,203,649 is larger than the maxim integer9,007,199,254,740,992  supported by SAS PC vesion. It is a not a bug since it is clearly stated in the document that there is a maximum integer without rounding supported by SAS.


I was building random generators on both SAS and Oracle. It is required that given the same initial number, the random generators on SAS and Oracle will produce the same series of pseudo random numbers.  Since it is impossible to match the SAS and Oracle results when the initial number is longer than 16 digits (Oracle integer supports 38 digits of precision), in my code I will throw an exception, stop the process and print a message similar to ORA-01438: "value larger than specified precision allowed for this column". 

A Bug in SAS MOD Function.

MOD( m, n)  is one of the most commonly used functions. It  returns the remainder of m divided by n and the result is deterministic. We would expect it to be calculated correctly by any data analysis software packages.

However, we have found an issue in SAS mod function.The calculation we tested is  mod(11169568236203649, 30269).  SAS returns different results than Oracle and R do. SAS returns 11731. Both Oracle and R return 11732.

SAS script (SAS Version 9.2) and the result.
data test; val=mod(11169568236203649, 30269) ; run;
11731
or
proc sql; select mod(11169568236203649, 30269) from dummy;
11731

Oracle SQL script and the result.
select mod(11169568236203649, 30269) from dual;
11732

R script and the result.
11169568236203649 %% 30269
11732

We have verified that 11732 is the correct answer using two independent approaches. You may try the above scripts on your own computer and let me know what you find out. ( I explain my approaches to verify that 11732 is the correct answer of mod(11169568236203649,30269) and why I bother to calculate mod(x, 30269) in other posts on this blog.)

SAS Is Wrong! R and Oracle Are Right. MOD(11169568236203649, 30269)=11732

For function MOD(11169568236203649, 30269), SAS returns 11731 while both Oracle  and R  return 11732. I used the following two approaches to verify SAS Is Wrong.  Oracle and R are right.
Approach 1. The following two Oracle Queries show that 11732 is the remainder
SQL>  select ( 11169568236203649 -11732)/30269 vl from dual;
        369010150193.000000
SQL>  select ( 11169568236203649 -11731)/30269 vl from dual;
        369010150193.000033


Approach 2. The following PL/SQL scripts.
At first, I wanted to run the following PL/SQL Script 1. It took too long and I had to kill the process. Instead, I ran Script 2 shown below and it returned  11732 at the end of the loop.
PL/SQL Script 1.
set serveroutput on;
declare
remainder number;
begin
remainder:=11169568236203649;
while (remainder >30269)
loop
remainder:=remainder-30269;
end loop;
dbms_output.put_line(remainder);
end;
/
PL/SQL Script 2.
set serveroutput on;
declare
remainder number;
begin
remainder:=11169568236203649;
while (remainder>302690000000)
loop
remainder:=remainder-302690000000; /* equivalent of 10,000,000 loops of -30269 */
end loop;
dbms_output.put_line(remainder);
while (remainder>30269)
loop
remainder:=remainder-30269;
end loop;
dbms_output.put_line(remainder);
end;
/
At the end of the loop, it returns 11732.

Calculate Gain Chart Using SQL in Oracle

The following script calculates the gain chart based on score and payment_ind (target variable, payment or non payment).  The mod function is to reduce the number of data points in the gain chart

with  tbl2 as
(select a.*, row_number() over(order by score desc ) risk_rank from tbl_testing_data a),
tbl3 as
( select a.*,
count(1) over(order by risk_rank) total_num,
sum(case when payment_ind='Y' then 1 else 0 end ) over(order by risk_rank) total_bad,
count(1) over(order by risk_rank)/count(1) over() pcnt_tot,
sum(case when payment_ind='Y' then 1 else 0 end ) over(order by risk_rank)/ sum(case when payment_ind='Y' then 1 else 0 end ) over()
pcnt_bad
 from tbl2 a
)
select * from tbl3 where mod(risk_rank,100)=1 order by risk_rank;

SAS and SQL (Oracle) Version of Univariate Statistics

The following SAS and SQL scripts produce exactly the same results.

SAS Version

proc univariate data = claims ;
by grp;
var payment_amt;
output out = claims_stats N = lines min = var_min
max = var_max mean = var_mean std = var_stnd_dev;
Run;


SQL Version

create materialized view claims
as select
grp,
count(1) lines,
minpayment_amt) var_min,
max(payment_amt) var_max,
avg(payment_amt) var_mean,
stddev(payment_amt) var_stnd_dec
from claim
group by grp;