Calculate Average Value - Watch Out NULL Values

Calculating the average or mean is one of the most common tasks. Average or mean of a variable is simply the sum of values divided by the number of data points. However if we do not understand how null values are handled by a database, mistakes may happen. Taking the following table as an example. It has 6 records and two of them have null in column value.

SQL> select * from tbl_test2;

        ID      VALUE
---------- ----------
         1          2
         2          3
         3          4
         4          5
         5
         6

6 rows selected.

We calculate the average value using avg() function.

SQL> select avg(value) from tbl_test2;

AVG(VALUE)
----------
       3.5

The result is 3.5. If we use the following query to calculate the total number of records (6), the sum of value(14) and the average as the sum of value divided by number of records (14/6), the average value is 2.33.

SQL> select  count(*) tot_num, sum(value) sum_value, sum(value)/count(*) as avg_2 
from tbl_test2;

   TOT_NUM  SUM_VALUE      AVG_2
---------- ---------- ----------
         6         14 2.33333333

We can see the average value calcuated by avg(value) and sum(value)/count(*) is very different. Why does this happen? The reason is that when Oracle calculates function avg(value), the records with null value are excluded from consideration. We can add a condition to the second query to specifiy only records with non-null value are taking into consideration as the following.

SQL> select  count(*) tot_num, sum(value) sum_value, 
            sum(value)/count(*) as avg_2  
from tbl_test2 where value is not null;

   TOT_NUM  SUM_VALUE      AVG_2
---------- ---------- ----------
         4         14        3.5

Now, the sum(value)/count(*) returns the same value as avg(value). As we can see, it is important to understand how NULL values are handled in a database.

Display How Long to Run a Query

Sometimes, we want to measure how long it takes to run a query. In sqlplus, this can be done by command "set timing on". The time elapsed for runing a query will be displayed after "set timing on".

SQL> set timing on;
SQL> select count(1) from PLANET;

  COUNT(1)
----------
         9

Elapsed: 00:00:00.03
SQL> select count(1) from user_objects;

  COUNT(1)
----------
      1099

Elapsed: 00:00:00.09
SQL>

Create or Replace Oracle Table

Problem

We can use "create or replace view" to create a view if it does not exist or replace it if it exits. However, there is not such thing as "create or replace table". How do we create a table if it does not exist or replace the table if it exists?

Solution

The following PL/SQL does preisely that. We store the script as a file create_or_replace.sql. It first tries to drop the table. If it does not exist, an exception will be raised and nothing is done. Then the script continues to create the table. We put the exception inside the begin/end block so that the exception will only break the first block and will continue to execute the "create table" statement outside of the block.

begin
  begin
   execute immediate 'drop table tbl_test';
  exception when others then
   NULL;
  end;
  execute immediate 'create table tbl_test (id number, value number)';
end;
/

We run the scripts twice. In the first time, table tbl_test were created. In the second run, table tbl_test was dropped and then created.

SQL> @create_or_replace.sql

PL/SQL procedure successfully completed.

SQL> @create_or_replace.sql

PL/SQL procedure successfully completed.

More on Calculating Histogram Using Oracle Function

In the older post Calculate Histogram Using Oracle Function, we showed how to use functions width_bucket() and ratio_to_report(). To display histogram visually, function lpad() can be used.

SQL> with
  2   tbl as (
  3   select min(NUM) low, max(NUM) high
  4   from TBL_1K_RND),
  5   tbl2 as (
  6   select width_bucket(NUM, low, high, 10) s, NUM
  7   from TBL_1K_RND, tbl
  8   )
  9   select s,
 10   min(NUM) lower, max(NUM) upper,
 11   count(1) num,
 12   round((ratio_to_report(count(1)) over())*100,1) pcnt,
 13   lpad('*', round((ratio_to_report(count(1)) over())*100,0), '*') histogram
 14   from tbl2 group by s order by s;

         S      LOWER      UPPER        NUM       PCNT HISTOGRAM
---------- ---------- ---------- ---------- ---------- ------------------------------
         1 -3.0602851 -2.5082225          7         .7 *
         2 -2.4379864   -1.84617         35        3.5 ***
         3 -1.8150501 -1.2060425         79        7.9 ********
         4 -1.1991025 -.58413539        164       16.4 ****************
         5 -.58247189 .029088646        224       22.4 **********************
         6 .040683615  .65371762        242       24.2 ************************
         7 .672280301 1.27225368        151       15.1 ***************
         8 1.27500147 1.82004315         61        6.1 ******
         9 1.90959272 2.47850573         33        3.3 ***
        10 2.56404876 3.00088262          4         .4
        11  3.1319198  3.1319198          1         .1

11 rows selected.

Lpad Function For Data Visualization- Making Bar Chart

Problem

Lpad() is a very cool function that can be used for data visualization. It takes three parameters as an example shown below. The first parameter is the string to display('hello'), the second one is the length of the resulting string (10), the third one if the character(*) that will be used to fill the spaces on the left.

SQL> select lpad('hello',10,'*') s from dual;

S
----------
*****hello

We can use lpad to display "bar chart" based on the data. For example, we have the following daily stock price data. How do we display the price variation visually?

SQL> select * from stock;

SYM DAT            PRICE
--- --------- ----------
XYZ 31-MAY-11         14
XYZ 01-JUN-11         19
XYZ 02-JUN-11         21
XYZ 03-JUN-11         23
XYZ 04-JUN-11         27
XYZ 05-JUN-11         14
XYZ 06-JUN-11         17
XYZ 07-JUN-11         22
XYZ 08-JUN-11         26
XYZ 09-JUN-11         27
XYZ 10-JUN-11         21
XYZ 11-JUN-11         17
XYZ 12-JUN-11         27
XYZ 13-JUN-11         27
XYZ 14-JUN-11         16
XYZ 15-JUN-11         14
XYZ 16-JUN-11         16
XYZ 17-JUN-11         26
XYZ 18-JUN-11         25
XYZ 19-JUN-11         24

20 rows selected.

Solution

We use the following query. Lpad('*', price, '*') means it will make a string "*" of the length of "price" with left spaces filled with '*'.

