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.