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.

Calculate Histogram Using Oracle Function

The following Oracle SQL script calculates the histogram.  width_bucket() and ratio_to_report() are two useful functions.
Width_buck function divides the CREDIT_SCORE into 10 equal length bins based on the minimum and maximum values.
Ratio_to_report function calculate the percentage of number of records, i.e., count(*), for each bin created by with_bucket function in step2.

with
tbl as (
select min(CREDIT_SCORE) low, max(CREDIT_SCORE) high
from CELL_PHONE_SERVICE_APPS),
tbl2 as (
select width_bucket(CREDIT_SCORE, low, high, 10) s, CREDIT_SCORE
from CELL_PHONE_SERVICE_APPS, tbl
)
select s,
min(CREDIT_SCORe) lower, max(credit_score) upper,
count(1) num,
round((ratio_to_report(count(1)) over())*100,1) pcnt
from tbl2 group by s order by s;

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