SQL> select dat, price, lpad('*', price, '*') price_bar 
from stock;

DAT            PRICE PRICE_BAR
--------- ---------- ----------------------------------------
31-MAY-11         14 **************
01-JUN-11         19 *******************
02-JUN-11         21 *********************
03-JUN-11         23 ***********************
04-JUN-11         27 ***************************
05-JUN-11         14 **************
06-JUN-11         17 *****************
07-JUN-11         22 **********************
08-JUN-11         26 **************************
09-JUN-11         27 ***************************
10-JUN-11         21 *********************
11-JUN-11         17 *****************
12-JUN-11         27 ***************************
13-JUN-11         27 ***************************
14-JUN-11         16 ****************
15-JUN-11         14 **************
16-JUN-11         16 ****************
17-JUN-11         26 **************************
18-JUN-11         25 *************************
19-JUN-11         24 ************************

20 rows selected.

Calculate Confusion Matrix Using SQL

Problem

Confusion matrix is often used to measure the accuracy of a predictive model. For example, for a model that predicts binary outcomes, if we compare the prediction and actual outcome, say 0 or 1, there are four possibilities: true positive (predicted 1 and actual 1), false positive (predicted 1 and actual 0), true negative (predicted 0 and actual 0) and false negative (predicted 0 and actual 1)as shown in the diagram below.

How to calculate the confusion matrix using SQL?

Solution

We can easily calculate the confusion matrix using SQL "group by". For example, the following query first applies model GLM1031E to data set DATASET_TEST using prediction() function. Then "group by" is used to calculate the number of records for all combinations of predicted and actual outcomes.

SQL> with tbl as (select prediction(GLM1031E using * ) predicted, 
account_default as actual from DATASET_TEST) select predicted, actual, 
count(1) 
from tbl group by predicted, actual;

 PREDICTED     ACTUAL   COUNT(1)
---------- ---------- ----------
         1          0          3
         0          0        437
         1          1         41
         0          1          3

I have found that in real world application people pay a great deal of attention to false positive. For example, in credit card fraud detection, there are only a few fraudulent transactions out of ten thousand. If our predictive model can detect correctly one true frauds (true positive) while make three false alarms (false positive), it is considered pretty good.

Start Oracle 12c Multitenant Database

Problem

When Oracle 12c was released a few months ago, I downloaded a copy and installed it on my laptop. I built a number of predictive models within a pluggable database. Today, I wanted to review my models and could not connect to the pluggable database on my laptop. The following is the error message that I got

C:\projects\sql>sqlplus dev/XXXXXX@//localhost:1521/testdb01

SQL*Plus: Release 12.1.0.1.0 Production on Sun Feb 9 06:34:27 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

ERROR:
ORA-01033: ORACLE initialization or shutdown in progress
Process ID: 0
Session ID: 0 Serial number: 0

Solution

I realized that I have rebooted my laptop and need to restart my pluggable database. To do this, I connected to the databbase as sys user.

C:\projects\sql>Sqlplus sys/xxxxx  as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Sun Feb 9 06:38:07 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>

I took a look at the pluggable databases that I have created.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDBORCL                        MOUNTED
         4 TESTDB01                       MOUNTED

As we can see, TESTDB01 is not open. I open the database.

SQL> alter pluggable database TESTDB01 open;

Pluggable database altered.
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDBORCL                        MOUNTED
         4 TESTDB01                       READ WRITE NO

I quit my sys session and I was able to connect to the plugable database TESTDB01 and check my predictive models built earlier.

SQL> quit
$sqlplus dev/XXXX@//localhost:1521/testdb01

SQL> select object_name, object_type from user_objects where 
object_type='MINING MODEL' order by object_name;

OBJECT_NAME                              OBJECT_TYPE
---------------------------------------- -----------------------
AI0929                                   MINING MODEL
AR1029                                   MINING MODEL
DT1029                                   MINING MODEL
GLM0115                                  MINING MODEL
GLM1031A                                 MINING MODEL
GLM1031B                                 MINING MODEL
GLM1031C                                 MINING MODEL
GLM1031E                                 MINING MODEL
KM1031C                                  MINING MODEL
KM1211                                   MINING MODEL
KM_MODEL                                 MINING MODEL
KM_MODEL_TRY1                            MINING MODEL
NB1021                                   MINING MODEL
OC_SH_CLUS_SAMPLE                        MINING MODEL
SVD0119                                  MINING MODEL
SVM1029                                  MINING MODEL
TMSVD1                                   MINING MODEL


17 rows selected.

Oracle 12c multitenant database is new and it took me a few hours to figure out how to manage the pluggable databases. I could build predictive models using the same PL/SQL code that worked on Oracle 11g. I have found that it is helpful for a data miner to know some basic database administrator stuff.

Impute Missing Values Using Clustering Algorithms

Problem

Sometimes, there are missing values in independent variables. Simply dropping those records with missing values is usually not a good idea. How do we replace the missing values with something meaningful?

Solution

A common way to replace missing values is to use mean or median values. Here we present a more sophisticated method to data imputation based clustering. The idea is to first find clusters in the data sets based on variables that do not have missing values. Then for the missing values, we can find out which cluster the records belongs to. We replace the missing values with the means or medians of all non-missing value points falling into the same cluster. The diagram below describe this idea. The missing values in Z for record 4 will be replaced by the mean or median value of Z for records 1, 2, 3, etc., that fall into the same cluster.
The advantage of this approach is that clusters are built based on multiple variables. We can do this using SQL within a database.

The following table shows that income is missing sometimes.

select * from TBL_CUSTOMER3 where rownum <=10;
   CUST_ID     INCOME  YEARS_RES HOME_VALUE SEX        MARRIED
---------- ---------- ---------- ---------- ---------- ----------
      7422   87499.50          2        117 "M"        "M"
      3356                    15         80 "M"        "U"
      4782   62499.50          3         91 "M"        "M"
      7333   87499.50          7         85 "M"        "M"
       890   42999.50          1         58 "F"        "U"
      6401   87499.50          5        128 "M"        "M"
      2356   87499.50          4         96 "M"        "M"
      1638   87499.50         13        152 "M"        "M"
      6713   62499.50          6         49 "M"        "U"
      3674   87499.50          3        119 "M"        "M"

