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.
This blog is mostly focused on the technical aspect of data mining.
Wednesday, February 19, 2014
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.
Saturday, February 15, 2014
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.
Sunday, February 09, 2014
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.
Saturday, January 18, 2014
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.
Thursday, November 28, 2013
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.
Wednesday, November 27, 2013
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
Monday, November 25, 2013
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.
Sunday, November 24, 2013
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.
Saturday, November 23, 2013
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
Tuesday, November 19, 2013
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 TURNERThere 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 SCOTT4. 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.
Monday, November 18, 2013
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.
Sunday, November 17, 2013
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
Subscribe to:
Posts (Atom)