NULL Value and Arithmetic Operations

Any arithmetic operations invlove NULL values will return NULL. We use the following table to illustrate this.

SQL> select * from tbl_test3;

        ID          A          B
---------- ---------- ----------
         1          1          2
         2          1
         3
         4          2          2

SQL> select t.*, a+b from tbl_test3 t;

        ID          A          B        A+B
---------- ---------- ---------- ----------
         1          1          2          3
         2          1
         3
         4          2          2          4

Watch out NULL values when comparing data

It is a very common task to compare data values. For example, I was involved in project where we  upgraded the scoring engine. We wanted to make sure the old and new scoring engines produce the same outputs given the same inputs. I use the following table to illustrate the problem. We want to make sure value_old and value_new are the same. (The blanks are NULL values.)

        ID  VALUE_OLD  VALUE_NEW
---------- ---------- ----------
         1        234
         2                   567
         3        789        789

If we simply use the following query to count the number of discrepancies, the result will return zero. This is not what we expect.
select count(*) from tbl_data_a where VALUE_OLD < > VALUE_NEW;

  COUNT(*)
----------
         0
This is because rows with NULL values appearing in the comparison are ignored.

A better approach is to write a query considering all of the following five situations:
In the following cases, VALUE_OLD and VALUE_NEW are the same.
1. VALUE_OLD is null, and VALUE_NEW is null.
2.VALUE_OLD is not null, VALUE_NEW is not null and VALUE_OLD=VALUE_NEW.

In the following cases, VALUE_OLD and VALUE_NEW are the different.
3.VALUE_OLD is null, and VALUE_NEW is not null.
4.VALUE_OLD is not null, and VALUE_NEW is null.
5.VALUE_OLD is not null, VALUE_NEW is not null and VALUE_OLD < > VALUE_NEW.

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.