10 rows selected.

Step 1. Prepare a data set by dropping income.

SQL> create view v_training_km as select cust_id, years_res, 
home_value, sex, married from TBL_CUSTOMER3;

Step 2. Build a k-means clustering model based on years_res, home_value, sex and married.

BEGIN
  DBMS_DATA_MINING.CREATE_MODEL(
    model_name          => 'KM_MODEL',
    mining_function     => dbms_data_mining.clustering,
    data_table_name     => 'v_training_km',
    case_id_column_name => 'cust_id');
END;
/

We can apply the k-mean clustering model to the data and generate the clusters.

SQL> select a.*, cluster_id(KM_MODEL using *) cls_id 
from TBL_CUSTOMER3 a where rownum<=10;

   CUST_ID     INCOME  YEARS_RES HOME_VALUE SEX        MARRIED        CLS_ID
---------- ---------- ---------- ---------- ---------- ---------- ----------
      7422   87499.50          2        117 "M"        "M"                12
      3356                    15         80 "M"        "U"                18
      4782   62499.50          3         91 "M"        "M"                18
      7333   87499.50          7         85 "M"        "M"                18
       890   42999.50          1         58 "F"        "U"                 8
      6401   87499.50          5        128 "M"        "M"                12
      2356   87499.50          4         96 "M"        "M"                18
      1638   87499.50         13        152 "M"        "M"                17
      6713   62499.50          6         49 "M"        "U"                 8
      3674   87499.50          3        119 "M"        "M"                12

10 rows selected.

Step 3. Generate imputed values for missing values based on cluster means.

SQL> create view v_imputed_data as with tbl_cls as 
(select a.*, cluster_id(KM_MODEL using *) as cls_id from TBL_CUSTOMER3 a ) 
select a.*, case when income is not null then income 
else avg(income) over(partition by cls_id) end as imputed_income 
from tbl_cls a ;

View created.

We can take a look at the missing values and their imputed values.

SQL> select CUST_ID,INCOME,IMPUTED_INCOME,CLS_ID 
from v_imputed_data where INCOME is null;

   CUST_ID     INCOME IMPUTED_INCOME     CLS_ID
---------- ---------- -------------- ----------
      6601                54704.0455          8
      4898                64394.2368          9
      6237                78894.6613         12
      6111                78894.6613         12
      6397                78894.6613         12
      4288                72316.5732         13
      2162                72316.5732         13
      5296                72316.5732         13
      3584                72316.5732         13
      2114                     79687         16
      3356                70261.2188         18
       462                70261.2188         18
      4474                70261.2188         18
        73                70261.2188         18
      3484                70261.2188         18
      5820                57488.1364         19

16 rows selected.

More on Taking Randomly Sampled Records From the Table

Problem

Random sampling is a very common task in a data analytics project. Sometime we want to sample precise number of records such as described in the post Take Randomly Sampled Records From the Table. Sometime, we randomly split a data set into training and testing sets at 70/30 (or whatever ratio). How do we efficiently perform multiple random sampling tasks?

Solution

I have found it is convenient to create a permanent table that contains the unique record id (or account id, card number, etc.) and a uniformly distributed random number. The following is an example of creating such a table described in Take Randomly Sampled Records From the Table. dbms_random.value returns uniformly distributed random number ranging from 0 to 1.

SQL> create table tbl_emp_id_rnd as select EMPLOYEE_ID, 
dbms_random.value rnd from EMPLOYEES;

Table created.

SQL> select * from tbl_emp_id_rnd where rownum <=10;

EMPLOYEE_ID        RND
----------- ----------
        100 .466996031
        101 .325172718
        102 .643593904
        103 .822225992
        104 .657242181
        105 .244060518
        106 .446914037
        107 .423664122
        108 .033736378
        109 .405546964

10 rows selected.

We can perform many types of random sampling based on such a table whenever we need to. For example, we can split the records into 70% training and 30% testing set. And we can change the ratio of 70/30 easily.

SQL> create view v_taining_emp_id as select EMPLOYEE_ID 
from tbl_emp_id_rnd where rnd <=0.7;

View created.

SQL> create view v_testing_emp_id as select EMPLOYEE_ID 
from tbl_emp_id_rnd where rnd >0.7;

View created.

I have found such a table is extremely helpful in a project where randomly sampling is performed multiple times.

Take Randomly Sampled Records From the Table

Problem

How do we randomly sample, say 20 records, from the table below that contains 107 employees?

SQL> select EMPLOYEE_ID, FIRST_NAME, LAST_NAME from EMPLOYEES where rownum <=10;

EMPLOYEE_ID FIRST_NAME           LAST_NAME
----------- -------------------- -------------------------
        100 Steven               King
        101 Neena                Kochhar
        102 Lex                  De Haan
        103 Alexander            Hunold
        104 Bruce                Ernst
        105 David                Austin
        106 Valli                Pataballa
        107 Diana                Lorentz
        108 Nancy                Greenberg
        109 Daniel               Faviet

Solution

We use the method describe in Randomly Sample a Precise Number of Records and More on random sampling in Oracle. Let's follow the steps.
1. Verify that employee_id is indeed the unique identifier.

SQL> select count(*), count(distinct employee_id) from EMPLOYEES;

  COUNT(*) COUNT(DISTINCTEMPLOYEE_ID)
---------- --------------------------
       107                        107

2. Generate a random number for each employee_id using dbms_random.value. Function dbms_random.value generates uniformly distributed random number greater than or equal to 0 and less than 1.

SQL> create table tbl_emp_id_rnd as select EMPLOYEE_ID, dbms_random.value rnd 
from EMPLOYEES;

Table created.

The following is what tbl_emp_id_rnd looks like.

SQL> select * from tbl_emp_id_rnd where rownum <=10;

EMPLOYEE_ID        RND
----------- ----------
        100 .466996031
        101 .325172718
        102 .643593904
        103 .822225992
        104 .657242181
        105 .244060518
        106 .446914037
        107 .423664122
        108 .033736378
        109 .405546964

