Remove Duplicates From Data

It is quite common to have duplicates in the data. From example, an application is submitted or a claim is filed  multiple times. With Oracle function row_number function, we can easily remove the duplicates.

The following SQL first calculates the rank of records by app_date for each app_id, then it only selects those records with ranks equals 1. The only earliest record for each app_id will be selected.

with tbl as
(
select a.*,
row_number() over(partition by app_id order by APP_DATE ) rnk  from CELL_PHONE_SERVICE_APPS a
)
select * from tbl where rnk=1;

If we want to keep the latest record for each app_id, we simply generate rank for each app_id by the descending order of app_date as shown below.


with tbl as
(
select a.*,
row_number() over(partition by app_id order by APP_DATE desc ) rnk  from CELL_PHONE_SERVICE_APPS a
)
select * from tbl where rnk=1;

Calculate Histogram for a Text File

Often the data are in text format before they are loaded into a database. It is a good practice to check the histogram of each data columns in the text file.
For example, we have csv file x.txt as shown below.

1,a
1,b
2,c
3,a
4,d
5,c
6,e
If  the operating system is UNIX or Cygwin (a  free unix simulator for Windows that can be downloaded here. we can use the follow commands to calculate histogram form column 2. Awk program defines "," as delimiter and print the second column ($2). Then the output data is sorted. Uniq counts the number of occurrences.

$ cat x.txt | awk -F"," '{print $2}' | sort | uniq -c
The following is the output.

     2 a
     1 b
     2 c
     1 d
     1 e