Oracle On The Fly Model- Clustering

One of the features in Oracle 12c is the ability of building and running models on the fly ( as opposed to the conventional two steps of building a persistent model first and then applying it to the data). On the fly predictive models provides data miners powerful tools for accomplishing sophisticated tasks with easy.
I will use on the fly k-means clustering as an example. Take a look at the following simple consumer data. Cust_ID is the unique identifier for a consumer.

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

   CUST_ID     INCOME  YEARS_RES HOME_VALUE SEX        MARRIED
---------- ---------- ---------- ---------- ---------- ----------
      7422    87499.5          2        117 "M"        "M"
      3356                    15         80 "M"        "U"
      4782    62499.5          3         91 "M"        "M"
      7333    87499.5          7         85 "M"        "M"
       890    42999.5          1         58 "F"        "U"
      6401    87499.5          5        128 "M"        "M"
      2356    87499.5          4         96 "M"        "M"
      1638    87499.5         13        152 "M"        "M"
      6713    62499.5          6         49 "M"        "U"
      3674    87499.5          3        119 "M"        "M"

10 rows selected.

To understand consumers better, usually we want to categorize consumers into small number of groups, instead of dealing with them individually. We can using Oracle predictive function cluster_id() over() to find clusters on the fly as shown below. Here, cluster_id(into 5 using income, YEARS_RES, HOME_VALUE) returns 5 cluster identifiers using the default clustering algorithms (K-means) based on variables income, YEARS_RES, HOME_VALUE. Missing input variables are OK as they will be replaced by the means. Please notice that the numbering of cluster identifier is not important at all,i.e., as long as clusters are different we can number cluster id arbitrarily.

SQL> select * from (select cust_id, 
   cluster_id(into 5 using income, YEARS_RES, HOME_VALUE ) over() cid 
from TBL_CUSTOMER3) where rownum <=10;

   CUST_ID        CID
---------- ----------
      7422          9
      3356          6
      4782          7
      7333          8
       890          5
      6401          9
      2356          9
      1638          8
      6713          7
      3674          9

10 rows selected.

We can calculate some summary information about each group using the following query to gain insight into the data.

SQL> select cid, count(*), avg(income) avg_income, 
avg(years_res) avg_years_res, 
avg(home_value) avg_home_value from 
(select cust_id, income, years_res, home_value, 
cluster_id(into 5 using income, YEARS_RES, HOME_VALUE ) over() cid 
from TBL_CUSTOMER3) group by cid order by count(*) desc;

       CID   COUNT(*)  AVG_INCOME AVG_YEARS_RES AVG_HOME_VALUE
---------- ---------- ----------- ------------- --------------
         7        152    62499.50          3.88          92.89
         9        126    87499.50          2.75         117.98
         5        110    47254.05          2.58          67.37
         8         65    87499.50          9.15         105.58
         6         31    55749.50         13.45          82.39

The clustering models are generated on the fly. When the queries finish, the models are gone. Thus it is very "clean" to run queries involving on the fly models.