10 rows selected.

3. Generate unique rank based on the random number for each record.

SQL> create table tbl_emp_id_rnd_rnk as select a.*, 
row_number() over(order by rnd) rnk from tbl_emp_id_rnd a;

Table created.

The following is what the table with rank looks like. To get the randomly sampled 20 records, we simply select records with ranks less or equal 20. The trick is based on that the rank of random numbers are also random.

SQL> select * from tbl_emp_id_rnd_rnk where rnk <=20 order by rnk;

EMPLOYEE_ID        RND        RNK
----------- ---------- ----------
        177 .001160678          1
        121 .001293396          2
        151 .013037966          3
        126 .018864319          4
        108 .033736378          5
        158  .09466332          6
        168 .095874461          7
        165 .116139108          8
        166 .116236033          9
        149 .121777964         10
        136 .126158543         11
        116 .140726813         12
        193 .147478226         13
        143 .164627124         14
        194 .175347727         15
        145 .206125327         16
        176 .207418722         17
        160 .207918621         18
        201  .21827842         19
        183 .218636576         20

20 rows selected.

Conclusions

We use Oracle dbms_random.value to first generate a random number of each record in the table and then row_number functions to calculate the rank of the random number. We then use the rank to select the desired number of records. Please notice that it is not a good practice to use the employee_id in the table directly for sampling purpose because we are not sure if it is randomly generated.

Calculate Standard Deviation Using SQL

Problem

How do we calculate the standard deviation of a variable, such as salary in the following table, using SQL?

SQL> select employee_id, department_id, hire_date, salary 
from EMPLOYEES where rownum <=20;

EMPLOYEE_ID DEPARTMENT_ID HIRE_DATE   SALARY
----------- ------------- --------- --------
        100            90 17-JUN-87  24000.0
        101            90 21-SEP-89  17000.0
        102            90 13-JAN-93  17000.0
        103            60 03-JAN-90   9000.0
        104            60 21-MAY-91   6000.0
        105            60 25-JUN-97   4800.0
        106            60 05-FEB-98   4800.0
        107            60 07-FEB-99   4200.0
        108           100 17-AUG-94  12000.0
        109           100 16-AUG-94   9000.0
        110           100 28-SEP-97   8200.0
        111           100 30-SEP-97   7700.0
        112           100 07-MAR-98   7800.0
        113           100 07-DEC-99   6900.0
        114            30 07-DEC-94  11000.0
        115            30 18-MAY-95   3100.0
        116            30 24-DEC-97   2900.0
        117            30 24-JUL-97   2800.0
        118            30 15-NOV-98   2600.0
        119            30 10-AUG-99   2500.0

20 rows selected.

Solution

We can use function stddev() to calculate the standard deviation.

SQL> select stddev(salary) from EMPLOYEES;

STDDEV(SALARY)
--------------
    3909.36575

The result can be verified using the following query.

SQL> with tbl as (select avg(salary) avg_sal, 
count(*) n from employees) 
select sqrt(sum((salary-avg_sal)*(salary-avg_sal)/(n-1))) 
from EMPLOYEES , tbl;

SQRT(SUM((SALARY-AVG_SAL)*(SALARY-AVG_SAL)/(N-1)))
--------------------------------------------------
                                        3909.36575

Calculate the Correlation Coefficient Using SQL

Problem

We want to calculate the correlation coefficient between two variables, such as the length of employment and salary within a company. If the data are Oracle database table, how do we do it using SQL?

SQL> select employee_id, department_id, hire_date, salary 
from EMPLOYEES where rownum <=20;

EMPLOYEE_ID DEPARTMENT_ID HIRE_DATE   SALARY
----------- ------------- --------- --------
        100            90 17-JUN-87  24000.0
        101            90 21-SEP-89  17000.0
        102            90 13-JAN-93  17000.0
        103            60 03-JAN-90   9000.0
        104            60 21-MAY-91   6000.0
        105            60 25-JUN-97   4800.0
        106            60 05-FEB-98   4800.0
        107            60 07-FEB-99   4200.0
        108           100 17-AUG-94  12000.0
        109           100 16-AUG-94   9000.0
        110           100 28-SEP-97   8200.0
        111           100 30-SEP-97   7700.0
        112           100 07-MAR-98   7800.0
        113           100 07-DEC-99   6900.0
        114            30 07-DEC-94  11000.0
        115            30 18-MAY-95   3100.0
        116            30 24-DEC-97   2900.0
        117            30 24-JUL-97   2800.0
        118            30 15-NOV-98   2600.0
        119            30 10-AUG-99   2500.0

20 rows selected.

Solution

We use Oracle corr function to calculate the correlation coefficient. The following is the query (since the data is old, we assume that the current date is May 30, 2000.)

SQL> select  corr(salary, to_date('20000530','YYYYMMDD')-hire_date) cor  from EMPLOYEES;

       COR
----------
.497838041

We can verify the correlation coefficient using the following query based on the equation for calculating correlation coefficient.

SQL> with 
tbl as (select avg(salary) m1, stddev(salary) s1, 
avg(to_date('20000530','YYYYMMDD')-hire_date) m2, 
stddev(to_date('20000530','YYYYMMDD')-hire_date) s2 ,
count(1) n from EMPLOYEES), 
tbl2 as (select salary, 
to_date('20000530','YYYYMMDD')-hire_date l from employees) 
select sum( (salary-m1)*(l-m2)/(n-1)/(s1*s2) ) from tbl, tbl2;

SUM((SALARY-M1)*(L-M2)/(N-1)/(S1*S2))
-------------------------------------
                           .497838041

Calculate the Difference of Purchase Amounts Between Transactions

Problem

It is interesting to compare purchase amount between the current transaction and the previous one. For example, the difference between a customer's current purchase amount and previous purchase amount may indicate frauds or may be used to predict customer's future spending trend. Taking the following table as an example, how to do calculate the difference in order amount between the current transaction and previous transaction for each customer_id?

SQL> select CUSTOMER_ID, order_id, ORDER_TOTAL, 
ORDER_TIMESTAMP from DEMO_ORDERS 
order by CUSTOMER_ID, ORDER_TIMESTAMP desc;

