Thursday, October 20, 2016

Oracle Ora_Hash function- Part 1 Random Sampling

Oracle ora_hash() is a very useful function. I have used it for different purposes such as generating random number. The following query generate 5 buckets from 0 to 4, each of them have the similar number of records.
First, we create a table and populate it with 1,000 numbers.

create table t_n (n number);

for i in 1..1000
 insert into t_n values(i);
 end loop;
In the query below, the parameter 5 of ora_hash defines the number of buckets is 5. As we see, each bucket has simlilar number of records.
with tbl as
select ora_hash(n, 5) bucket, n  from t_n)
select bucket, count(*), min(n), max(n) from tbl
group by bucket order by 1;
0 154          2  993
1 164          7  999
2 168          6  991
3 175          4  995
4 173          8 1000

Saturday, July 02, 2016

SQL Listagg Function

Listagg is a useful function to combine column values from different rows. In a sense, it is similar to sum() function that aggregates information across records.
To show how listagg works, first we create a table that has record id, group id and words.

create table t(id number, gp number, word varchar2(128));

insert into t values(1,1,'Hello');
insert into t values(2,1,'World!');
insert into t values(3,2,'Good');
insert into t values(4,2,'Morning!');
select * from t;

1 1 Hello
2 1 World!
3 2 Good
4 2 Morning!

We can combine the words by group using listagg function.
select gp, listagg(word,' ') within group(order by id) 
from t group by gp order by gp;

1 Hello World!
2 Good Morning!

Saturday, June 11, 2016

Run Oracle SQL Online

I have found an excellent website to learn Oracle PL/SQL Oracle SQL live. From the website, we can run SQL or PL/SQL commands. There are also many scripts in Code Library that we can learn from. Here are screenshots.

Wednesday, May 11, 2016

Featured Company: ZSAnalytics

ZSAnalytics is a business partner bringing advanced process capabilities to its customers at the best possible cost resulting in the highest savings. ZSA provides customer’s access to Productized Analytics, advanced Analysis and subject matter expertise resulting in increased operational efficiency and lowest total operating cost. Their solutions and services have saved our clients hundreds of millions. They include:

  • ZSales: 70% increase in response rate
  • ZClaim: 40% reduction in insurance claim losses
  • ZCheck: 70% reduction in charge off
  • ZCredit: 50% reduction in delinquency rate 
In the following youtube video clips, ZSAnalytics' CEO Chris Sandusky is explaining how ZSA's solutions lower the overall business cost and the advantages of predictive models over intuitive rules in fraud detection.

Lower the Overall Business Cost


Predictive Models vs Intuitive Rules 

    Sunday, March 06, 2016

    the Danger of Predictive Model Overfitting

    In the post a Young Data Scientist- Kaggle Competition Top 5% Winner: Yuyu Zhou, Yuyu talks about the important role of feature engineering, i.e, finding good derived variables, and gradient boosting trees in their success. He also tells me a very interesting observations on Kaggle Competition ranking.

    "After participating teams finished building their predictive models, they apply their models to two data sets to generate predictions: a smaller set containing target variable and a larger data set where the target variable is removed. Each participating team's model is temporarily ranked based on the result on the smaller data set with the target variable. After the competition's deadline is due, Kaggle will calculate the final ranking of each team based on a model's prediction on the larger data set. "
    "It is interesting to see that the rankings of some top 1% models based on the smaller data set drop more than 20% on the larger data set. I figure out what might cause the huge discrepancies in their model performance. Those teams' models fit the smaller data set so well that they lose their capability to generalize. It is a typical overfitting problem."
    It is important to avoid model overfitting. After all, a predictive model is only useful if it can generalize, i.e., able to handle new data reasonably well.

    Saturday, March 05, 2016

    a Young Data Scientist- Kaggle Competition Top 5% Winner: Yuyu Zhou

    Yuyu Zhou is a graduate student in Analytics in University of New Hampshire. His team has achieved the top 3% and 5% in two Kaggle prediction competitions respectively. In an interview, I asked him how their predictive models performed so well. Yuyu said,

    "One of the keys to the success is that we spend tremendous amount of time working on building feature variables. Those variables are usually the results of combining several raw variables. For example, the ratio between the body weight and height is a better variable in predicting a patient's health than using body weight or height alone."
    "My training in computer science is extremely helpful in these projects. I am able to write Java, Python and SQL scripts to perform tasks such as data cleansing, data merge, and data transform, etc. As we know, more than 80% of time in a project is typically spent on those tasks before we start building predictive models."
    "We have tried many type of predictive models and found that gradient boosting trees have consistently perform the best."

    The following is a summary of Yuyu's contribution in those two projects.

    Kaggle Competition: Rossmann Store Sales Prediction (ranked top 5%) Oct 2015 – Dec 2015

    • Built the Predictive Model for daily sales for Rossmann Stores using Python Machine Learning library.
    • Conducted data cleaning and feature engineering for increasing data quality.
    • Designed final prediction model by combining the multiple gradient boosting trees algorithms
    • Prediction accuracy was ranked at 163 out of 3303 teams

    Kaggle Competition: Property Risk Level Prediction (ranked top 3%) July 2015 – Aug 2015

    • Developed Statistics models to predict risk level of properties which Liberty Mutual Inc is going to protect.
    • Led the team and conducted cost and benefit analysis on new ideas.
    • Implemented ideas using statistical packages from Python.
    • Prediction accuracy was ranked at 71 out of 2236 teams.
    Yuyu is currently looking for a full time job in data analytics. Please feel free to contact him if you are hiring. He can be reached by email or phone (508) 933-7311. Here is his LinkedIn Profile.

    Friday, March 04, 2016

    Find Tablespaces for Oracle Tables

    We may run the following query from SYS to find the tablespace names for all tables, including IOTs (index organized table) and partitioned tables (replacing 'DMUSER' with your user name).

    select owner, table_name, tablspace_name 
     obj$ o,  ts$ t, sys_objects s, user$ u
    where o.obj#=s.object_id and s.ts_number=t.ts#
          and o.owner#=u.user# 
          and o.type#=2
    order by 1,2;
    -------------------------------- -------------------------------- 
    DMUSER     DM$P0AR_SH_SAMPLE         TBS_1