Please notice that in the above query, we combine predictive model with standard SQL (group by cid). This is extremely powerful in that we seamlessly raise conventional SQL queries up to a higher level of predictive modeling. We instantly turn a SQL developer into a data miner. Bingo! (If you are interested in this point, please also see my early posts From Oracle SQL Developer to Data Miner and How easy is it for a SQL developer/analyst to become a data miner?

Oracle Exception NO_DATA_FOUND

Problem

We want to select a column value from a table based on a key. When no record is found for the key, a special message will be displayed.

Solution

Taking the following table as an example.

SQL> select * from TBL_0321;

        ID VAL
---------- --------------------------------
         1 ok

We create a text file find_id.sql that contains the following scripts. When an id is found, it will display "value is:...". When an id is not found, use "exception when NO_DATA_FOUND" kicks in and "Id not found" is displayed.

set serveroutput on;
declare
msg varchar2(32);
begin
 select val into msg from TBL_0321 where id=&1;
 dbms_output.put_line('value is:'||msg);
 exception when NO_DATA_FOUND then
   dbms_output.put_line('Id not found'); 
end;
/

The following are the outputs from actual running of the script.

SQL> @find_id 1
old   4:  select val into msg from TBL_0321 where id=&1;
new   4:  select val into msg from TBL_0321 where id=1;
value is:ok

PL/SQL procedure successfully completed.

SQL> @find_id 2
old   4:  select val into msg from TBL_0321 where id=&1;
new   4:  select val into msg from TBL_0321 where id=2;
Id not found

PL/SQL procedure successfully completed.

Add Comments to Oracle Table

It is a good idea to add comments or descriptions to a table that could be lengthier and more descriptive than the table name. We can retrieve the comments later when needed. This is particularly important for a large project that may involve hundreds of tables.

SQL> comment on table TBL_0321 is 'this is a test';

Comment created.

To retrieve the comments, use the following query.

SQL> select comments from user_tab_comments where table_name='TBL_0321';

COMMENTS
----------------------------------------------------------------------------------
this is a test

Using Oracle Dump Function

Oracle dump() function is a useful tool for looking into the "real value", not just the displayed. For example, we may be puzzled to see the results of the query below. Message "data analytics" in Record 3 spans two lines.

SQL> select * from tbl_test;

        ID MESSAGE
---------- --------------------------------
         1 Hello
         2 World
         3 data
            analytics

To find out what is going on, we use dump() function to examine the content of message. On record 3, there is a ASCII character 10 which represents a line new.

SQL> select a.*, dump(id), dump(message) from tbl_test a;

        ID MESSAGE
---------- --------------------------------
DUMP(ID)
------------------------------------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------
DUMP(MESSAGE)
------------------------------------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------
         1 Hello
Typ=2 Len=2: 193,2
Typ=1 Len=5: 72,101,108,108,111

         2 World
Typ=2 Len=2: 193,3
Typ=1 Len=5: 87,111,114,108,100

         3 data
            analytics
Typ=2 Len=2: 193,4
Typ=1 Len=16: 100,97,116,97,32,10,32,97,110,97,108,121,116,105,99,115

To remove the new line, we use replace() function as shown below. Now the display looks much better.

SQL> select id, replace(message, chr(10), '') from tbl_test a;

        ID REPLACE(MESSAGE,CHR(10),'')
---------- ----------------------------------------------------------------
         1 Hello
         2 World
         3 data  analytics

In addition to dump(), we can also use function utl_raw.CAST_TO_RAW() as described in post Watch out invisible characters.

Make Oracle Table Read Only

It is a good idea to protect a static table from being modified by making it read only. Command "alter table .. read only" does precisely that. The following example shows how it works.

SQL> create table tbl_test (id number, message varchar2(64));

Table created.

SQL> insert into tbl_test values(1,'Hello');

1 row created.

SQL> alter table tbl_test read only;

Table altered.

SQL> insert into tbl_test values(2,'World');
insert into tbl_test values(2,'World')
            *
ERROR at line 1:
ORA-12081: update operation not allowed on table "MYSCHEMA"."TBL_TEST"

As we can see, after we make the table read only, we can not insert data into the table. We can still run the following queries against read only tables.

1. Select from a read only table.

SQL> select * from tbl_test;

        ID MESSAGE
---------- ----------------------------------------------------------------
         1 Hello

2. Create index on a read only table

SQL> create index tbl_test_idx on tbl_test(id);

Index created.

3. Rename a read only table

SQL> rename tbl_test to tbl_test_b;

Table renamed.

4. Drop a read only table

SQL> drop table tbl_test_b;

Table dropped.

Finally, to restore a read only table to a writable table, we use the command "alter table ... read write" as shown below. Once a "ready only" table becomes "read write", we can insert data into it.

SQL> alter table tbl_test read write;

Table altered.

SQL> insert into tbl_test values(2,'World');

1 row created.

Rename an Oracle Table or View

We can use "rename" to rename a table or view.

SQL> rename tbl_test01 to tbl_test01b;

Table renamed.

Alternatively, we can rename a table using "alter table ...rename to...".

SQL> alter table tbl_test01c rename to tbl_test01d;

Table altered.

Flush Shared Pool and Buffer Cache

In the post Display How Long to Run a Query, we talk about how to display the time elapsed runing a query. When we run a "fresh" or new query, the database needs to do a lot of work to parse the SQL text and decide the execution plan. Parsed SQL query is stored in memory and may be reused in the future if the same SQL text is issued. Thus as we query the databases, information about our past queries are buffered in the memory. If we run the same query again, it may be faster because the database can reuse information in the memory. We can run the following SQL commands to flush the share pool (information about SQL text) and buffer cache(table data buffered in memory). After that, we can more accurately measure how long it takes to run a fresh query .

SQL>alter system flush shared_pool;
SQL>alter system flush buffer_cache;

Oracle Direct Path Insert

Problem

When there are large amount of data to be inserted into a table, it is better to use direct path insert. To do direct path insert, we can specify hit /*+ append */ as shown below.

SQL>  insert  /*+ append */ into tblabc select a.id, a.a from TBL_TEST3 a;

However, if we do another direct path insert into the same table, there will be error, ORA-12838: cannot read/modify an object after modifying it in parallel.

SQL>  insert  /*+ append */ into tblabc select a.id, a.a from TBL_TEST3 a;
 insert  /*+ append */ into tblabc select a.id, a.a from TBL_TEST3 a
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel

To do another direct path insert into the same table, we need to commit the first one first.

SQL> commit;

Commit complete.

SQL>  insert  /*+ append */ into tblabc select a.id, a.a from TBL_TEST3 a;

4 rows created.

Conventional insert does not have the issue as shown below.

SQL>  insert   into tblabc select a.id, a.a from TBL_TEST3 a;

4 rows created.

SQL>  insert   into tblabc select a.id, a.a from TBL_TEST3 a;

4 rows created.

SQL>  insert  /*+ append */ into tblabc select a.id, a.a from TBL_TEST3 a;

4 rows created.

SQL>  insert  /*+ append */ into tblabc select a.id, a.a from TBL_TEST3 a;
 insert  /*+ append */ into tblabc select a.id, a.a from TBL_TEST3 a
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel


SQL> commit;

Commit complete.

SQL>  insert  /*+ append */ into tblabc select a.id, a.a from TBL_TEST3 a;

4 rows created.

Handle Divided By Zero in Oracle

Problem

Ratio is very useful variable. For example, the ratio between credit card account balance and credit limit is a good measurement of the default risk. However, when calculating ratio, it the denominator is zero, Oracle raises an error,ORA-01476: divisor is equal to zero, and stops the query. This is inconvenient as shown below.

SQL> select * from tbl_account;

CUSTOMER_ID ACCT_BALANCE CREDIT_LIMIT
----------- ------------ ------------
          1         2000         5000
          2            0            0
          3         6000        12000

SQL> select a.*, ACCT_BALANCE/CREDIT_LIMIT r from tbl_account a;
ERROR:
ORA-01476: divisor is equal to zero
no rows selected

Solution

One solution to calculating ratio or division is to build a function that returns NULL when the denominator is zero and normal value when it is not zero. The script below builds such a function div(num1, num2) that calculates num1/num2. It will return NULL when num2 is zero and continue the execution.

create or replace FUNCTION div (
   num1 number,
   num2 number
)
   RETURN NUMBER IS
   v_number   NUMBER;
BEGIN
   BEGIN
      v_number:= num1/num2 ;
   EXCEPTION
      WHEN OTHERS THEN
         RETURN NULL;
   END;

   RETURN v_number;
END div;
/

After we run the above script to build function div(), we can use it to replace division "/".

SQL> select a.*, div(ACCT_BALANCE,CREDIT_LIMIT) r from tbl_account a;

CUSTOMER_ID ACCT_BALANCE CREDIT_LIMIT          R
----------- ------------ ------------ ----------
          1         2000         5000         .4
          2            0            0
          3         6000        12000         .5

The following are other outputs of using div()function.

SQL> select div(0,0) r from dual;

         R
----------


SQL> select div(NULL,0) r from dual;

         R
----------


SQL> select div(NULL,NULL) r from dual;

         R
----------

NULL Value and Order By

By default, when we order the records by columns, the null values are ranked last.

SQL> select * from TBL_TEST3 order by a, b;

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

We can place rows with nulls first by specifying "nulls fist".

SQL> select * from TBL_TEST3 order by a nulls first, b;

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

NULL Value and Logical Comparison

Logical comparisons in where clause of queries invloving NULL values will be ignored. 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 * from tbl_test3 where a=b;

        ID          A          B
---------- ---------- ----------
         4          2          2

SQL> select * from tbl_test3 where a<>b;

        ID          A          B
---------- ---------- ----------
         1          1          2

It is interesting to notice that 1. NULL=NULL is not true; 2. NULL<>NULL is not true either.

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

Four ways of Calculating Average Value

In the post Calculate Average Value - Watch Out NULL Values, we mentioned that we need to pay attention to how the NULL values are handled by the database. Using the following table as an example (that has two records with NULL values), we will show four ways of calculating average.

SQL> select * from tbl_test2;

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

6 rows selected.

Using sum(value)/count(*) will return the wrong result.

SQL> select sum(value)/count(*) from tbl_test2; (wrong!)

SUM(VALUE)/COUNT(*)
-------------------
         2.33333333

The following four queries are correct.
Method 1. Using avg() function.

SQL> select avg(value) from tbl_test2;

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

Method 2. Using sum(value)/count(value) function. Count(value) will ignore NULL values.

SQL> select sum(value)/count(value) from tbl_test2;

SUM(VALUE)/COUNT(VALUE)
-----------------------
                    3.5

Method 3. Using sum(value)/sum(case when value is null then 0 else 1 end).

SQL> select sum(value)/sum(case when value is null then 0 else 1 end) from tbl_test2;

SUM(VALUE)/SUM(CASEWHENVALUEISNULLTHEN0ELSE1END)
------------------------------------------------
                                             3.5

Method 4. Using sum(value)/count(*) and put "not null" condition in where clause.

SQL> select sum(value)/count(*) from tbl_test2 where value is not null;

SUM(VALUE)/COUNT(*)
-------------------
                3.5

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.

Find Records with Highest/Lowest Values by Category

Problem

We use the emp table as an example. How to we find the records for the highest paid employees by job titles?

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 use Oracle analytic function row_number() which generates unique ranks for records. The rank will be generated independently by job ("partition by job") and will be based on the descending order of salary ("order by sal desc"). So the record with the highest salary in each job will receive a rank of 1 as shown below.

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.

To show only those records having highest salaries by jobs, we add a condition that only records with the rank of salary equal one will be selected.

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

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

The above method using row_number to generates rank will select one record for each category. If more then one records have the highest salaries in their group such as Analyst Scott and Ford, only one of them will be selected. In this case, it is Scott. In the post More on Finding Records with Highest/Lowest Values by Category, I will describe how to select all records with the highest value when there are ties.

Three Ways of Calculating Percentage of Quantity Using Oracle SQL

Problem

Similar to the problem mentioned in In the earlier post, Three Ways of Calculating Percentage of Counts Using Oracle SQL, we often need to calculate percentage based on the quantity such as revenue by product type, total expense by department, etc. In the following example, how do we calculate the percentage of salary expense by job title?

SQL> select EMPNO, ENAME, JOB, SAL from emp;

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

14 rows selected.

Solution

In the same way as described in Three Ways of Calculating Percentage of Counts Using Oracle SQL, three approaches are described here to calculate percentage by quantity.

Approach 1.

We use Oracle analytic ratio_to_report function and pass the function sum(sal) to it. This is the simplest solution.

SQL> select JOB, ratio_to_report(sum(SAL)) over() as percentage from emp group by job order by JOB;

JOB       PERCENTAGE
--------- ----------
ANALYST   .206718346
CLERK     .142980189
MANAGER   .285099053
PRESIDENT .172265289
SALESMAN  .192937123

Approach 2.

We calculate total salary first in a sub query.

SQL> select JOB, sum(SAL)/(select sum(SAL) as total from emp) as percentage from emp group by job order by JOB;

JOB       PERCENTAGE
--------- ----------
ANALYST   .206718346
CLERK     .142980189
MANAGER   .285099053
PRESIDENT .172265289
SALESMAN  .192937123

Approach 3.

Again, we make use of new_value to generate SQLPLUS variable &total_sal.

SQL> column total_sal new_value total_sal
SQL> select sum(SAL) as total_sal from emp;

 TOTAL_SAL
----------
     29025

SQL> select JOB, sum(SAL)/&total_sal as percentage from emp group by job order by JOB;
old   1: select JOB, sum(SAL)/&total_sal as percentage from emp group by job order by JOB
new   1: select JOB, sum(SAL)/     29025 as percentage from emp group by job order by JOB

JOB       PERCENTAGE
--------- ----------
ANALYST   .206718346
CLERK     .142980189
MANAGER   .285099053
PRESIDENT .172265289
SALESMAN  .192937123

Three Ways of Calculating Percentage of Counts Using Oracle SQL

Problem

We are often asked to calculate summary information concerning percentage of counts, such as the percentage of our customers living in each state, percentage of credit card transactions by Merchant Category Code, percentage of employees by job tile, etc. For example, how do we calculate the percentage of number of employees by job title for the following table?

SQL> select EMPNO, ENAME, JOB, SAL from emp;

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

14 rows selected.

Solution

Approach 1.

The simplest solution is to use Oracle analytic function ratio_to_report function with count(*) as the input parameter.

SQL> select JOB, ratio_to_report(count(*)) over() as percentage from emp    group by job order by JOB;

JOB       PERCENTAGE
--------- ----------
ANALYST   .142857143
CLERK     .285714286
MANAGER   .214285714
PRESIDENT .071428571
SALESMAN  .285714286

Approach 2.

If we do not use anlytic function ratio_to_report, we need to do it in two steps.

SQL> select JOB, count(*)/(select count(*) as total from emp) as percentage from emp   group by job order by JOB;

JOB       PERCENTAGE
--------- ----------
ANALYST   .142857143
CLERK     .285714286
MANAGER   .214285714
PRESIDENT .071428571
SALESMAN  .285714286

Approach 3.

We can take advantage of "new_value" feature to define a variable total_num, fill it with the actual total counts of employees and use it ( as &total_num) in calculating the percentage.

SQL> column total new_value total_num
SQL> select count(*) as total from emp;

     TOTAL
----------
        14

SQL> select JOB, count(*)/&total_num as percentage from emp   group by job order by JOB;
old   1: select JOB, count(*)/&total_num as percentage from emp group by job order by JOB
new   1: select JOB, count(*)/        14 as percentage from emp group by job order by JOB

JOB       PERCENTAGE
--------- ----------
ANALYST   .142857143
CLERK     .285714286
MANAGER   .214285714
PRESIDENT .071428571
SALESMAN  .285714286

How to Generate Globally Unique Identifier on the Fly

Problem

How do we generate an identifier that is globally unique across databases and machines? A globally unique identifier is very useful. For example, if we mark a credit card transaction uniquely, we trace it in different stages including authorization, settlement, dispute etc.

Solution

In the earlier post Five Ways of Creating Unique Record Identifier For Oracle Tables we mentioned unique identifiers in Oracle can be created by using: 1. Oracle pseudocolumn rowid; 2. Oracle rownum; 3. row_number() function; 4. Sequence; 5. sys_guid().
The simplest solution to globally unique identifier is to call sys_guid() whenever we need to. It does not depend on a separate structure like sequence. Other mechanisms include rowid, rownum and row_number() are only guaranteed to be unique for a single table. I have seen a self-made globally unique identifier generator does not work 100% of the time.
The following query uses sys_guid function to generate globally unique identifier for every row in EMP table. Sys_guid returns 16 byte "raw" type.

SQL> select sys_guid() as gid, EMPNO, ENAME from EMP;

GID                                   EMPNO ENAME
-------------------------------- ---------- ----------
EB567FE077DC5427E040D00AD17F0769       7839 KING
EB567FE077DD5427E040D00AD17F0769       7698 BLAKE
EB567FE077DE5427E040D00AD17F0769       7782 CLARK
EB567FE077DF5427E040D00AD17F0769       7566 JONES
EB567FE077E05427E040D00AD17F0769       7788 SCOTT
EB567FE077E15427E040D00AD17F0769       7902 FORD
EB567FE077E25427E040D00AD17F0769       7369 SMITH
EB567FE077E35427E040D00AD17F0769       7499 ALLEN
EB567FE077E45427E040D00AD17F0769       7521 WARD
EB567FE077E55427E040D00AD17F0769       7654 MARTIN
EB567FE077E65427E040D00AD17F0769       7844 TURNER
EB567FE077E75427E040D00AD17F0769       7876 ADAMS
EB567FE077E85427E040D00AD17F0769       7900 JAMES
EB567FE077E95427E040D00AD17F0769       7934 MILLER

14 rows selected.

Oracle SQLPLUS Client Installation on Windows Troubleshooting

Problem

I wanted to install Oracle Instant Client On Windows for my laptop, a 32 bit system running Windows 7. I went to the Instant Client Downloads for Microsoft Windows (32-bit) and downloaded two zip files, instantclient-basic-nt-12.1.0.1.0.zip and instantclient-sqlplus-nt-12.1.0.1.0.zip. I unzipped them under the same directory. When I ran sqlplus, nothing happened.

$
$ sqlplus myuser/mypasswrod@192.168.1.5:1521/xe
$

Solution

Under the directory that I unzipped the downloaded files, I found a number of executable file such as adrci.exe in addition to sqlplus.exe. I ran adrci.exe and got the following error message.

$adric.exe 
"error while loading shared libraries: MSVCR100.dll"

After doing some research about MSVCR100.dll on the internet, I went to Microsoft Download Center: Microsoft Visual C++ 2010 Redistributable Package (x86). Once I downloaded and install the package, I can run sqlplus and connect to the database server without issue.

$ sqlplus myuser/mypasswrod@192.168.1.5:1521/xe

SQL*Plus: Release 12.1.0.1.0 Production on Sat Nov 16 13:07:04 2013

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


Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL>

I have written a newer post about the installation Oracle SQLPLUS Client Installation on Windows 64.

Add Line Number to Text File

Problem

We have the following text file.

$ cat ads_log_small.csv
ADS_ID,DEVICE_OS,NUM_IMPRESSION,NUM_CONVERSION
 32, Android, 1, 0
 32, Android, 1, 0
 32, Android, 1, 0
 32, Android, 2, 0
 32, Android, 1, 0
 32, Android, 2, 0
 32, Android, 1, 0
 32, Android, 3, 0
 32, Android, 2, 0

If we load records in the text file into a database table, the original order of records in the file is lost. In the post SAS data set vs. relational database table, we mentioned that,Unlike SAS data set, a database table is a set where records no order (unless we explicitly sort them by keys). If we want to preserve the original sequence of the record, how do we add a line number to the original file?

Solution

We can use Linux (or Cygwin on Windows) command awk to add a line number to the file.

$ cat ads_log_small.csv | awk '{if (NR==1) print "LINE_NUMBER" ,",",$0;    else print NR-1,",",$0}'
LINE_NUMBER , ADS_ID,DEVICE_OS,NUM_IMPRESSION,NUM_CONVERSION
1 ,  32, Android, 1, 0
2 ,  32, Android, 1, 0
3 ,  32, Android, 1, 0
4 ,  32, Android, 2, 0
5 ,  32, Android, 1, 0
6 ,  32, Android, 2, 0
7 ,  32, Android, 1, 0
8 ,  32, Android, 3, 0
9 ,  32, Android, 2, 0

We can write the output to a new file.

$ cat ads_log_small.csv | awk '{if (NR==1) print "LINE_NUMBER" ,",",$0;    else print NR-1,",",$0}' > ads_log_new.csv

How to Find Out the Cutoff Value for Certain Percentile

Problem

We want to find out what answers to questions like:
What is the salary that top 1 percent of people are making?
What is the credit score that the lowest 15% have?

What is the cutoff predictive risky score that will raise alerts on the riskiest 0.1% of credit card transaction?

Solution

This problem can be solved using the same approach as described in Find the cutoff value for the top n records. We can always convert the percentile to the top-n-records (or bottom-n-records) by multiplying the size of the table and the percentile. However, there is a simpler way to do this. We can use Oracle function percentile_disc() to return the cutoff value given a certain percentile and ordering. Let's use a table that contains 100 random numbers as an example.
SQL> select * from TBL_100_RND where rownum <10;

NUM
-.58413539
1.31513578
2.16901993
.596910933
.208190221
.179899195
-.63028597
.525443855
-.52158424

The following query returns the cutoff value (v) for the top 10%.
SQL> select percentile_disc(0.1) within group(order by num desc) as cutoff from TBL_100_RND;

cutoff
1.13239998

The following query verifies that the cutoff value 1.13239998 indeed produces 10% of the records.
SQL> select count(1) from TBL_100_RND where num>=1.13239998;

COUNT(1)
10

How to Change Delimiters of Text File

Problem

A text file may contain many fields separated by delimiters. The delimiters could be tab, pipe(|), comma, etc. The following is an example of such a file. It has 4 fields separated by pipe (|). Some utilities only correctly recognize certain type of delimiters. How do we change the delimiter from one type to another, e.g., from pipe (|) to comma(,)?

$ head ads_log.txt
ADS_ID|DEVICE_OS|NUM_IMPRESSION|NUM_CONVERSION
 32| Android| 1| 0
 32| Android| 1| 0
 32| Android| 1| 0
 32| Android| 2| 0
 32| Android| 1| 0
 32| Android| 2| 0
 32| Android| 1| 0
 32| Android| 3| 0
 32| Android| 2| 0

Solution

We can use an editor such as notepad to do character replacement. However, if the file size is very big, using editor to do the work is very slow. A better way would be using Unix/Linux command tr.If we are running Windows system, we can install free cygwin which simulates Unix.

$ cat ads_log.txt | tr '|' ','
ADS_ID,DEVICE_OS,NUM_IMPRESSION,NUM_CONVERSION
 32, Android, 1, 0
 32, Android, 1, 0
 32, Android, 1, 0
 32, Android, 2, 0
 32, Android, 1, 0
 32, Android, 2, 0
 32, Android, 1, 0
 32, Android, 3, 0
 32, Android, 2, 0

We can write the output to the resulting file ads_log.txt.

$ cat ads_log.txt | tr '|' ',' > ads_log2.txt