CUSTOMER_ID   ORDER_ID ORDER_TOTAL ORDER_TIME
----------- ---------- ----------- ----------
          1          1        1200 2013-08-15
          2          2         599 2013-08-10
          2          3        1999 2013-08-05
          3          4         750 2013-07-31
          3          5          40 2013-07-26
          4          6         250 2013-07-21
          5          7        3800 2013-07-16
          6          8          40 2013-07-11
          6          9         450 2013-07-06
          7         10         500 2013-07-01

10 rows selected.

Solution

We use Oracle analytic function lag as shown below. "lag(ORDER_TOTAL,1)" means getting the 1 previous row's order_total. "order by ORDER_TIMESTAMP" indicates that the "previous" is determined by order_timestamp. "partition by CUSTOMER_ID" specifies that the order is done independently by customer_id.

SQL> select CUSTOMER_ID, order_id, ORDER_TOTAL, ORDER_TIMESTAMP, ORDER_TOTAL-lag(ORDER_TOTAL,1) over(partition by CUSTOMER_ID 
order by ORDER_TIMESTAMP) delta 
from DEMO_ORDERS 
order by CUSTOMER_ID, ORDER_TIMESTAMP desc;

CUSTOMER_ID   ORDER_ID ORDER_TOTAL ORDER_TIME      DELTA
----------- ---------- ----------- ---------- ----------
          1          1        1200 2013-08-15
          2          2         599 2013-08-10      -1400
          2          3        1999 2013-08-05
          3          4         750 2013-07-31        710
          3          5          40 2013-07-26
          4          6         250 2013-07-21
          5          7        3800 2013-07-16
          6          8          40 2013-07-11       -410
          6          9         450 2013-07-06
          7         10         500 2013-07-01

10 rows selected.

As we see, customer 2 has a big drop in order_total (-1400) on 2013-08-10. While customer 3 has a jump in order_total (710) on 2013-07-31. Most of transactions in the above table do not have previous transactions.

More on Caculating Time Elapsed Between Two Dates

Problem

Time since last transaction, or recency, is an important factor for building predictive models such as fraud detection. How do we calculate the time elapsed between two transactions, i.e., time since last transaction, when the dates/time stamps are located in different rows as shown below?

SQL> select customer_id, TXN_DT, TXN_AMT from TBL_TXN_SMALLER order by CUSTOMER_ID, TXN_DT desc;

CUSTOMER_ID          TXN_DT               TXN_AMT
-------------------- ----------------- ----------
AAAAAAAA00000849171  20111111:16:11:41       5.23
AAAAAAAA00000849171  20111111:12:19:38      13.45
AAAAAAAA00000849171  20111107:12:36:53       12.2
AAAAAAAA00000849171  20111106:20:43:28       9.99
AAAAAAAA00000849171  20111104:23:00:02      19.95
AAAAAAAA00000849171  20111104:23:00:01          0
AAAAAAAA00000849171  20111102:17:08:20      42.77
AAAAAAAA00000849171  20111102:17:05:41      36.33
AAAAAAAA00000849171  20111102:16:00:25      14.07
AAAAAAAA00000849171  20111101:16:41:56      16.32
AAAAAAAA00003868233  20111110:16:07:54      35.16
AAAAAAAA00003868233  20111110:06:12:14      83.69
AAAAAAAA00003868233  20111110:06:12:09          0
AAAAAAAA00003868233  20111109:16:00:38      19.95
AAAAAAAA00003868233  20111104:06:21:50         54
AAAAAAAA00003868233  20111103:06:23:42      29.71
AAAAAAAA00003868233  20111103:04:42:13         79
AAAAAAAA00003868233  20111102:13:17:10        489
AAAAAAAA00003868233  20111102:02:10:28       9.95
AAAAAAAA00003868233  20111031:16:03:42      714.1
AAAAAAAA00004821778  20111002:02:19:17        100

21 rows selected.

Solution

One of the solutions is to use Oracle function lag. In the following query, lag(txn_dt,1) gets the previous row for the customer_id indepedently (partition by CUSTOMER_ID) order by txn_date. Please notice the first transaction from each customer is always null. This makes sense because there is no previous transaction for the first transaction.

SQL> select customer_id, TXN_DT, TXN_AMT, 
lag(TXN_DT,1) over(partition by CUSTOMER_ID order by TXN_DT) prev_dt, 
txn_dt-lag(TXN_DT,1) over(partition by CUSTOMER_ID order by TXN_DT) 
days_since_last from TBL_TXN_SMALLER order by CUSTOMER_ID, TXN_DT desc;

CUSTOMER_ID          TXN_DT               TXN_AMT PREV_DT           DAYS_SINCE_LAST
-------------------- ----------------- ---------- ----------------- ---------------
AAAAAAAA00000849171  20111111:16:11:41       5.23 20111111:12:19:38      .161145833
AAAAAAAA00000849171  20111111:12:19:38      13.45 20111107:12:36:53      3.98802083
AAAAAAAA00000849171  20111107:12:36:53       12.2 20111106:20:43:28      .662094907
AAAAAAAA00000849171  20111106:20:43:28       9.99 20111104:23:00:02      1.90516204
AAAAAAAA00000849171  20111104:23:00:02      19.95 20111104:23:00:01      .000011574
AAAAAAAA00000849171  20111104:23:00:01          0 20111102:17:08:20      2.24422454
AAAAAAAA00000849171  20111102:17:08:20      42.77 20111102:17:05:41      .001840278
AAAAAAAA00000849171  20111102:17:05:41      36.33 20111102:16:00:25      .045324074
AAAAAAAA00000849171  20111102:16:00:25      14.07 20111101:16:41:56      .971168981
AAAAAAAA00000849171  20111101:16:41:56      16.32
AAAAAAAA00003868233  20111110:16:07:54      35.16 20111110:06:12:14      .413657407
AAAAAAAA00003868233  20111110:06:12:14      83.69 20111110:06:12:09       .00005787
AAAAAAAA00003868233  20111110:06:12:09          0 20111109:16:00:38      .591331019
AAAAAAAA00003868233  20111109:16:00:38      19.95 20111104:06:21:50      5.40194444
AAAAAAAA00003868233  20111104:06:21:50         54 20111103:06:23:42      .998703704
AAAAAAAA00003868233  20111103:06:23:42      29.71 20111103:04:42:13      .070474537
AAAAAAAA00003868233  20111103:04:42:13         79 20111102:13:17:10      .642395833
AAAAAAAA00003868233  20111102:13:17:10        489 20111102:02:10:28      .462986111
AAAAAAAA00003868233  20111102:02:10:28       9.95 20111031:16:03:42      1.42136574
AAAAAAAA00003868233  20111031:16:03:42      714.1
AAAAAAAA00004821778  20111002:02:19:17        100

