NOAA Comprehensice Large Array-Data Stewdship System (CLASS)

I am working on a project that needs climate data about many towns China. I looked around and a friend who works for National Oceanic and Atmospheric Administration (NOAA) mentioned that NOAA Comprehensice Large Array-Data Stewdship System (CLASS) The site contains many data sources derived from earth observation satellites.

19 Free Public Data Sets From Springboard

Springboard "19 Free Public Data Sets For Your First Data Science Project" has good information about free data sets such as United States Census Data, FBI Crime Data and CDC Cause of Death etc. I also added the link to my post Free Data Sources

Count Unique Values in Oracle and Microsoft Access

I have found that it is convenient to use Microsoft Access to create an external link to a Spreadsheet and run SQL queries against it.

In a recent project, I want to count the name of unique name in table t_test. If it is an Oracle table, it can be done using "count(distinct name)" as the following:

select count(distinct name) from t_test;

However, t_test is an linked external spreadsheet within an Access database. Access does not support "count(distinct "). So I first find the unique name and then count the number of records using the following query:

select count(*) from
(
SELECT  distinct name
FROM t_test
)

Current Schema Name

To find out the current schema that we are connect to, we may use function sys_context(). I connected to Oracle SQL Live at https://livesql.oracle.com and ran the following query:

select sys_context('USERENV', 'SESSION_USER') from dual;

SYS_CONTEXT('USERENV','SESSION_USER')
APEX_PUBLIC_USER

The returned result showed that the schema name was APEX_PUBLIC_USER.

How to set up date display format in SQL Developer

A Simple Way to Load Excel Spreadsheet Into Oracle Database

Display Date in SQLPLUS Client

When I select column of date type within SQLPLUS, I got the following result.

SQL> select txn_date from card_txn where rownum <10;

TXN_DATE
---------
02-OCT-11
02-OCT-11
02-OCT-11
02-OCT-11
02-OCT-11
03-OCT-11
23-OCT-11
23-OCT-11
23-OCT-11

9 rows selected.

Since I want to see the hour, minute and second of a transaction, I use to_char() to format the date.

SQL> select to_char(txn_date,'YYYYMMDD:hh24:mi:ss') from card_txn 
where rownum <10;