21 rows selected.

Conclusion

Using Oracle analytic function lag, we can calculate the recency factors such as time since last transaction within a single query without complex coding that may involve joining or looping.

Select the Most Recent N Transactions for Each Account

Problem

There is a customer purchase table that contains many transactions as show below. We want to only select the most recent 10 transactions for each customer. How do we do it?

SQL> select * from V_ALL_TXNS where rownum <20;

CUSTOMER_ID          TXN_DT       TXN_AMT
-------------------- --------- ----------
11738368607          02-DEC-10       4.59
11738368607          06-DEC-10        115
11738368607          06-DEC-10      12.04
11738368607          06-DEC-10        8.7
11738368607          06-DEC-10      15.96
11738368607          07-DEC-10          7
11738368607          07-DEC-10          8
11738368607          07-DEC-10         50
11738368607          13-DEC-10       2.12
11738368607          23-DEC-10      14.95
11738368607          28-DEC-10      209.8
11738368607          28-DEC-10      18.41
11738368607          30-DEC-10      43.18
11738368607          29-DEC-10      49.98
11738368607          29-DEC-10       9.83
11738368607          28-DEC-10      11.65
11738368607          30-DEC-10         20
11738368607          06-DEC-10      30.79
11738368607          01-DEC-10      19.23

Solution

We use Oracle analytic function row_number() to generate rank for each customer_id based on the transaction time. The query is shown below.

SQL> with
    tbl_temp as
    (select a.*,
    row_number() over 
    (partition by customer_id order by txn_dt desc) as rnk
    from V_ALL_TXNS a)
    select customer_id, txn_dt, txn_amt from
    tbl_temp
    where rnk <=10
    order by customer_id, txn_dt desc;

CUSTOMER_ID          TXN_DT       TXN_AMT
-------------------- --------- ----------
11685988069          30-DEC-10      269.5
11685988069          30-DEC-10     150.56
11685988069          30-DEC-10      46.66
11685988069          29-DEC-10      12.94
11685988069          29-DEC-10      16.04
11685988069          28-DEC-10      18.33
11685988069          28-DEC-10      23.33
11685988069          23-DEC-10     174.71
11685988069          22-DEC-10      23.76
11685988069          20-DEC-10     194.79
11738368607          30-DEC-10      43.18
11738368607          30-DEC-10       15.2
11738368607          30-DEC-10         20
11738368607          29-DEC-10      49.98
11738368607          29-DEC-10       9.83
11738368607          28-DEC-10      209.8
11738368607          28-DEC-10      11.65
11738368607          28-DEC-10      18.41
11738368607          23-DEC-10      14.95
11738368607          20-DEC-10      36.99
11768488237          31-DEC-10       6.69
11768488237          31-DEC-10       4.62
11768488237          31-DEC-10        1.4
11768488237          30-DEC-10       6.69
11768488237          30-DEC-10        1.4
11768488237          29-DEC-10       2.92
11768488237          29-DEC-10        1.7
11768488237          29-DEC-10        2.8
11768488237          29-DEC-10       3.55
11768488237          28-DEC-10        1.7

Logically Merge Data From Different Sources- Combine Records

Problem

In the real world application, data are collected by different systems. For example, some of the credit card fraudulent transactions are detected by banks through their fraud operation. Other fraudulent transactions are reported by customers and collected by claim department. To get the totality of credit cards that are involved in fraudulent activities, we have to combine the records from above two data sources. We describe the problem using a simple example. There are two tables, tbl_soccer_kids and tbl_tennis_kids, that record the names of kids who join soccer and tennis clubs. We want answers to the following questions:
1. Who join either soccer or tennis clubs?
2. Who join soccer and tennis clubs?
3. Who join soccer club but not tennis club?
4. Who join tennis club but not soccer club?

SQL> select * from tbl_soccer_kids order by name;

NAME
--------------------------------
CLARK
FORD
JAMES
JONES
MARTIN
SCOTT
TURNER

7 rows selected.

SQL> select * from tbl_tennis_kids order by name;

NAME
--------------------------------
ADAMS
ALLEN
BLAKE
CLARK
MARTIN
MILLER
TURNER
WARD

8 rows selected.

Solution

1. Who join either soccer or tennis clubs?
We use the "union" to combine the names from two tables. The duplicate names will be automatically removed. The following query shows that 12 kids are joining either soccer or tennis clubs.

SQL> select name from tbl_soccer_kids union select name from tbl_tennis_kids order by name;

NAME
--------------------------------
ADAMS
ALLEN
BLAKE
CLARK
FORD
JAMES
JONES
MARTIN
MILLER
SCOTT
TURNER
WARD

12 rows selected.

2. Who join soccer and tennis clubs?
We use "intersect" to find the common names in both tables.

SQL> select name from tbl_soccer_kids intersect select name from tbl_tennis_kids order by name;

NAME
--------------------------------
CLARK
MARTIN
TURNER

There are other approaches to find the common set and I will talk about them in another blog post.
3. Who join soccer club but not tennis club?
We use "minus" to find out records that are in the first table but not in the second table.

SQL> select name from tbl_soccer_kids minus select name from tbl_tennis_kids order by name;

NAME
--------------------------------
FORD
JAMES
JONES
SCOTT

4. Who join tennis club but not soccer club?
This is similar to question 3. We use "minus" and simply switch the order of two tables.

SQL> select name from tbl_tennis_kids minus select name from tbl_soccer_kids order by name;

NAME
--------------------------------
ADAMS
ALLEN
BLAKE
MILLER
WARD

How to Calculate Stock Price 200 Day Moving Average

Problem

It is a common task to calculate the moving average of a variable. For example, the 200 day stock price moving average is considered significant by some traders. We have data containing stock symbol, date and close price for 2011. The following query shows a few records for MSFT. How do we calculate 200 day stock price moving average for each symbol?

SQL> select symbol, dt, close from TBL_STOCK_2011 where symbol='MSFT' and dt between to_date('20111215','YYYYMMDD') and  to_date('20111231','YYYYMMDD'
) order by dt desc;

SYMBOL   DT             CLOSE
-------- --------- ----------
MSFT     30-DEC-11      25.25
MSFT     29-DEC-11       25.3
MSFT     28-DEC-11      25.11
MSFT     27-DEC-11      25.32
MSFT     23-DEC-11      25.31
MSFT     22-DEC-11       25.1
MSFT     21-DEC-11      25.05
MSFT     20-DEC-11      25.31
MSFT     19-DEC-11      24.83
MSFT     16-DEC-11      25.28
MSFT     15-DEC-11      24.86

11 rows selected.

Solution

One of the approaches is to use Oracle analytic window function, avg() over() as shown below.

SQL> select symbol, dt, close, avg(close) over(partition by symbol order by dt range between 199 preceding and current row) avg from TBL_STOCK_2011 or
der by dt desc;

SYMBOL   DT             CLOSE        AVG
-------- --------- ---------- ----------
MSFT     30-DEC-11      25.25 25.0448571
MSFT     29-DEC-11       25.3 25.0292143
MSFT     28-DEC-11      25.11 25.0272662
MSFT     27-DEC-11      25.32 25.0266667
MSFT     23-DEC-11      25.31 24.9653901
MSFT     22-DEC-11       25.1 24.9492199
MSFT     21-DEC-11      25.05 24.9481429
MSFT     20-DEC-11      25.31 24.9474101
MSFT     19-DEC-11      24.83 24.9302878
........................................

In the above query, "partition by symbol" means the calculation is done independently by symbols. We can verify the 200 moving average of 25.0448571 on 30-DEC-11 using the following query.

SQL> select avg(close) from tbl_stock_2011 where dt  between to_date('20111230','YYYYMMDD')-199 and to_date('20111230','YYYYMMDD');

AVG(CLOSE)
----------
25.0448571

As we can see, the 200 day average prices using two approaches are the same. A nice thing about analytic window function is that it easily creates moving average for every day. It is extremely useful when we are dealing with time series data. I have used analytic window functions extensively when building bank card or check fraud predictive models.

More on Calculating Z-Score

Problem

In the earlier post Calculate Z-Score using SQL in Oracle, we show that we can normalize a variable using z-score which is defined as (variable value-mean)/Standard Deviation. How do we calculate the Z-scores for employee's salaries for the following table?

SQL>  select EMPNO, ENAME, JOB, SAL from emp order by job, sal desc;

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7902 FORD       ANALYST         3000
      7788 SCOTT      ANALYST         3000
      7934 MILLER     CLERK           1300
      7876 ADAMS      CLERK           1100
      7900 JAMES      CLERK            950
      7369 SMITH      CLERK            800
      7566 JONES      MANAGER         2975
      7698 BLAKE      MANAGER         2850
      7782 CLARK      MANAGER         2450
      7839 KING       PRESIDENT       5000
      7499 ALLEN      SALESMAN        1600
      7844 TURNER     SALESMAN        1500
      7521 WARD       SALESMAN        1250
      7654 MARTIN     SALESMAN        1250

14 rows selected.

Solution

We can use the following query to calculate Z-score.

SQL> with tbl_mean_std as
    (
    select avg(sal) m, stddev(sal) std from EMP
    )
    select EMPNO, ENAME, JOB, SAL, (sal-m)/std as z_score
    from EMP, tbl_mean_std order by job, sal desc;

     EMPNO ENAME      JOB              SAL    Z_SCORE
---------- ---------- --------- ---------- ----------
      7902 FORD       ANALYST         3000 .783748996
      7788 SCOTT      ANALYST         3000 .783748996
      7934 MILLER     CLERK           1300 -.65387922
      7876 ADAMS      CLERK           1100 -.82301195
      7900 JAMES      CLERK            950  -.9498615
      7369 SMITH      CLERK            800  -1.076711
      7566 JONES      MANAGER         2975 .762607405
      7698 BLAKE      MANAGER         2850 .656899448
      7782 CLARK      MANAGER         2450 .318633985
      7839 KING       PRESIDENT       5000 2.47507631
      7499 ALLEN      SALESMAN        1600 -.40018012
      7844 TURNER     SALESMAN        1500 -.48474649
      7521 WARD       SALESMAN        1250  -.6961624
      7654 MARTIN     SALESMAN        1250  -.6961624

14 rows selected.

Not surprisingly, the president's salary has a z-score of 2.47 which is almost two and half standard deviation above the mean. Mean always has a z-score of zero. If we want to save the mean and standard deviation derived from this data set, we may create a permanent table to store them. This table can be used to calculate the z-score for any future data points. The following queries create a permanent table containing mean and standard deviation. It is then used to create a view that calculates the z-score.

SQL> create table tbl_mean_std_perm as select avg(sal) m, stddev(sal) std from EMP;
SQL> create view v_emp_zscore as select EMPNO,  ENAME,  JOB,   SAL,        (sal-m)/std as z_score from EMP, tbl_mean_std_perm;

We can take a look at the actual values of mean and standard deviation.

SQL> select * from tbl_mean_std_perm;

         M        STD
---------- ----------
2073.21429 1182.50322

How to Calculate Percentile

Problem

Sometimes, we are more interested in the percentile than the value itself. For example, parents of new born baby want to find out their baby's percentiles for weight and height. Using the employee salary table below, how do we calculate the percentiles for employee's salaries?