TO_CHAR(TXN_DATE,
-----------------
20111002:22:53:54
20111002:22:21:30
20111002:11:38:17
20111002:23:45:52
20111002:02:19:17
20111003:11:38:17
20111023:23:30:24
20111023:23:30:52
20111023:14:57:46

9 rows selected.

If we want to display dates in desired format without using to_char() conversion, we can use the following command. After running this command, all dates will be automatically formatted.

SQL> alter session set nls_date_format='YYYYMMDD:hh24:mi:ss';

Session altered.

SQL> select txn_date from card_txn where rownum <10;

TXN_DATE
-----------------
20111002:22:53:54
20111002:22:21:30
20111002:11:38:17
20111002:23:45:52
20111002:02:19:17
20111003:11:38:17
20111023:23:30:24
20111023:23:30:52
20111023:14:57:46

9 rows selected.

Troubleshooting: Could not Connect to Amazon RDS Oracle Instance

I could not connect to the Oracle Instance on Amazon RDS platform when I worked in the town library. I followed the following steps and resolved the issue:

1. Find my IP address using Google Search "my ip address". Google will display my ip address 64.64.117.xx.

2. Go to Amazon AWS console/Services/RDS/Dashboard/Instance. Right click the instance and select "See details".

3. From the detail view, click the link to next to "Security Groups".
4. At the bottom section, click Inbound and then Edit

5. Click Add Rule and add the IP address 64.64.117.xx/32.

Oracle SQLPLUS Client Installation on Windows 64

I want to run SQLPLUS interactive command environment to query Oracle databases. When I type my SQL commands and hit return key, I get the response from the database right away. It is like I am having a conversation with the data. To me, this is a more preferable way than using a GUI-based tool. I wrote a post 3 years ago, Oracle SQLPLUS Client Installation on Windows Troubleshooting. But that one was about installing 32 bit version installation. Since I am installing 64 bit version on my Windows Machine, I write down the steps.

Step 1. Go to the Oracle site:
http://www.oracle.com/technetwork/topics/winx64soft-089540.html
Step 2. Accept OTN License Agreement and download the following two files:

Version 12.1.0.2.0
   Instant Client Package - Basic: All files required to run OCI, OCCI, 
and JDBC-OCI applications
Download instantclient-basic-windows.x64-12.1.0.2.0.zip (72,416,242 bytes) 
(cksum - 1751087003)
  Instant Client Package - SQL*Plus: Additional libraries and executable for 
running SQL*Plus with Instant Client Download 
instantclient-sqlplus-windows.x64-12.1.0.2.0.zip (876,515 bytes) 
(cksum - 1826332995) 

Step 3. I unzip both zip files and put files under the same directory. I run the following command and am able to connect the my Oracle DB on Amazon cloud.
sqlplus myusername/mypassword@mysid.ctbxjhstgq8o.us-east-1.rds.amazonaws.com:1521/mysid

Dr. Jiang Zhou will Give a Talk at The University of Massachusetts Boston

I am going to give a talk at Department of Computer Science, the University of Massachusetts Boston, on January 31. The following is the abstract:

Abstract: Will a new mobile phone customer pay her monthly bill? Is a check deposited into a bank customer’s account fraudulent? Will an insurance claim become expensive? For nearly two decades, Dr. Jiang (Jay) Zhou has been helping mobile phone service providers, banks, insurance companies and other businesses solve problems like these. The predictive models that he built have resulted in over $200 million savings for clients. In the seminar, Dr. Zhou will discuss topics including challenges and best practices when building these predictive models in real world environments.

Ranking High in Kaggle Competition is a Huge Advantage for Job Seekers

For someone who is looking for a job in data analytics field, high rankings in Kaggle Competition will give him tremendous advantage. Employers see the competition winners having strong problem solving skills and hands-on expertise. Indeed, to be able to complete some projects for Kaggle competitions, participants have to put in a lot of effort to deal with data issues even before any predictive models can be built. This is very similar to that in the real world applications where 80% of time is spent on data cleanse and manipulation. It is not a surprise that some employers prefer Kaggle competition winners over PhD graduates whose skills are perceived as more theoretical.

Taking my nephew, Yuyu Zhou, as an example. He got a master degree in data analytics. While he was in school, he spent a few weeks with other classmates to participate in Kaggle competitions. His team has achieved the top 3% and 5% in two Kaggle prediction competitions respectively (see my blog post a Young Data Scientist- Kaggle Competition Top 5% Winner: Yuyu Zhou. Once he graduated, he was quickly hired by a prestigious company and has been earning PhD level salary. Those few weeks he spent on Kaggle Competition was the best time investment of his life.

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);

begin
for i in 1..1000
 loop 
 insert into t_n values(i);
 end loop;
 commit;
end;

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;
BBUCKET COUNT(*) MIN(N) MAX(N)
0 154          2  993
1 164          7  999
2 168          6  991
3 175          4  995
4 173          8 1000

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;

ID GP WORD
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;

GP LISTAGG(WORD,'')WITHINGROUP(ORDERBYID)
1 Hello World!
2 Good Morning!

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.

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 

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.

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 yuyu.zhou@hotmail.com or phone (508) 933-7311. Here is his LinkedIn Profile.

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  u.name owner, o.name table_name,t.name tablspace_name 
from
 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
      and u.name='DMUSER'
order by 1,2;
OWNER     TABLE_NAME              TABLSPACE_NAME
-------------------------------- -------------------------------- 
DMUSER     AI_EXPLAIN_OUTPUT         TBS_1
DMUSER     AR_SH_SAMPLE_SETTINGS     TBS_1
DMUSER     DM$P0AR_SH_SAMPLE         TBS_1
DMUSER     DM$P0AR_SH_SAMPLE_2COL    TBS_1
DMUSER     DM$P0EM_SH_CLUS_SAMPLE    TBS_1
DMUSER     DM$P0NB_SH_CLAS_SAMPLE    TBS_1
DMUSER     DM$P0OC_SH_CLUS_SAMPLE    TBS_1
DMUSER     DM$P1EM_SH_CLUS_SAMPLE    TBS_1
DMUSER     DM$P1NB_SH_CLAS_SAMPLE    TBS_1
DMUSER     DM$P1OC_SH_CLUS_SAMPLE    TBS_1

a Random Number Generator

Rand Number Generator

Dr. Jiang Zhou's Presentation at UNH Analytics

I shared my analytics experience with a group of students and professors from University of New Hampshire Analytics on February 25. I really enjoyed the interaction with professors and students. The following is the news on UNH Graduate Analytics LinkedIn page.

UNH Graduate Analytics would like to thank Dr. Jiang Zhou for his wonderful presentation this morning, which was part of our Analytics Speaker Series. 

Dr. Zhou highlighted the advantages and disadvantages of using statistical models vs. intuition to solve business problems throughout his 10+ years of experience. Among his many accomplishments, Dr. Zhou built a fraud detection model for large banks, which detects 70% more fraud than using business intuition rules alone.