SQL>  select EMPNO, ENAME, JOB, SAL from emp order by job, sal desc;

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7902 FORD       ANALYST         3000
      7788 SCOTT      ANALYST         3000
      7934 MILLER     CLERK           1300
      7876 ADAMS      CLERK           1100
      7900 JAMES      CLERK            950
      7369 SMITH      CLERK            800
      7566 JONES      MANAGER         2975
      7698 BLAKE      MANAGER         2850
      7782 CLARK      MANAGER         2450
      7839 KING       PRESIDENT       5000
      7499 ALLEN      SALESMAN        1600
      7844 TURNER     SALESMAN        1500
      7521 WARD       SALESMAN        1250
      7654 MARTIN     SALESMAN        1250

14 rows selected.

Solution

We can use Oracle analytics function cume_dist for convert the value of salary into the percentile as shown below.

SQL>  select EMPNO, ENAME, JOB, SAL, cume_dist() over(order by sal) percentile from emp order by 5 desc;

     EMPNO ENAME      JOB              SAL PERCENTILE
---------- ---------- --------- ---------- ----------
      7839 KING       PRESIDENT       5000          1
      7788 SCOTT      ANALYST         3000 .928571429
      7902 FORD       ANALYST         3000 .928571429
      7566 JONES      MANAGER         2975 .785714286
      7698 BLAKE      MANAGER         2850 .714285714
      7782 CLARK      MANAGER         2450 .642857143
      7499 ALLEN      SALESMAN        1600 .571428571
      7844 TURNER     SALESMAN        1500         .5
      7934 MILLER     CLERK           1300 .428571429
      7521 WARD       SALESMAN        1250 .357142857
      7654 MARTIN     SALESMAN        1250 .357142857
      7876 ADAMS      CLERK           1100 .214285714
      7900 JAMES      CLERK            950 .142857143
      7369 SMITH      CLERK            800 .071428571

14 rows selected.

In the above output, the salary of 1500 corresponds to percentile of 50%. This means that 50% of employees have salaries less or equal to 1500. We  can verify this by running the following query. The number of records with salaries less or equal to 1500 is 7 which is 50% of the total size 14.

SQL> select count(*) from emp where sal <= 1500;

  COUNT(*)
----------
         7

More on Finding Records with Highest/Lowest Values by Category

Problem

In the earlier post Find Records with Highest/Lowest Values by Category, we use row_number() to generate the unique rank for each record. When there are ties in records, those records will receive different ranks randomly. For example, both Ford and Scott have the highest salary of 3000. However, row_number() generates different ranks for them. How do we find all the records with the highest salary by job including ties?

SQL> select EMPNO, ENAME, JOB, SAL, 
row_number() over(partition by job order by sal desc) sal_rank 
from emp order by job, sal desc;

     EMPNO ENAME      JOB              SAL   SAL_RANK
---------- ---------- --------- ---------- ----------
      7902 FORD       ANALYST         3000          1
      7788 SCOTT      ANALYST         3000          2
      7934 MILLER     CLERK           1300          1
      7876 ADAMS      CLERK           1100          2
      7900 JAMES      CLERK            950          3
      7369 SMITH      CLERK            800          4
      7566 JONES      MANAGER         2975          1
      7698 BLAKE      MANAGER         2850          2
      7782 CLARK      MANAGER         2450          3
      7839 KING       PRESIDENT       5000          1
      7499 ALLEN      SALESMAN        1600          1
      7844 TURNER     SALESMAN        1500          2
      7521 WARD       SALESMAN        1250          3
      7654 MARTIN     SALESMAN        1250          4

14 rows selected.

Solution

Two approaches are described here. The first one uses Oracle analytic function and the second one does not.
Approach 1. In stead of using Oracle analytic function row_number, we use dense_rank. If there are ties, dense_rank will produce the same rank for them.

SQL> select EMPNO, ENAME, JOB, SAL, 
dense_rank() over(partition by job order by sal desc) sal_rank 
from emp order by job, sal desc;

     EMPNO ENAME      JOB              SAL   SAL_RANK
---------- ---------- --------- ---------- ----------
      7902 FORD       ANALYST         3000          1
      7788 SCOTT      ANALYST         3000          1
      7934 MILLER     CLERK           1300          1
      7876 ADAMS      CLERK           1100          2
      7900 JAMES      CLERK            950          3
      7369 SMITH      CLERK            800          4
      7566 JONES      MANAGER         2975          1
      7698 BLAKE      MANAGER         2850          2
      7782 CLARK      MANAGER         2450          3
      7839 KING       PRESIDENT       5000          1
      7499 ALLEN      SALESMAN        1600          1
      7844 TURNER     SALESMAN        1500          2
      7521 WARD       SALESMAN        1250          3
      7654 MARTIN     SALESMAN        1250          3

14 rows selected.

Our final selection will be the following.

SQL> select * from (select EMPNO, ENAME, JOB, SAL,
 dense_rank() over(partition by job order by sal desc) sal_rank 
from emp order by job, sal desc)   where sal_rank=1 order by job;

     EMPNO ENAME      JOB              SAL   SAL_RANK
---------- ---------- --------- ---------- ----------
      7788 SCOTT      ANALYST         3000          1
      7902 FORD       ANALYST         3000          1
      7934 MILLER     CLERK           1300          1
      7566 JONES      MANAGER         2975          1
      7839 KING       PRESIDENT       5000          1
      7499 ALLEN      SALESMAN        1600          1

6 rows selected.

Approach 2. We calculate a temporary table containing the highest salary by job and then perform inner joining of the temporary table with the original table. As a result of the inner joining, only those employees with the highest salaries the jobs will be selected.

SQL> with
  2  tbl_temp as
  3  ( select job, max(sal) max_sal from emp group by job)
  4  select a.EMPNO, a.ENAME, a.JOB, a.SAL from
  5  emp a, tbl_temp b
  6  where a.job=b.job and a.sal=b.max_sal
  7  order by a.job;

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7788 SCOTT      ANALYST         3000
      7902 FORD       ANALYST         3000
      7934 MILLER     CLERK           1300
      7566 JONES      MANAGER         2975
      7839 KING       PRESIDENT       5000
      7499 ALLEN      SALESMAN        1600

6 rows selected.

As we see, the results produced by the two approaches are the same.